# CloudAudit data model and migration plan

## Data rules

- Use `BIGINT UNSIGNED` primary keys, UTC timestamps, `DECIMAL(19,4)` money/quantity fields, and `CHAR(36)` public UUIDs.
- Every business table contains `organization_id`; branch-specific tables also contain `branch_id`.
- Store money currency code alongside document totals; MVP posts in the organisation base currency.
- Add `created_by`, `updated_by`, and appropriate foreign keys. Never use hard delete for posted/compliance records.

## Entity map

```text
Organisation → Branch → Warehouse → Stock level
Organisation → User ↔ Role ↔ Permission
Product → Stock movement ← Sale item / Goods receipt item / Adjustment
Customer → Invoice → Invoice item → Sale
Supplier → Purchase order → Goods receipt → Bill
Source document → Journal entry → Journal entry lines → Account
Payment → Payment allocation → Invoice or Bill
```

## Migration sequence

1. Foundation: organisations, branches, users, password/session tables, roles, permissions, role_permissions, user_roles, settings, audit_logs.
2. Master data: units, categories, tax_rates, customers, suppliers, product tables, warehouses, payment_methods, document_sequences.
3. Inventory: stock_levels, stock_movements, stock_adjustments, stock_transfer headers/items.
4. Finance base: accounts, accounting_periods, journal_entries, journal_entry_lines, cash_bank_accounts.
5. Sales: sales, sale_items, invoices, invoice_items, payments, payment_allocations, returns, return_items.
6. Purchasing: purchase_orders, purchase_order_items, goods_receipts, goods_receipt_items, bills, bill_items.
7. Reporting and AI: report_exports, notifications, ai_conversations, ai_messages, ai_requests, ai_tool_calls, ai_usage, ai_action_proposals.

## Essential constraints and indexes

- Unique: `(organization_id, public_uuid)` on public entities; `(organization_id, sku)` for products; document type/branch/number sequence; `(organization_id, provider, provider_request_id)` for AI idempotency.
- Index: `(organization_id, branch_id, occurred_at)` for operational events; `(organization_id, product_id, warehouse_id)` for stock; `(organization_id, account_id, posted_at)` for ledger reporting.
- Foreign keys: source/document lines cannot cross organisations; journal line account and journal organisation must match.
- Check constraints/application guards: positive quantity where appropriate, non-negative tax rate, valid status transition, and debit XOR credit per journal line.

## Transaction boundaries

Posting services run inside a database transaction and use a unique idempotency key supplied by the client. They must acquire the product/warehouse stock row lock before validating and updating stock. Reports use posted records only. Read models/caches may be refreshed after commit but are never the source of accounting truth.