Data model overview

These are the rules every table follows. The diagrams are in ERD, and the column-level detail is in the data dictionary. The decisions behind them are ADR-002 (PostgreSQL, Prisma, RLS), ADR-007 (data protection) and ADR-010 (migrations) in the decision log.

1. Databases and schemas

Database	Where	Contents	Access roles
App DB (one per cell)	RDS PostgreSQL 16+	All tenant business data for the tenants in the cell, the kernel tables and global reference data	spyrl_owner (migrations), spyrl_app (API, workers, ingest), spyrl_maint (cross-tenant maintenance), spyrl_report (read replica, reporting views)
Control DB (one per region)	RDS PostgreSQL 16+	Tenants, cells, plans, entitlements, flags, usage, billing, rollout, support access	cp_owner, cp_app

Everything lives in the public schema of each database. A dedicated cell has the same schema as a pooled cell; it simply holds one tenant.

2. Naming

Item	Rule	Example
Module table	<module>_<entity>, singular, snake_case, using the lowercase module code	att_punch, lve_ledger_entry, pay_run_line
Kernel table	sys_<entity>, for infrastructure that every module writes (outbox, inbox, audit, idempotency, files, jobs)	sys_outbox_event, sys_audit_event
Control-plane table	No prefix (it is a separate database)	tenant, cell, plan
Reporting view	rpt_v_<subject>, published and versioned by the owning module	rpt_v_attendance_day
Primary key	id	—
Foreign key	<entity>_id, or <role>_<entity>_id when there are several	employee_id, approver_user_id
Booleans	is_ or has_ prefix	is_active, has_attachment
Instants	_at suffix, timestamptz	punched_at
Business dates	_date or _on suffix, date	work_date, expires_on
Effective range	valid_from, valid_to	—
Money	_paise suffix, bigint	gross_paise
Durations	_minutes suffix, integer	worked_minutes
Status	status (text + CHECK)	status IN ('DRAFT','SUBMITTED',…)
Constraint names	<table>_<columns>_<pk/uk/fk/ck/ex>	lve_request_tenant_id_employee_id_fk
Prisma model	PascalCase mapped with @@map	model LveLedgerEntry → @@map("lve_ledger_entry")

3. Keys and tenancy

• Primary keys are UUIDv7, generated in the application. They are time-ordered (good B-tree locality), unique across cells (so tenant moves never collide), and they leak no counts. When cells run PostgreSQL 18+, the native uuidv7() can become the column default; application generation stays the rule.
• Every tenant-owned table has tenant_id uuid NOT NULL as its first column after id. Global reference tables (statutory rates, countries, states, banks' IFSC directory) have no tenant_id and are read-only to spyrl_app.
• Every tenant-owned table has UNIQUE (tenant_id, id). Children reference parents with composite foreign keys (tenant_id, parent_id) → parent(tenant_id, id), so a row can never point at another tenant's row, even through a bug that bypasses RLS.
• Natural keys are tenant-scoped unique constraints, for example UNIQUE (tenant_id, code) on masters and UNIQUE (tenant_id, employee_code) on employees. Case-insensitive uniqueness uses citext or a unique index on lower(name).
• Indexes lead with tenant_id (for example (tenant_id, employee_id, work_date)), except UUID lookups by id alone.
• Human-friendly numbers (employee code, claim number, payslip number) come from org_id_sequence rows that are locked with SELECT … FOR UPDATE, which gives gap-free, concurrency-safe numbering per location prefix (Spryple's prefix + seed, SP-ORG-04).

4. Standard columns

Column	Type	On	Rule
id	uuid	all	UUIDv7 primary key
tenant_id	uuid	tenant-owned	Set by the repository from TenantContext; checked by RLS WITH CHECK
created_at	timestamptz	all	DEFAULT now()
created_by	uuid	all mutable tables	The user (or system principal) from app.actor_id
updated_at	timestamptz	mutable tables	Set by a trigger
updated_by	uuid	mutable tables	As above
row_version	integer	mutable tables	Optimistic locking: UPDATE … WHERE id = $1 AND row_version = $2; the API exposes it as the ETag
archived_at, archived_by	timestamptz, uuid	masters	Archival (below)
custom_fields	jsonb	entities with custom fields	Validated against org_custom_field_def definitions; GIN index if reportable

Ledgers, events, punches and audit rows are append-only. They have created_at and created_by but no updated_* and no row_version.

5. Delete, archive or keep

Kind of data	What "delete" means	Examples
Masters and configuration	Archive: set archived_at. Archived rows disappear from pickers but stay referenced by history. Re-activation clears it. Deactivation that would orphan active employees is blocked (Spryple SP-X-01 requires a transfer first).	Department, designation, shift, leave type, pay component
Drafts that never took effect	Hard delete	A draft leave request, a draft pay run, an unsent announcement
Requests after submission	Status change, never delete: WITHDRAWN, CANCELLED	Leave, attendance and claim requests
Ledgers and financial records	Never deleted or updated. Corrections are reversal entries.	lve_ledger_entry, pay_loan_ledger, locked pay_run_employee and pay_run_line
Raw evidence with retention limits	Purged by the retention job when the period ends; an aggregate is kept where the policy says so	Punch selfies, raw GPS points
Personal data on erasure request	Anonymised in place (identity fields replaced; statutory records retained as the law requires)	Ex-employee profile after the retention period

There is no generic deleted_at column. A soft-delete flag on every table hides data from queries and makes uniqueness constraints painful; the explicit options above are clearer.

6. Effective dating

Records that change over time and that payroll or leave must read "as of" a date carry valid_from date NOT NULL and valid_to date NULL. Both are inclusive, and a null valid_to means open-ended.

-- No overlapping rows for one employee (requires the btree_gist extension)
ALTER TABLE emp_job_assignment
  ADD CONSTRAINT emp_job_assignment_no_overlap_ex
  EXCLUDE USING gist (
    tenant_id WITH =,
    employee_id WITH =,
    daterange(valid_from, coalesce(valid_to, 'infinity'::date), '[]') WITH &&
  );

-- As-of read
SELECT * FROM emp_job_assignment
 WHERE tenant_id = $1 AND employee_id = $2
   AND valid_from <= $3 AND (valid_to IS NULL OR valid_to >= $3);

Rule	Detail
Effective-dated tables	emp_job_assignment, pay_compensation, att_shift_assignment, att_device_user_map, <module>_policy_version, <module>_policy_assignment, location time-zone changes, statutory rate tables
A change from a date	Close the current row (valid_to = D − 1) and insert a new row from D, in one transaction. The service method is changeFrom(date, patch).
A correction (the old value was wrong)	Update in place; the audit log keeps the before and after. If the corrected period is in a locked payroll period, the change is recorded as a post-lock difference and settled by the next run.
Future-dated changes	Allowed. Promotions and transfers are entered ahead and become active automatically.
Bitemporal history	Not modelled. Payroll reproducibility comes from immutable run snapshots instead (System design).

7. Ledgers

Balances are derived, never edited.

Ledger	Table	Stream key	Projection
Leave	lve_ledger_entry	(tenant_id, employee_id, leave_type_id, cycle_id)	lve_balance
Loans and salary advances	pay_loan_ledger	(tenant_id, loan_id)	pay_loan.outstanding_paise
Comp-off credits	lve_comp_off_credit + ledger entries	per credit	balance per type
Claims advances and payouts	clm_ledger_entry	(tenant_id, employee_id)	advance balance per employee

Rules:

• Entries carry entry_type, a signed quantity, effective_date, source_type + source_id (the request, accrual run or adjustment that caused it), reason and reverses_entry_id for reversals.
• UNIQUE (tenant_id, source_type, source_id, entry_type) makes posting idempotent.
• The projection row (lve_balance) is updated in the same transaction as the entry, under SELECT … FOR UPDATE on the projection row, and stores last_entry_seq. A nightly job recomputes projections from the ledger and alerts on any difference.
• The API exposes the ledger for "why is my balance 7.5?" drill-downs.

8. Enums versus reference tables

Kind of list	Storage	Example
A state machine or system behaviour that code branches on	text column + CHECK constraint; TypeScript union in packages/contracts	lve_request.status, att_punch.source, pay_run.status
A list tenants configure	A tenant-owned master table	Leave types, departments, designations, claim categories, pipeline stages, separation reasons
A list the platform publishes	A global reference table, versioned by effective date	Statutory rates, PT slabs by state, states and districts
A tenant-editable label on a system value	An i18n override, not a new value	"Comp-off" → "Compensatory leave"

Native PostgreSQL ENUM types are not used: adding a value needs a migration outside a transaction, and removing one is not supported. A CHECK constraint changes through a normal expand/contract migration.

9. Column types

Kind	Database type	API (JSON)	Notes
Identifier	uuid	string	UUIDv7
Money	bigint (paise)	integer, field suffix Paise	Never numeric or float for amounts
Rate or percentage	numeric(9,4)	decimal string "12.0000"	EPF 12%, ESI 0.75% (examples to verify)
Days (leave, payable days)	numeric(6,2)	decimal string "1.50"	Halves are common
Duration	integer minutes	integer	Never store hours as decimals
Instant	timestamptz	ISO 8601 with offset	Stored in UTC
Business date	date	YYYY-MM-DD	Local to a location's time zone
Time of day	time	HH:mm	Paired with the location's IANA zone
Time zone	text (IANA name)	string	Asia/Kolkata
Short code	text + CHECK (char_length(code) <= 32)	string	No varchar(n)
Email	citext	string	Lower-cased comparison
Phone	text in E.164	string	+919876543210
Coordinates	numeric(9,6) latitude and longitude	number	Geo-fence checks in the application (haversine). Decision pending: PostGIS for R3 field force (beat plans, route queries).
Flexible document	jsonb	object	Always validated by a zod schema before writing (policy settings, snapshots, custom fields)
Encrypted value	bytea ciphertext in a *_enc column + a key_id; a separate *_hmac bytea for equality lookups and a *_last4 text for display	masked string	Envelope encryption (ADR-007)
Hash	bytea (SHA-256) or text hex	hex string	input_hash, file checksums

10. Partitioning

High-volume, append-mostly tables are range-partitioned by time, with partitions created ahead by pg_partman (or a scheduled job) and dropped by the retention job.

Table	Partition key	Partition size	Retention	Notes
att_punch	punched_at	Monthly	8 years for punch facts; selfie objects 180 days (the key is nulled)	PK (id, punched_at); unique (tenant_id, client_ref, punched_at)
ffm_location_point	recorded_at	Monthly (weekly when a cell exceeds about 50M rows per month)	90 days raw, then ffm_daily_travel aggregates	Written by ingest in batches with COPY
sys_audit_event	occurred_at	Monthly	8 years	INSERT-only grant
sys_outbox_event	occurred_at	Weekly	7 days after publication	Dropped by partition
ntf_message	created_at	Monthly	1 year	Delivery status for the inbox
ntf_webhook_delivery	created_at	Monthly	30 days	Replay window

Rules for partitioned tables: the primary key must include the partition key; no foreign keys point into a partitioned table (references such as att_day → punch are by query, not by FK); every partition gets the same indexes ((tenant_id, employee_id, punched_at) for punches); queries must include a time-range predicate so pruning works, which a lint check on repository methods enforces.

11. Row-Level Security template

Every tenant-owned table gets the same policy. A migration helper (enable_tenant_rls('table_name')) generates it, and CI fails if any table with a tenant_id column lacks it.

-- Roles (created once per database by the platform bootstrap)
CREATE ROLE spyrl_owner NOLOGIN;                       -- owns tables, runs migrations
CREATE ROLE spyrl_app LOGIN NOBYPASSRLS;               -- api, worker, ingest
CREATE ROLE spyrl_maint LOGIN BYPASSRLS;               -- cross-tenant maintenance jobs only
CREATE ROLE spyrl_report LOGIN NOBYPASSRLS;            -- reporting replica, rpt_v_* views only

-- Template applied to every tenant-owned table
ALTER TABLE lve_request ENABLE ROW LEVEL SECURITY;
ALTER TABLE lve_request FORCE ROW LEVEL SECURITY;      -- applies to the table owner too

CREATE POLICY tenant_isolation ON lve_request
  AS PERMISSIVE
  FOR ALL
  TO spyrl_app, spyrl_report
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON lve_request TO spyrl_app;

-- Append-only tables: no UPDATE or DELETE for the app role
GRANT SELECT, INSERT ON lve_ledger_entry TO spyrl_app;
GRANT SELECT, INSERT ON sys_audit_event TO spyrl_app;

-- Global reference data: readable by everyone, writable only by migrations and the statutory-pack importer
GRANT SELECT ON pay_statutory_rate TO spyrl_app, spyrl_report;

-- Per-transaction context, issued by the unit of work (never session-level SET)
SELECT set_config('app.tenant_id', '01928f00-0000-7000-8000-00000000a001', true);

• nullif(…, '') makes the policy fail closed: if the setting is missing, the comparison is with NULL, and no rows are visible or writable.
• Locked payroll data is protected by a trigger as well as by grants:

CREATE FUNCTION pay_block_locked_changes() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM pay_run r
              WHERE r.tenant_id = OLD.tenant_id AND r.id = OLD.run_id
                AND r.status IN ('LOCKED','PAID','REVERSED')) THEN
    RAISE EXCEPTION 'pay run % is locked', OLD.run_id USING ERRCODE = 'P0001';
  END IF;
  RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END $$;

CREATE TRIGGER pay_run_employee_locked_guard
  BEFORE UPDATE OR DELETE ON pay_run_employee
  FOR EACH ROW EXECUTE FUNCTION pay_block_locked_changes();

CREATE TRIGGER pay_run_line_locked_guard        -- pay_run_line carries run_id too
  BEFORE UPDATE OR DELETE ON pay_run_line
  FOR EACH ROW EXECUTE FUNCTION pay_block_locked_changes();

• The CI coverage check:

SELECT c.relname
  FROM pg_class c
  JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id'
 WHERE c.relkind IN ('r','p') AND c.relnamespace = 'public'::regnamespace
   AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity
        OR NOT EXISTS (SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid));
-- must return zero rows

12. Concurrency

Situation	Technique
Editing a record in a form	Optimistic locking with row_version (If-Match header)
Posting to a balance	SELECT … FOR UPDATE on the projection row inside the unit of work
Computing one attendance day	`pg_advisory_xact_lock(hashtextextended(tenant_id
Running payroll for a tenant	A single q.pay job group per tenant (limit 1) plus a pay_run status check under FOR UPDATE
Generating a sequence number	SELECT … FOR UPDATE on the org_id_sequence row

13. Migration rules (expand/contract)

1. Expand (release N): add tables, nullable columns or columns with defaults, new indexes (CREATE INDEX CONCURRENTLY, in a migration file marked non-transactional), and new CHECK values. Existing code keeps working.
2. Migrate code (release N): write to old and new; read from new with a fallback.
3. Backfill (a job, not the migration): batches of 5,000 rows per tenant under spyrl_maint, throttled, resumable, with progress in sys_job_run.
4. Contract (release N+1 or later): make the column NOT NULL, drop old columns or constraints, remove the fallback.

Rule	Why
No DROP, RENAME or type change in the same release as the code change	Rollback is redeploying the previous image (ADR-010)
SET lock_timeout = '3s' and statement_timeout = '15min' at the top of every migration	Never queue behind a long transaction at peak
No migration rewrites a large table (for example ALTER COLUMN TYPE on att_punch)	Use a new column + backfill
Each migration ships with its RLS call, grants and classification tags	Coverage checks run in CI
Prisma migrate generates SQL that is reviewed and edited by hand when needed (partitioning, RLS, exclusion constraints, triggers)	Prisma can't express these
Migrations run per cell through the control plane's rollout orchestrator; a failure halts the wave	Blast radius
The per-tenant export/import tool is updated in the same PR as any schema change	Tenant moves and single-tenant restore (ADR-011)
The payroll-window freeze (25th to 5th) applies to migrations touching pay_*, att_* or lve_*	Month-end safety

14. Data classification

Each column and each file purpose carries a classification tag. Tags are written as Prisma doc comments and as COMMENT ON COLUMN in SQL, and a generated registry (packages/db/classification.json) drives log redaction, export masking, field-level permissions and retention.

model EmpPersonal {
  /// @class C4 @purpose statutory-kyc @encrypt envelope @mask last4
  aadhaarEnc  Bytes   @map("aadhaar_enc")
  /// @class C3 @purpose payroll-tax @encrypt envelope @mask last4
  panEnc      Bytes   @map("pan_enc")
}

Class	Meaning	Examples	Controls
C0 Public	Safe to publish	Company name, logo, public holidays	None
C1 Internal	Business data without personal impact	Department names, shift timings, policy settings	Tenant-only access
C2 Personal	Identifies a person	Name, work email, mobile, employee code, designation, manager	Scope-checked access; included in DPDP access requests
C3 Sensitive personal	Financial or identity data with harm potential	Salary and compensation, bank account, PAN, tax declarations, family details, medical leave documents	Extra permission (pay.compensation.read, emp.sensitive.read); masked in lists and exports; never logged; read access audited
C4 Special	Biometric, precise location, government ID	Aadhaar, face templates, punch selfies, GPS tracks	Envelope encryption or short retention; explicit consent (emp_consent); purpose-bound use only; never in events or webhooks

Rules: event payloads and webhooks may contain C0–C2 only; logs may contain C0–C1 and IDs; analytics exports mask C3 and exclude C4 unless the user holds the specific permission and the export is audited.

15. Retention

The sys.retention.purge job applies this schedule per tenant. Tenants on Professional and Enterprise can lengthen (never shorten below statutory minimums) the configurable rows. Statutory periods are examples to verify with counsel.

Data	Default retention	Basis	Action at the end
Payroll runs, payslips, statutory files	8 years after the financial year	Tax and labour record-keeping (verify)	Delete; Object Lock expires
Attendance days and punch facts	8 years	Wage records (verify)	Delete partitions
Punch selfies and kiosk photos	180 days	Evidence for disputes	Delete the object; null the key
Raw GPS points	90 days	Purpose limitation	Drop partitions; keep ffm_daily_travel aggregates
Face templates	Until consent is withdrawn or 30 days after exit	Consent	Delete the template at the provider and locally
Employee profile after exit	8 years, then anonymise	Statutory records	Anonymise C2–C4 fields
Candidate / pre-joining data that never joined	180 days	Purpose limitation	Delete
Leave and claims records	8 years	Financial records	Delete
Audit events	8 years	Accountability	Drop partitions
Notifications (in-app)	1 year	Usability	Drop partitions
Webhook deliveries	30 days	Replay window	Drop partitions
Outbox events	7 days after publication	Operational	Drop partitions
Idempotency keys	24 hours	Operational	Delete
Sessions and refresh tokens	30 days after expiry	Security forensics	Delete
Import files and generated exports	30 days	Operational	Delete objects
Whole tenant after churn	30 or 90 days after the export window, per contract	Contract	Delete, issue a deletion certificate (ADR-011)