# 資料模型草案

## 核心資料表

### parties

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| type | enum | owner, tenant, vendor, staff |
| name | text | 姓名或公司名 |
| phone | text | 電話 |
| email | text | 信箱 |
| tax_id | text | 統編或身分證字號，可加密 |
| bank_account | text | 收款帳戶，可加密 |
| created_at | timestamp | 建立時間 |

### properties

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| owner_id | uuid | 房東 parties.id |
| code | text | 內部物件編號 |
| address | text | 地址 |
| unit | text | 房號 |
| community | text | 社區 |
| status | enum | vacant, active, ending, archived |
| base_owner_rent | integer | 應付房東基準租金 |
| base_tenant_rent | integer | 應收房客基準租金 |

### contracts

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| property_id | uuid | 物件 |
| party_id | uuid | 房東或房客 |
| contract_type | enum | master_lease, management, tenant_lease |
| starts_on | date | 起租日 |
| ends_on | date | 到期日 |
| monthly_rent | integer | 月租 |
| deposit_amount | integer | 押金 |
| payment_day | integer | 每月付款日 |
| management_fee_rate | numeric | 管理費率 |
| status | enum | draft, active, ended, canceled |

### bills

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| contract_id | uuid | 合約 |
| property_id | uuid | 物件 |
| party_id | uuid | 應收或應付對象 |
| direction | enum | receivable, payable |
| period | char(7) | YYYY-MM |
| due_on | date | 到期日 |
| category | enum | rent, deposit, utility, repair, fee, adjustment |
| amount | integer | 應收/應付金額 |
| paid_amount | integer | 已核銷金額 |
| status | enum | open, partial, paid, overdue, void |

### payments

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| paid_on | date | 付款日 |
| amount | integer | 金額 |
| method | enum | transfer, virtual_account, cash, card |
| account_last5 | text | 帳號後五碼 |
| memo | text | 銀行備註 |
| matched_bill_id | uuid | 對應帳單 |
| match_status | enum | unmatched, matched, split, overpaid |

### repairs

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| property_id | uuid | 物件 |
| tenant_id | uuid | 房客 |
| reported_on | date | 報修日 |
| issue | text | 問題 |
| responsibility | enum | owner, tenant, company, pending |
| amount | integer | 金額 |
| approval_status | enum | draft, pending, approved, rejected |
| bill_id | uuid | 衍生帳單 |

### reconciliation_runs

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| period | char(7) | 月份 |
| status | enum | draft, reviewing, closed |
| total_receivable | integer | 應收總額 |
| total_received | integer | 實收總額 |
| total_payable | integer | 應付房東總額 |
| exception_count | integer | 異常數 |
| closed_at | timestamp | 鎖帳時間 |

### reconciliation_exceptions

| 欄位 | 型別 | 說明 |
| --- | --- | --- |
| id | uuid | 主鍵 |
| run_id | uuid | 對帳批次 |
| severity | enum | high, medium, low |
| type | enum | unpaid, partial, overpaid, unmatched_payment, contract_gap, repair_unapproved |
| target_id | uuid | 關聯資料 |
| message | text | 說明 |
| status | enum | open, resolved, waived |

## 設計原則

- 所有金流都透過 bill + payment 核銷，不直接改合約租金。
- 月結鎖帳後只能新增 adjustment bill，不覆寫原始紀錄。
- 附件、核准、備註都要有 audit_logs。
- 個資與銀行帳號欄位正式版需加密或遮罩。