# Database Schema (MVP)

Every tenant-owned table carries `tenant_id`. Money stored as integers in
paisa (or smallest currency unit) to avoid float rounding errors — display
layer converts to rupees.

## tenants
Contractor accounts, created by super admin.

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| name | string | contractor/business name |
| phone | string, unique | |
| email | string, nullable, unique | |
| plan | enum(free, paid) | default free |
| project_limit | int, nullable | null = unlimited (paid); 1 for free |
| is_active | boolean | super admin can suspend an account |
| created_at / updated_at | | |

## users
Login accounts within a tenant (owner or supervisor).

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| tenant_id | fk → tenants | |
| name | string | |
| phone | string, nullable | one of phone/email required |
| email | string, nullable | |
| password | string | |
| role | enum(owner, supervisor) | |
| created_at / updated_at | | |

## projects  (= construction sites)

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| tenant_id | fk → tenants | |
| name | string | e.g. "Site A — Baneshwor" |
| location | string, nullable | |
| status | enum(active, paused, completed) | |
| budget | bigint, nullable | paisa; optional target budget |
| start_date | date, nullable | |
| created_at / updated_at | | |

## project_user  (pivot — which supervisors are assigned to which sites)

| column | type | notes |
|---|---|---|
| project_id | fk → projects | |
| user_id | fk → users | |

## inventory_items
Catalog of material types a tenant tracks (cement, rebar, bricks, etc).

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| tenant_id | fk → tenants | |
| name | string | |
| unit | string | e.g. "bag", "kg", "piece" |
| created_at / updated_at | | |

## inventory_transactions
One row per stock movement, scoped to a project.

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| project_id | fk → projects | |
| inventory_item_id | fk → inventory_items | |
| type | enum(in, out) | received vs consumed |
| quantity | decimal(10,2) | |
| transaction_date | date | |
| notes | string, nullable | |
| created_by | fk → users | |
| created_at / updated_at | | |

## cash_receipts
Cash coming into a site (from the contractor's own funds, client payments, etc).

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| project_id | fk → projects | |
| amount | bigint | paisa |
| source | string, nullable | free text: who it came from |
| received_date | date | |
| notes | string, nullable | |
| created_by | fk → users | |
| created_at / updated_at | | |

## expenses
Vendor payments / site expenses.

| column | type | notes |
|---|---|---|
| id | bigint pk | |
| project_id | fk → projects | |
| vendor_name | string | free text for MVP — see ARCHITECTURE.md open question |
| category | string, nullable | e.g. "material", "labor", "transport" |
| amount | bigint | paisa |
| expense_date | date | |
| payment_method | enum(cash, bank, other), nullable | |
| notes | string, nullable | |
| created_by | fk → users | |
| created_at / updated_at | | |

## Derived: per-site P&L

Not a stored table — computed:

```
profit = SUM(cash_receipts.amount for project)
       - SUM(expenses.amount for project)
```

Inventory value is deliberately NOT included in P&L for MVP (would require
unit cost per inventory item, which isn't in scope yet — inventory
transactions currently track quantity, not value). If contractors want
inventory valued into P&L, that's a schema change (add `unit_cost` to
`inventory_items` or `inventory_transactions`), not a display-layer fix.