# Database Schema

## Direct answer

The documented schema contains 49 tables. This file lists names and structural metadata only—never customer rows or credential values. `PK` means primary key; `FK` means a declared relationship present in the existing schema map. Some logical relationships are implemented by columns even where the old map did not record a foreign-key constraint.

## Tables

| Table | PK / recorded FK | Important, status, and financial fields | Related API | Related frontend |
|---|---|---|---|---|
| `audit_logs` | PK `id`; FK `employee_id -> employees.id` | `table_name`, `record_id`, `action`, timestamps | mutation helpers | all authenticated write pages |
| `auth_login_failures` | PK `id` | `login_scope`, `ip_hash`, `attempted_at` | staff/customer login | staff login, customer portal |
| `business_idempotency_keys` | PK `id` | `action_name`, key/hash metadata, timestamps | financial mutation helpers | contracts, cash sales |
| `cash_sale_promotions` | PK `id`; logical `cash_sale_id`, `package_id` | `gross_amount`, `installment_subsidy_total`, `net_amount` | cash-sale create | cash-sales.html |
| `cash_sale_refunds` | PK `id`; FK `cash_sale_id -> cash_sales.id`, `refunded_by -> employees.id` | `amount`, `reason`, `idempotency_key` | cash-sale refund | cash-sales.html |
| `cash_sales` | PK `id`; FK `customer_id -> customers.id`, `recorded_by/voided_by/refunded_by -> employees.id` | `sale_code`, `sale_price`, `gross_amount`, `discount_amount`, `net_amount`, `transaction_status`, `refunded_amount`, dates | cash-sales GET/POST/void/refund; dashboard | cash-sales.html, dashboard.html, customers.html |
| `contract_benefits` | PK `id`; logical `contract_id` | `value_amount`, `status` | contract create | contracts.html |
| `contract_documents` | PK `id`; FK `contract_id -> contracts.id`, `uploaded_by -> employees.id` | document path/type, timestamps | contract workflow helpers | contracts.html |
| `contract_payment_intents` | PK `id`; FK `contract_id -> contracts.id`, `schedule_id -> payment_schedules.id` | `intent_no`, `idempotency_key`, requested/principal/penalty/discount amounts, `verification_status`, payment reference/slip path | payment-intents GET/create/verify | contracts.html, customer dashboard |
| `contract_promotions` | PK `id`; logical `contract_id`, `package_id` | `gross_amount`, `installment_subsidy_total`, `net_amount` | contract create | contracts.html |
| `contracts` | PK `id`; FK `customer_id/product_user_customer_id -> customers.id`, `guarantor_id -> guarantors.id`, `created_by/updated_by -> employees.id` | `contract_no`, `contract_status`, finance/product/sale/total/down-payment/loan/installment amounts, lifecycle dates, delivery status | contracts routes; dashboard; follow-ups; payment intents | contracts.html, dashboard.html, customers.html, follow-ups.html, customer dashboard |
| `customer_addresses` | PK `id`; FK `customer_id -> customers.id` | address fields, `updated_at` | customer create/update | customers.html |
| `customer_employments` | PK `id`; FK `customer_id -> customers.id` | employment/income fields, `work_start_date` | customer update | customers.html |
| `customer_references` | PK `id`; FK `customer_id -> customers.id` | reference identity/contact field names | customer/contract KYC | customers.html, contracts.html |
| `customer_referrals` | PK `id`; logical referrer/referred customer IDs | `referral_code_used`, `status` | customer/referral/promotion helpers | customers.html, promotions flows |
| `customer_relations` | PK `id`; FK both customer IDs -> `customers.id` | relationship type | customer deletion/update helpers | customers.html |
| `customer_status_history` | PK `id`; FK `customer_id -> customers.id`, `contract_id -> contracts.id` | `old_status`, `new_status`, timestamps | customer status helpers | customers.html |
| `customers` | PK `id`; FK `created_by/updated_by -> employees.id` | `customer_code`, login identifiers, `portal_status`, `customer_status`, KYC field names, password-hash metadata fields, timestamps | auth/customer CRUD; savings/contracts/cash sales | customers.html, contracts.html, cash-sales.html, customer portal |
| `document_sequences` | PK `sequence_key` (map also records `id`) | current sequence, `updated_at` | document-number helper | indirect |
| `employees` | PK `id`; FK `role_id -> roles.id` | `employee_code`, username, password-hash field, `status`, timestamps | staff login; employees routes | login.html, employees.html |
| `financial_action_requests` | PK `id`; FK `employee_id -> employees.id` | `idempotency_key`, `resource_type`, action state | financial hardening helpers | contracts, cash sales |
| `follow_up_logs` | PK `id`; logical `customer_id`, `contract_id` | `follow_up_date`, `follow_up_type`, `status`, note | payment-pause request; follow-ups | follow-ups.html, dashboard.html, customer dashboard |
| `guarantors` | PK `id`; FK `customer_id -> customers.id` | KYC field names, birthdate, timestamps | contract/customer KYC | contracts.html, customers.html |
| `migration_data_conflicts` | PK `id` | `migration_version`, conflict metadata | migration tooling | developer only |
| `minor_guardians` | PK `id`; FK `customer_id -> customers.id` | guardian KYC field names, birthdate, marital status | customer/contract KYC | customers.html, contracts.html |
| `payment_allocations` | PK `id`; FK `payment_id -> payments.id`, `schedule_id -> payment_schedules.id` | `allocated_amount` | payment helpers | contracts.html |
| `payment_reschedules` | PK `id`; FK `schedule_id -> payment_schedules.id`, `approved_by -> employees.id` | old/new due date, `approval_status`, `fee_amount` | no direct current route | contracts/follow-up domain |
| `payment_schedules` | PK `id`; FK `contract_id -> contracts.id` | installment number, `due_date`, `amount`, `paid_amount`, `penalty_amount`, `status` | schedules; activation; intent verify; dashboard/follow-ups | contracts.html, dashboard.html, follow-ups.html, customer dashboard |
| `payments` | PK `id`; FK `schedule_id -> payment_schedules.id` | `payment_date`, `amount`, method/reference/slip path, `verification_status` | payment-intent verification; dashboard | contracts.html, dashboard.html, customer dashboard |
| `penalty_rules` | PK `id` | grace/penalty rule fields, `updated_at` | payoff/payment helpers | indirect |
| `products` | PK `id` | `product_code`, descriptive variant fields, `cost_price`, `selling_price`, status/timestamps | products GET/POST; contract/cash-sale creation | products.html, contracts.html, cash-sales.html |
| `promotion_benefit_items` | PK `id`; logical benefit/package/product/variant IDs | quantity/value metadata, `updated_at` | package benefit routes | promotions.html |
| `promotion_benefits` | PK `id`; logical `package_id` | benefit type, `value_amount` | package benefit routes; validate | promotions.html |
| `promotion_eligibility_rules` | PK `id`; logical `package_id` | rule type/value | package creation; validate | promotions.html |
| `promotion_event_applications` | PK `id`; logical event/customer/referral/savings/contract IDs | `source_amount`, `reward_amount`, `status`, applied timestamps | eligible/claim; savings/contract/cash create | promotions.html, contracts.html, cash-sales.html, customer dashboard |
| `promotion_event_referrals` | PK `id`; logical application/referral IDs | `qualifying_amount` | referral event helpers | promotion/customer flows |
| `promotion_events` | PK `id` | `event_code`, `event_type`, reward/qualification amounts, start/end dates, `is_active` | promotion-events GET/eligible/claim | promotions.html, contracts.html, cash-sales.html, customer dashboard |
| `promotion_packages` | PK `id`; logical `promotion_id` | `package_code`, fee/discount/subsidy fields, start/end dates, `is_active` | promotions/packages/validate | promotions.html, contracts.html, cash-sales.html |
| `promotion_redemptions` | PK `id`; logical package/contract/cash-sale/savings IDs | `status` | contract/savings/cash create | contracts.html, cash-sales.html |
| `promotions` | PK `id` | `promo_code`, start/end dates, active metadata | promotions GET/POST/toggle; active/packages | promotions.html, contracts.html, cash-sales.html |
| `role_permissions` | PK `id`; FK `role_id -> roles.id` | permission flags including `can_update` | auth/authorization helpers | employees.html |
| `roles` | PK `id` | role name/description | auth; employees | employees.html, login flow |
| `savings_account_credits` | PK `id`; logical account/event-application IDs | `credit_amount`, credit type, `status` | savings account creation with event credit | contracts.html |
| `savings_account_promotions` | PK `id`; logical account/package IDs | gross/fee/discount/subsidy/net amounts | savings account creation | contracts.html |
| `savings_accounts` | PK `id`; logical customer/product/variant/converted-contract IDs | `account_no`, plan type, minimum/target amounts, `start_date`, `status`, promotion totals | savings account create/list/detail/convert; customer/auth summaries | contracts.html, customers.html, customer dashboard |
| `savings_allocations` | PK `id`; logical account/transaction/period IDs | `amount` | savings verification allocator | indirect through savings UI |
| `savings_periods` | PK `id`; logical `account_id` | `period_no`, start/end, target/allocated amounts, `status` | savings create/verify/detail | indirect through savings UI |
| `savings_transactions` | PK `id`; logical `account_id` | `amount`, payment date/channel/reference/slip path, provider metadata, `verification_status`, `idempotency_key` | savings deposit/verify/list/detail | contracts.html, customer summaries |
| `schema_migrations` | PK `version` (map also records `id`) | migration name/checksum/timestamps | migration tooling | developer only |

## Core relationships

```text
customers -> savings_accounts -> savings_transactions -> savings_allocations -> savings_periods
customers -> contracts -> payment_schedules -> payments
contracts -> contract_payment_intents -> payment_schedules
customers -> cash_sales -> cash_sale_refunds
promotions -> promotion_packages -> promotion_benefits / promotion_eligibility_rules
promotion_events -> promotion_event_applications -> savings_accounts / contracts / cash_sales
employees -> roles -> role_permissions
```

## Status-bearing tables

The most consequential status fields are `customers.customer_status`, `customers.portal_status`, `employees.status`, `savings_accounts.status`, `savings_periods.status`, `savings_transactions.verification_status`, `contracts.contract_status`, `payment_schedules.status`, `payments.verification_status`, `contract_payment_intents.verification_status`, `cash_sales.transaction_status`, promotion active/application/redemption status fields, and follow-up status/type fields.