# Schema And Data Model

## Commercial domain

`customers` owns contacts, sites/branches, quotes, invoices, and completion certificates. Catalog pricing is held in categories, products, and customer/product price lists. Quotes own ordered items, approval decisions, and immutable submitted versions. Accepted quotes and approved proformas convert transactionally to invoices with copied line items and a unique source relationship.

Invoices own line items, eTIMS requests/responses, payments, credit/debit note placeholders, and completion certificates. Confirmed payments own one receipt. Payment reversal is retained on the original payment with actor, timestamp, reason, and voided receipt evidence; records are not deleted.

## Operations domain

Cost entries use categories, approval/payment states, evidence fields, and authorization-specific transitions. Employees store statutory identity, national ID, employment details, daily/monthly pay basis, overtime rate, and payment destination. Attendance is unique per employee/day. Payroll runs own payslips calculated from attendance and employee pay basis.

## Governance domain

Users use Spatie roles and permissions. Account status and verification are explicit. Sessions use the database store and are revoked when an account is disabled. `security_events` retains encrypted metadata, actor, request identifier, IP address, event type, and occurrence time. Quote versions retain JSON snapshots that cannot be changed through application workflows.

## Integrity rules implemented

- unique quote, proforma, invoice, receipt, certificate, customer, employee, and payroll identifiers;
- one invoice per source quote/proforma enforced in service transactions and audited for drift;
- one quote snapshot per version;
- one receipt per payment;
- idempotency keys for payments and eTIMS submissions;
- one attendance record per employee/day;
- customer sub-record ownership checks;
- one primary contact per customer through transactional updates;
- confirmed-payment sums are the source of truth for invoice collection state.

## Remaining schema work

Credit/debit note tables exist but require a KRA-approved adjustment lifecycle and eTIMS document contract before production use. Automated M-Pesa requires a provider transaction table when Daraja STK Push is commissioned. Retention/archival tables should be designed only after finance and statutory retention periods are approved.