OMT-SM/supabase/migrations/README.md

Supabase migrations

Numbered, append-only SQL migrations. Each file matches a step from docs/THREAT_MODEL.md and the implementation roadmap. Never edit a migration after it ships — add a new one.

File	Roadmap step	Summary
0001_auth_and_org.sql	1, 2	Schema, roles, shops, tills, profiles, PIN, RLS.
0002_shifts_and_cash.sql	3	Shifts, blind close, append-only cash movements.
0003_transactions_ledger.sql	4	Append-only ledger, hash chain, voids, services.
0004_service_details.sql	5	OMT/recharge/bill/goods/repair detail tables.
0005_inventory_and_float.sql	6	Items, stock lots, vouchers, e-float ledger.
0006_customers_and_kyc.sql	7	Customers, KYC docs, AML thresholds & detection.
0007_receipts_and_evidence.sql	8	HMAC-signed receipts, notifications, evidence.
0008_refunds_and_overrides.sql	9	Refunds, price overrides, collusion views.
0009_external_reconciliation.sql	10	Provider statements, matching, exceptions, close.
0010_reporting_and_alerts.sql	11	Z-report, P&L, scorecards, alert detectors.
0011_hardening.sql	12	pg_cron, daily anchor, key rotation, DDL lock.
0013_user_shift_record_rpcs.sql	13a/b	app.me, my_open_shift, record_* per-service RPCs, fixed Z-report / scorecard / AML views, service catalog seed.

Applying

With the Supabase CLI:

supabase db push

Or manually against a Postgres instance:

psql "$DATABASE_URL" -f supabase/migrations/0001_auth_and_org.sql

Conventions

• All app tables live in the app schema; public stays empty for client SDK type generation comfort. (We can later expose read-only views in public.)
• Every table has RLS enabled and forced from the migration that creates it. No table is ever public-readable.
• All money-changing operations go through SECURITY DEFINER functions, not raw table writes. Migrations 0002+ will add them.
• DELETE is revoked on audit/ledger tables from every role.