Data dictionary

This dictionary lists the key tables of the app database and the control database (about 140, many grouped into shared sections). Table names are aligned with the module specs in the Modules section. The conventions behind every table are in the data model overview, and the relationships are drawn in the ERD.

How to read it:

• Standard columns are not repeated. Unless a table says otherwise, it also has id uuid PK (UUIDv7), tenant_id uuid NOT NULL, UNIQUE (tenant_id, id), created_at, created_by, updated_at, updated_by and row_version. Append-only tables (marked append-only) have no updated_* or row_version.
• FK means a composite foreign key (tenant_id, column) to the named table. Soft ref means a UUID reference without a constraint (used when the target is downstream in the module DAG or partitioned).
• Class is the data classification (C0–C4) for the sensitive columns only; unmarked columns are C1 or C2.
• Money columns are bigint paise. Days are numeric(6,2). Rates are numeric(9,4).
• Enumerated values are text + CHECK. Values are listed in the Notes column.

---

Kernel (sys_)

sys_tenant

The cell-local copy of the tenant record, written only by the provisioning job from the control plane.

Column	Type	Constraints	Notes
id	uuid	PK	Same ID as tenant.id in the control plane. This table has no tenant_id column; RLS uses id.
slug	text	UNIQUE, NOT NULL	Immutable
name	text	NOT NULL	Display name
status	text	CHECK	TRIAL, ACTIVE, PAST_DUE, SUSPENDED, CHURNED, DELETED (mirrors the control plane)
plan_code	text	NOT NULL	Cached for diagnostics; entitlements are checked through the SDK
timezone	text	NOT NULL, default Asia/Kolkata	IANA name
settings	jsonb		Locale, date format, financial-year start

sys_outbox_event

Append-only, partitioned weekly by occurred_at.

Column	Type	Constraints	Notes
id	uuid	PK with occurred_at	Also the CloudEvents id
event_type	text	NOT NULL	<module>.<entity>.<past-tense-verb>
schema_version	int	NOT NULL	Payload schema version
aggregate_type	text	NOT NULL	For example lve_request
aggregate_id	uuid	NOT NULL
aggregate_version	int		For order-sensitive consumers
payload	jsonb	NOT NULL	C0–C2 only
actor	jsonb		User, system or API client; on-behalf flag
trace_context	text		W3C traceparent
occurred_at	timestamptz	NOT NULL	Partition key
published_at	timestamptz		Null until relayed; partial index WHERE published_at IS NULL

sys_inbox_receipt

Append-only. Consumer-side deduplication.

Column	Type	Constraints	Notes
subscriber	text	PK (with event_id)	Handler name, for example att.leave-mark
event_id	uuid	PK
processed_at	timestamptz	NOT NULL	Purged after 14 days

sys_audit_event

Append-only, partitioned monthly by occurred_at; the app role has INSERT and SELECT only.

Column	Type	Constraints	Notes
actor_type	text	CHECK	USER, API_CLIENT, SYSTEM, PLATFORM_SUPPORT, KIOSK
actor_user_id	uuid
on_behalf_of_user_id	uuid
support_grant_id	uuid	Soft ref	Control-plane grant when platform staff act
action	text	NOT NULL	Permission key or <entity>.read for sensitive reads
entity_type	text	NOT NULL
entity_id	uuid
before_hash, after_hash	bytea		SHA-256 of canonical JSON
diff_redacted	jsonb		C3/C4 values replaced by "***"
ip, user_agent, device_id	inet, text, uuid
trace_id	text
occurred_at	timestamptz	NOT NULL	Partition key

sys_idempotency_key

Column	Type	Constraints	Notes
key	text	UNIQUE with tenant_id, user_id, method, path_template	Client-supplied, ≤ 255 chars
user_id	uuid		Null for API clients (then api_client_id)
api_client_id	uuid
method, path_template	text	NOT NULL	For example POST /lve/requests
request_hash	bytea	NOT NULL	A different body with the same key → 422
state	text	CHECK	IN_FLIGHT, COMPLETED
response_status	int
response_body	jsonb		Stored for replay
expires_at	timestamptz	NOT NULL	24 h

sys_file_object

Column	Type	Constraints	Notes
purpose	text	CHECK	LOGO, SELFIE, EMP_DOCUMENT, CLAIM_BILL, IMPORT, EXPORT, PAYSLIP, STAT_FILE, LMS_CONTENT, CERTIFICATE, ANNOUNCEMENT
object_key	text	UNIQUE	t/<tenant_id>/<module>/<yyyy>/<mm>/<uuid>
original_name	text		C2 (may contain names)
content_type	text	NOT NULL	Checked against the purpose allowlist
size_bytes	bigint	NOT NULL
sha256	bytea		Set after upload
status	text	CHECK	PENDING_UPLOAD, UPLOADED, CLEAN, INFECTED, DELETED
data_class	text	CHECK	C0–C4; drives retention
owner_type, owner_id	text, uuid		The entity the file is attached to (authorises downloads)
retain_until	date		Computed from the retention schedule
is_immutable	bool	default false	Object Lock prefix

sys_job_run

The API's "job resource" for asynchronous operations (imports, exports, recomputes).

Column	Type	Constraints	Notes
job_type	text	NOT NULL	Job name from the catalogue, for example rpt.export.generate
status	text	CHECK	QUEUED, RUNNING, SUCCEEDED, FAILED, CANCELLED
progress_pct	int	0–100
params	jsonb		Validated input
result	jsonb		Summary (counts, warnings)
result_file_id	uuid	FK sys_file_object
error	jsonb		problem+json body
requested_by	uuid
started_at, finished_at	timestamptz

---

ORG: organisation and masters

org_legal_entity

Column	Type	Constraints	Notes
legal_name	text	NOT NULL	Registered name
pan_enc, pan_last4	bytea, text		C3, envelope-encrypted
gstin	text
tan	text		Tax deduction account number (for TDS)
cin	text
registered_state	text		ISO 3166-2:IN code, for example IN-TG
address	jsonb

Decision pending: whether one tenant may hold several legal entities with separate EPF/ESI registrations. The table exists from R1 either way; with one entity per tenant, it has a single row.

org_location

Column	Type	Constraints	Notes
legal_entity_id	uuid	FK org_legal_entity
code	text	UNIQUE per tenant	Branch short code, for example KP
name	text	NOT NULL
address	jsonb		Lines, city, district
state_code	text	NOT NULL	Drives PT and LWF
pin_code	text	CHECK 6 digits
timezone	text	NOT NULL	Defaults to the tenant zone
latitude, longitude	numeric(9,6)		Map picker
id_sequence_id	uuid	FK org_id_sequence	Shared or per-branch sequence (SP-ORG-04)
archived_at	timestamptz

org_geofence

Column	Type	Constraints	Notes
location_id	uuid	FK org_location	Several fences per location
name	text
latitude, longitude	numeric(9,6)	NOT NULL	Centre
radius_m	int	CHECK 25–5000	Spryple example: 200 m
allowed_bssids	jsonb		Wi-Fi fallback
qr_secret_ref	text		Rotating QR fallback
is_active	bool	default true

org_department / org_designation / org_grade

Column	Type	Constraints	Notes
code	text	UNIQUE per tenant
name	text	UNIQUE per tenant (case-insensitive)	Duplicates rejected (SP-ORG-02)
parent_id (department)	uuid	FK self	Hierarchy
head_employee_id (department)	uuid	Soft ref	EMP is downstream of ORG
default_grade_id (designation)	uuid	FK org_grade
rank (grade)	int		Ordering for policies ("grade ≥ M2")
archived_at	timestamptz		Blocked while active employees reference it

org_dept_designation (department_id, designation_id) restricts the designations allowed per department (SP-ORG-07).

org_holiday_calendar / org_location_calendar / org_holiday

Column	Type	Constraints	Notes
calendar.name, calendar.year	text, int	UNIQUE (tenant_id, name, year)
location_calendar.location_id, calendar_id, valid_from, valid_to	uuid, uuid, date, date	no overlap per location	Which calendar a location uses, effective-dated
holiday.calendar_id	uuid	FK
holiday.holiday_date	date	UNIQUE (calendar_id, holiday_date)	No duplicate date per calendar
holiday.name	text	NOT NULL
holiday.type	text	CHECK	PUBLIC, OPTIONAL, RESTRICTED

A change emits org.holiday.changed, which triggers attendance recompute and leave re-quotes.

org_id_sequence

Column	Type	Constraints	Notes
scope_key	text	UNIQUE per tenant	For example employee:KP or claim
prefix	text		NE, CL_GEN_
next_value	bigint	NOT NULL	Locked with FOR UPDATE when used
pad_length	int	default 0

org_custom_field_def

Column	Type	Constraints	Notes
entity	text	CHECK	EMPLOYEE, ACCOUNT, CLAIM, PROJECT
key	text	UNIQUE (tenant_id, entity, key)	Stored under this key in the entity's custom_fields column
label	text
data_type	text	CHECK	TEXT, NUMBER, DATE, BOOLEAN, SELECT, MULTI_SELECT
options	jsonb		For selects
validation, visibility	jsonb, text		Required flag, ranges, who can see it
reportable	bool		Reportable fields get an expression index
data_class	text	CHECK	Tenants must classify custom fields

org_account / org_contact

The shared customer/client master used by FFM (CRM) and TSM (projects).

Column	Type	Constraints	Notes
account.name	text	NOT NULL
account.account_type	text		Tenant-configurable list (hospital, distributor…)
account.gstin	text	CHECK format
account.address, latitude, longitude	jsonb, numeric		Used for visit verification
account.owner_employee_id	uuid	Soft ref
contact.account_id	uuid	FK
contact.name, phone, email	text, text, citext		C2

---

IAM: identity and access

iam_user

Column	Type	Constraints	Notes
email	citext	UNIQUE per tenant, nullable	Login identifier
phone	text	UNIQUE per tenant, nullable	E.164; login for frontline staff
employee_id	uuid	Soft ref (EMP is downstream of IAM)	Null for users who are not employees
username	text	UNIQUE per tenant, nullable	Optional
status	text	CHECK	INVITED, ACTIVE, LOCKED, DISABLED
password_hash	text		argon2id; null until activation or for SSO-only users
password_changed_at	timestamptz
failed_login_count	int		Lockout per password policy
mfa_required	bool		Derived from roles; can be forced
last_login_at	timestamptz
locale	text

At least one of email, phone or username is required (CHECK).

iam_identity

Column	Type	Constraints	Notes
user_id	uuid	FK iam_user
sso_connection_id	uuid	FK iam_sso_connection	The connection (protocol OIDC or SAML, provider, config, enforce)
subject	text	UNIQUE (tenant_id, sso_connection_id, subject)	IdP sub / NameID
email_verified	bool		JIT linking only when true

iam_permission / iam_role / iam_role_permission / iam_role_assignment

Column	Type	Constraints	Notes
role.key	text	UNIQUE per tenant	Seeded keys from the role catalogue (org_owner, hr_admin, …); custom roles get custom_*
role.is_system	bool		System roles can be cloned, not edited
role.template_key	text		The seeded role it was cloned from
permission.key	text	UNIQUE (global table, no tenant_id)	<module>.<resource>.<action>, seeded from the RBAC permission matrix; the sensitive flag marks C3/C4 access
role_permission.role_id, permission_id	uuid	UNIQUE pair
role_permission.max_scope	text	CHECK	SELF, TEAM, DEPARTMENT, LOCATION, ORG
assignment.user_id, role_id	uuid	FK
assignment.scope	text	CHECK	The granted scope (≤ max_scope)
assignment.filters	jsonb		Optional location or department list for LOCATION/DEPARTMENT scopes
assignment.valid_from, valid_to	date		Time-boxed grants

line_manager is not assigned manually: it is derived from emp_job_assignment.manager_employee_id. See the RBAC permission matrix.

iam_session

Column	Type	Constraints	Notes
user_id	uuid	FK
device_id	uuid	FK iam_device	Null for web
family_id	uuid	NOT NULL	Refresh-token family; reuse revokes the family
refresh_token_hash	bytea	UNIQUE	SHA-256 of the current token
client_type	text	CHECK	WEB, MOBILE, KIOSK, ADMIN
ip, user_agent	inet, text
expires_at, revoked_at, last_used_at	timestamptz

iam_device

Column	Type	Constraints	Notes
user_id	uuid	FK
platform	text	CHECK	ANDROID, IOS
device_fingerprint	text	UNIQUE (tenant_id, user_id, device_fingerprint)	Install ID + hardware hints
model, os_version, app_version	text
kind	text	CHECK	PERSONAL, KIOSK (push tokens live in mob_push_token)
status	text	CHECK	PENDING_APPROVAL, ACTIVE, REVOKED
approved_by, approved_at	uuid, timestamptz		When the device-approval policy is on

iam_invite

Column	Type	Constraints	Notes
user_id	uuid	FK
token_hash	bytea	UNIQUE	Single-use; the raw token is only in the link
channel	text	CHECK	EMAIL, SMS, WHATSAPP
expires_at	timestamptz	NOT NULL	72 h
used_at, revoked_at	timestamptz

iam_mfa_factor / iam_api_client

Column	Type	Constraints	Notes
mfa.type	text	CHECK	TOTP, RECOVERY_CODES
mfa.secret_enc	bytea		C3, envelope-encrypted
api_client.client_id	text	UNIQUE (global)	Public API OAuth client
api_client.secret_hash	bytea		argon2id
api_client.scopes	jsonb		Permission keys (scope ORG implied, or narrower)
api_client.ip_allowlist	jsonb		Optional
api_client.status, last_used_at	text, timestamptz

---

EMP: core HR

emp_employee

Column	Type	Constraints	Notes
employee_code	text	UNIQUE per tenant	From org_id_sequence
first_name, middle_name, last_name, display_name	text	first and last NOT NULL	C2
work_email, personal_email	citext		C2
mobile	text		E.164, C2
hired_on, joining_date	date	joining_date NOT NULL
probation_end_date, confirmation_date	date
last_working_date	date		Set by EXT
status	text	CHECK	PRE_JOINING, ACTIVE, ON_NOTICE, EXITED, ANONYMISED
photo_file_id	uuid	FK sys_file_object
custom_fields	jsonb		Validated against org_custom_field_def
search_tsv	tsvector	generated, GIN	Name, code, email, phone

emp_job_assignment

Effective-dated job history. Exclusion constraint: no overlapping ranges per employee.

Column	Type	Constraints	Notes
employee_id	uuid	FK
legal_entity_id, location_id, department_id, designation_id, grade_id	uuid	FK ORG tables; location NOT NULL	Facts for policy applicability
manager_employee_id	uuid	FK emp_employee	Drives line_manager and TEAM scope
cost_center_id	uuid	FK org_cost_center
employment_type	text	CHECK	PERMANENT, PROBATION, CONTRACT, INTERN, CONSULTANT, APPRENTICE
work_mode	text	CHECK	OFFICE, FIELD, HYBRID, REMOTE
change_reason	text	CHECK	JOIN, PROMOTION, TRANSFER, REORG, CORRECTION, CONFIRMATION
valid_from, valid_to	date	inclusive	Emits emp.job.changed

emp_reporting_closure

A closure table rebuilt from the current manager_employee_id chain; used by the TEAM scope guard.

Column	Type	Constraints	Notes
ancestor_id, descendant_id	uuid	PK (with tenant_id)
depth	int	NOT NULL	0 = self, 1 = direct report

emp_personal

Column	Type	Constraints	Notes
employee_id	uuid	FK, UNIQUE	One row per employee
date_of_birth	date		C2
gender	text	CHECK	FEMALE, MALE, NON_BINARY, UNDISCLOSED; never used for leave eligibility except where the law requires it
aadhaar_enc, aadhaar_hmac, aadhaar_last4	bytea, bytea, text	aadhaar_hmac UNIQUE per tenant	C4, envelope-encrypted; HMAC for duplicate detection
pan_enc, pan_hmac, pan_last4	bytea, bytea, text		C3
blood_group	text		C3 (health)
residence_address, permanent_address, emergency_contact	jsonb		C2
key_id	text	NOT NULL	The tenant data-key version

UAN and ESIC numbers live in pay_employee_statutory. Reading the encrypted columns needs emp.sensitive.read, and every read is audited.

emp_bank_account

Column	Type	Constraints	Notes
employee_id	uuid	FK
ifsc	text	CHECK IFSC format (4 letters, 0, 6 alphanumerics)
account_no_enc, account_last4	bytea, text		C3
holder_name	text		Name-match check against the employee
is_primary	bool	partial UNIQUE (tenant_id, employee_id) WHERE primary
status	text	CHECK	UNVERIFIED, VERIFIED, FAILED, INACTIVE
valid_from, valid_to	date		Payroll uses the account valid at pay date

emp_family_member / emp_education / emp_experience

Column	Type	Constraints	Notes
family.name, relationship, date_of_birth	text, text, date		C3
family.is_nominee, nomination_pct	bool, numeric(5,2)	nominees sum to 100 per scheme	EPF, gratuity nominations
education.qualification, institution, year	text, text, int		C2
experience.employer, designation, from_date, to_date	text, text, date, date		C2

emp_document

Column	Type	Constraints	Notes
employee_id	uuid	FK
doc_type	text		Tenant list (PAN copy, relieving letter, …)
file_id	uuid	FK sys_file_object
status	text	CHECK	SUBMITTED, APPROVED, REJECTED, SUPERSEDED; HR uploads are auto-approved (SP-EMP-08)
reviewed_by, reviewed_at, reject_reason	uuid, timestamptz, text
expires_on	date		For visas and licences

emp_checklist_template / emp_checklist_item / emp_task

Column	Type	Constraints	Notes
template.kind	text	CHECK	ONBOARDING, OFFBOARDING
template.applicability	jsonb		Departments, locations; null = all
item.title, sort_order	text, int
item.owner_type, owner_ref	text		DEPARTMENT, ROLE, USER, MANAGER: each item has an owner (Spryple screenshot evidence)
item.due_offset_days, evidence_required	int, bool		Relative to joining or LWD
task.employee_id, item_id	uuid	FK; UNIQUE (employee_id, item_id, context_id)
task.context, context_id	text, uuid	CHECK context	ONBOARDING, OFFBOARDING (exit clearance), INDUCTION
task.owner_user_id, due_date	uuid, date
task.status	text	CHECK	PENDING, DONE, NOT_APPLICABLE, OVERDUE
task.comment	text

emp_consent

Append-only (withdrawal sets withdrawn_at through a dedicated function).

Column	Type	Constraints	Notes
employee_id	uuid	FK
purpose	text	CHECK	FACE_BIOMETRIC, LOCATION_TRACKING, SELFIE_CAPTURE, WHATSAPP_MESSAGES, DATA_PROCESSING_NOTICE
notice_version	text	NOT NULL	The text the employee saw
status	text	CHECK	GIVEN, WITHDRAWN
given_at, withdrawn_at	timestamptz
channel, device_id	text, uuid		Evidence

emp_import_batch

Column	Type	Constraints	Notes
import_type	text	CHECK	EMPLOYEES, BANK, COMPENSATION, OPENING_LEAVE, YTD_PAYROLL
template_version	text	NOT NULL
file_id, error_report_file_id	uuid	FK
status	text	CHECK	UPLOADED, VALIDATING, VALIDATED, COMMITTING, COMMITTED, FAILED
row_count, error_count, committed_count	int

---

ATT: attendance

Aligned with the Attendance module spec.

att_policy / att_policy_version / att_policy_assignment

The same three-table shape is used by every module with policies (lve_, pay_, clm_, …).

Column	Type	Constraints	Notes
policy.code	text	UNIQUE per tenant
policy.kind	text	NOT NULL	att.attendance, att.overtime
policy.is_default	bool	one default per kind (partial UNIQUE)	Seeded at provisioning
version.policy_id	uuid	FK
version.version_no	int	UNIQUE (policy_id, version_no)
version.schema_version	text	NOT NULL	Semver of the kind's schema
version.settings	jsonb	NOT NULL	Validated by packages/policy
version.status	text	CHECK	DRAFT, PUBLISHED, SUPERSEDED; published rows are immutable
version.valid_from, valid_to	date	no overlap among published versions
version.published_by, published_at	uuid, timestamptz
assignment.applicability	jsonb	NOT NULL	Rule over job-assignment facts
assignment.priority	int	default 100	Tie-breaker after specificity
assignment.valid_from, valid_to	date

att_shift

Column	Type	Constraints	Notes
code, name	text	code UNIQUE per tenant
start_time, end_time	time	NOT NULL	Local to the location
crosses_midnight	bool	generated (end_time <= start_time)	Night shifts
is_flexible	bool
flex_core_minutes	int		Required hours for flexible shifts
break_minutes	int		Default break
early_window_minutes, late_window_minutes	int		Punch window around the shift
color	text		Roster UI

att_rotation / att_rotation_step / att_weekoff_pattern

Column	Type	Constraints	Notes
rotation.location_id	uuid	FK org_location	Rotations are per location (SP-ATT-07)
rotation.cycle_days	int		Sum of step days
step.step_no	int	UNIQUE (rotation_id, step_no)
step.shift_id, days	uuid, int	FK
weekoff.rule	jsonb		Weekly days plus monthly rules, for example "every Sunday, and the 2nd and 4th Saturday"

att_shift_assignment

Effective-dated; exclusion constraint per employee.

Column	Type	Constraints	Notes
employee_id	uuid	FK
shift_id	uuid	FK, nullable	Either a shift or a rotation (CHECK)
rotation_id, rotation_start_step	uuid, int	FK
weekoff_pattern_id	uuid	FK
valid_from, valid_to	date		A change triggers att.range.recompute
source	text	CHECK	MANUAL, DEFAULT_RULE, SWAP, IMPORT

att_device / att_device_user_map

Column	Type	Constraints	Notes
device.serial_no	text	UNIQUE (global)	Identifies the tenant at ingest
device.vendor, mode	text	CHECK mode	PUSH, PULL_AGENT, CSV
device.secret_hash	bytea		Per-device secret
device.location_id, last_seen_at	uuid, timestamptz		Health monitoring
map.device_user_code	text	UNIQUE (device_id, device_user_code, valid_from)	Device enrolment number
map.employee_id, valid_from, valid_to	uuid, date		SP-ATT-13

att_punch

Append-only, partitioned monthly by punched_at. PK (id, punched_at).

Column	Type	Constraints	Notes
employee_id	uuid	NOT NULL	No FK into or out of partitions; validated by the service
punched_at	timestamptz	NOT NULL	Server-derived for offline punches
direction	text	CHECK	IN, OUT, UNKNOWN (devices often don't know)
source	text	CHECK	WEB, MOBILE, KIOSK, BIOMETRIC, IMPORT, MANAGER, FIELD
device_id, kiosk_id	uuid
client_ref	uuid	UNIQUE (tenant_id, client_ref, punched_at)	Offline-queue idempotency
device_time, received_at	timestamptz		Skew detection
latitude, longitude, accuracy_m	numeric		C4
geofence_id, geofence_result	uuid, text	CHECK result	INSIDE, OUTSIDE, NO_FIX, NOT_REQUIRED, WIFI_OK, QR_OK
selfie_object_key	text		C4; nulled after 180 days
face_match_result, face_score, liveness_score	text, numeric, numeric		MATCH, MISMATCH, NOT_CHECKED
dedupe_key	text		Hash of employee, minute bucket, direction, source family
status	text	CHECK	ACCEPTED, FLAGGED, REJECTED, DUPLICATE
reason	text		Manager entry or rejection reason

att_day

A rebuildable projection: one row per employee per work date. UNIQUE (tenant_id, employee_id, work_date).

Column	Type	Constraints	Notes
employee_id, work_date	uuid, date	NOT NULL	Work date = local date of the shift start
shift_id, policy_version_id	uuid	FK
status	text	CHECK	P, A, HD, W, H, L, HL, WP, HP, OD, WFH, NJ, SEP
first_half, second_half	text	CHECK (same set)	The day status is derived from the halves
paid_fraction	numeric(3,2)	CHECK IN (0, 0.5, 1)
first_in, last_out	timestamptz
worked_minutes, break_minutes, late_minutes, early_out_minutes, permission_minutes	int	≥ 0
ot_minutes, ot_approved_minutes	int		Eligible versus approved
is_late, missed_punch	bool
flags	jsonb		GEOFENCE_EXCEPTION, FACE_MISMATCH, MULTI_SOURCE
input_hash	text	NOT NULL	Recompute is skipped when unchanged
engine_version	text
is_locked	bool		Set by att_period_lock
computed_at	timestamptz

att_request

All attendance request types in one table (work-type requests, regularization, permission, shift change, OT claim).

Column	Type	Constraints	Notes
employee_id	uuid	FK
type	text	CHECK	WORK_TYPE, REGULARIZATION, PERMISSION, SHIFT_CHANGE, OVERTIME
sub_type	text		WFH, WFO, OD, REMOTE; MISSED_OUT, MISSED_IN; SWAP
from_date, to_date	date	from_date <= to_date
from_half, to_half	text	CHECK	FIRST, SECOND, FULL
minutes	int		Permission or OT minutes
payload	jsonb		Type-specific (corrected times, target shift, counterpart employee)
status	text	CHECK	DRAFT, SUBMITTED, IN_APPROVAL, AWAITING_COUNTERPART, APPROVED, REJECTED, WITHDRAWN, CANCELLATION_REQUESTED, CANCELLED
on_behalf, requested_by	bool, uuid		On-behalf requests are auto-approved
reason, attachment_file_id	text, uuid
workflow_instance_id	uuid	FK ntf_instance

att_leave_mark

A projection maintained from LVE events (lve.request.approved, lve.request.cancelled). The day computer reads it so ATT never calls LVE.

Column	Type	Constraints	Notes
employee_id, leave_date, half	uuid, date, text	UNIQUE (tenant_id, employee_id, leave_date, half)
leave_type_code, is_paid	text, bool
lve_request_id	uuid	Soft ref

att_period_lock / att_adjustment

Column	Type	Constraints	Notes
lock.pay_period_id	uuid	Soft ref (PAY is downstream)
lock.period_start, period_end	date	no overlap per tenant and pay schedule
lock.status	text	CHECK	OPEN, PRE_LOCK_CHECK, LOCKED, CLOSED
lock.locked_at, locked_by, override_reason	timestamptz, uuid, text
adjustment.employee_id, work_date	uuid, date		A post-lock change
adjustment.paid_fraction_delta, ot_minutes_delta	numeric(3,2), int
adjustment.target_pay_period_id	uuid	Soft ref	The period whose run settles it (via pay_input)
adjustment.status	text	CHECK	PENDING, APPLIED, WAIVED

mob_kiosk_device / mob_face_profile

Owned by MOB. MOB also owns mob_app_install, mob_push_token, mob_sync_batch and mob_kiosk_punch_batch (sync bookkeeping).

Column	Type	Constraints	Notes
kiosk.location_id, iam_device_id	uuid	FK	The kiosk token is an iam_session on a KIOSK device
kiosk.name, mode	text	CHECK mode	FACE, PIN, FACE_OR_PIN
kiosk.status	text	CHECK	PENDING_ENROLMENT, ACTIVE, REVOKED
face.employee_id	uuid	FK, UNIQUE
face.provider	text
face.template_ref_enc	bytea		C4: provider face ID or local template, envelope-encrypted
face.enrolled_by, enrolled_at	uuid, timestamptz
face.status	text	CHECK	NOT_ENROLLED, VERIFIED, REVOKED
face.consent_id	uuid	FK emp_consent	Required (NOT NULL)

---

LVE: leave

Aligned with the Leave module spec.

lve_type

Column	Type	Constraints	Notes
code	text	UNIQUE per tenant	CL, SL, EL, LOP, COMP_OFF, …
name, color	text
category	text	CHECK	PAID, UNPAID, COMP_OFF, STATUTORY_PARENTAL, SPECIAL
unit	text	CHECK	DAY, HALF_DAY, HOUR
template_code	text		Seed template it came from
is_active	bool

lve_policy (leave_type_id, name) with lve_policy_version (adds transition_mode: how existing balances move to the new version) and lve_policy_assignment follow the shared policy shape.

lve_cycle

Column	Type	Constraints	Notes
cycle_start, cycle_end	date	no overlap per tenant	Apr–Mar or Jan–Dec
status	text	CHECK	OPEN, CLOSING, CLOSED

lve_ledger_entry

Append-only. UNIQUE (tenant_id, source_type, source_id, entry_type).

Column	Type	Constraints	Notes
employee_id, leave_type_id, cycle_id	uuid	FK	The ledger stream
seq	bigint	UNIQUE per stream	Monotonic
entry_type	text	CHECK	OPENING, ACCRUAL, PRORATA_ACCRUAL, AVAIL, AVAIL_REVERSAL, ADJUSTMENT, CARRY_FORWARD_OUT, CARRY_FORWARD_IN, LAPSE, ENCASHMENT, COMP_OFF_CREDIT, COMP_OFF_EXPIRY, LATE_DEDUCTION, ADVANCE, FNF_SETTLEMENT
days	numeric(6,2)	≠ 0	Signed
effective_date	date	NOT NULL
source_type, source_id	text, uuid	NOT NULL	Request, accrual run, adjustment, event
policy_version_id	uuid	FK lve_policy_version
reason	text	required for ADJUSTMENT
reverses_entry_id	uuid	FK self

lve_balance

The projection of the ledger. UNIQUE (tenant_id, employee_id, leave_type_id, cycle_id).

Column	Type	Constraints	Notes
balance	numeric(6,2)		Sum of entries
reserved	numeric(6,2)	≥ 0	Submitted but not approved requests
availed_ytd	numeric(6,2)
last_entry_seq	bigint		Consistency check

lve_request / lve_request_day

Column	Type	Constraints	Notes
request.employee_id, leave_type_id	uuid	FK
request.from_date, to_date, from_half, to_half	date, date, text, text
request.requested_days, charged_days	numeric(6,2)		Charged includes sandwiched days
request.status	text	CHECK	DRAFT, SUBMITTED, IN_APPROVAL, APPROVED, REJECTED, WITHDRAWN, CANCELLATION_REQUESTED, CANCELLED
request.source	text	CHECK	SELF, ON_BEHALF, REGULARIZATION, IMPORT
request.reason, contact_during_leave	text		C2
request.attachment_file_id	uuid	FK	Medical certificate above N days (C3)
request.workflow_instance_id	uuid	FK
day.leave_date, half	date, text	UNIQUE (request_id, leave_date, half)
day.day_kind	text	CHECK	WORKING, WEEK_OFF, HOLIDAY
day.charge	numeric(3,2)	IN (0, 0.5, 1)
day.sandwiched	bool

An exclusion constraint on approved and pending requests prevents overlapping leave for the same employee and half.

lve_comp_off_credit / lve_encashment

Column	Type	Constraints	Notes
credit.employee_id, worked_date	uuid, date	UNIQUE	One credit per worked off-day
credit.worked_minutes, days	int, numeric(3,2)		Half or full credit
credit.expires_on	date		FIFO use by expiry
credit.status	text	CHECK	REQUESTED, CREDITED, PARTIALLY_USED, USED, EXPIRED, REJECTED
encashment.days	numeric(6,2)
encashment.trigger	text	CHECK	CYCLE_END, REQUEST, FNF
encashment.status	text	CHECK	PENDING, APPROVED, PAID, REJECTED
encashment.pay_input_id	uuid	Soft ref to pay_input	PAY is downstream

---

PAY: payroll

Table names follow the Payroll module spec. Amounts are stored as bigint paise (*_paise), per the data model overview.

pay_component

Column	Type	Constraints	Notes
code	text	UNIQUE per tenant	BASIC, HRA, SPL, EPF_EE, …
name, payslip_label	text		Renamable label (SP-PAY-05)
kind	text	CHECK	EARNING, DEDUCTION, EMPLOYER_CONTRIBUTION, REIMBURSEMENT, INFORMATIONAL
calc_type	text	CHECK	FLAT, PERCENT, FORMULA, STATUTORY, INPUT, BALANCING
taxable, pf_wage, esi_wage, pt_wage, gratuity_wage, prorate, part_of_ctc	bool		Statutory wage bases and CTC treatment
statutory_code	text		Links statutory components to engine rules (EPF_EE, ESI_ER, PT, LWF_EE, TDS)
rounding	text	CHECK	NONE, NEAREST_RUPEE, UP_RUPEE, DOWN_RUPEE
display_order	int

pay_structure / pay_structure_version / pay_structure_line

A salary structure template, versioned. It replaces Spryple's "pay group by CTC range".

Column	Type	Constraints	Notes
structure.code, name	text	code UNIQUE per tenant
structure.suggested_for	jsonb		Optional applicability hint for the assignment UI
version.structure_id, version_no	uuid, int	UNIQUE pair
version.status	text	CHECK	DRAFT, ACTIVE, RETIRED; active versions are immutable
version.valid_from, valid_to	date
line.structure_version_id, component_id	uuid	FK; UNIQUE pair
line.formula	text		Sandboxed expression (flat, % of CTC/Basic, slab)
line.is_balancing	bool	one per version	Absorbs the CTC remainder (special allowance)

pay_compensation / pay_compensation_line

Effective-dated compensation per employee. Class C3. Exclusion constraint per employee.

Column	Type	Constraints	Notes
employee_id, structure_version_id	uuid	FK
annual_ctc_paise	bigint	> 0
reason	text	CHECK	JOIN, INCREMENT, PROMOTION, CORRECTION, RESTRUCTURE
valid_from, valid_to	date		Back-dated changes create arrears inputs
approved_by	uuid		Segregation of duties against run approval
line.component_id	uuid	FK; UNIQUE (compensation_id, component_id)	Resolved breakdown
line.monthly_paise, annual_paise	bigint

pay_employee_statutory

Effective-dated statutory profile. Class C3.

Column	Type	Constraints	Notes
employee_id	uuid	FK
uan, esic_ip_no	text		EPF UAN and ESIC insurance number
pf_member, pf_wage_capped	bool		Election of capped or actual PF wages (examples; verify scheme rules)
vpf_percent	numeric(9,4)		Voluntary PF
pt_state, lwf_state	text		Usually the work location's state
tax_regime	text	CHECK	NEW, OLD
valid_from, valid_to	date

pay_schedule / pay_period

Column	Type	Constraints	Notes
schedule.legal_entity_id	uuid	FK
schedule.frequency	text	CHECK	MONTHLY (others later)
schedule.period_start_day	int	1–28	1 = calendar month; 25 = 25th to 24th (SP-PAY-04)
schedule.salary_basis	text	CHECK	CALENDAR, ORG_WORKING_DAYS, FIXED
schedule.fixed_basis_days, pay_day, attendance_cutoff_day	int
period.period_start, period_end, pay_date	date	UNIQUE (schedule_id, period_start)
period.att_window_start, att_window_end	date		Attendance window may differ from the pay period
period.financial_year	text		2026-27
period.status	text	CHECK	OPEN, IN_PROGRESS, CLOSED

pay_run

Column	Type	Constraints	Notes
period_id	uuid	FK
run_type	text	CHECK	REGULAR, OFF_CYCLE, BONUS, FNF, REVERSAL
status	text	CHECK	DRAFT, PREPARED, CALCULATING, CALCULATED, REVIEWED, APPROVED, LOCKED, PAID, REVERSED, CANCELLED
calc_version	int		Incremented by each recalculation
engine_version, stat_pack_version	text		Frozen at prepare
reverses_run_id	uuid	FK self
employee_count, total_gross_paise, total_net_paise	int, bigint, bigint
prepared_by, approved_by, locked_at	uuid, uuid, timestamptz
workflow_instance_id	uuid	FK ntf_instance	Run approval

At most one non-cancelled REGULAR run per period (partial UNIQUE).

pay_run_employee

One row per employee per run. UNIQUE (tenant_id, run_id, employee_id). Immutable after lock (trigger). Class C3.

Column	Type	Constraints	Notes
run_id, employee_id, compensation_id	uuid	FK
status	text	CHECK	PENDING, CALCULATED, ERROR, ON_HOLD, EXCLUDED
basis_days, payable_days, lop_days	numeric(6,2)
gross_paise, total_deductions_paise, net_pay_paise, employer_cost_paise	bigint
input_snapshot	jsonb	NOT NULL after calculation	The exact engine input
input_hash	text		SHA-256 hex of canonical JSON
output_snapshot	jsonb		The engine output
engine_version	text
warnings	jsonb		Variance flags, minimum-wage check, negative net
hold_reason	text		Salary hold

pay_run_line

One component amount for one employee in one run. Class C3. Immutable after lock.

Column	Type	Constraints	Notes
run_id	uuid	FK pay_run	Denormalised so the lock trigger and run-level queries need no join
run_employee_id, component_id	uuid	FK; UNIQUE pair
base_paise, rate	bigint, numeric(9,4)		For example the EPF wage base and 12.0000
amount_paise	bigint		Rounded
ytd_paise	bigint		Financial year to date
source	text	CHECK	STRUCTURE, INPUT, STATUTORY, LOAN, ATTENDANCE

pay_input

One-time inputs for a period: arrears, variable pay, bonus, reimbursements (from claims), encashment (from leave), attendance adjustments, TDS override, other deductions.

Column	Type	Constraints	Notes
employee_id, period_id, component_id	uuid	FK
input_type	text	CHECK	ARREAR, VARIABLE_PAY, BONUS, REIMBURSEMENT, ENCASHMENT, TDS_OVERRIDE, OTHER_DEDUCTION, ATT_ADJUSTMENT
amount_paise, quantity	bigint, numeric(9,2)		Signed amount for adjustments
source_module, source_id	text, uuid	UNIQUE (tenant_id, source_module, source_id)	Idempotent from clm.claim.approved, lve.encashment.approved, att.adjustment.created
status	text	CHECK	PENDING, CONSUMED, CANCELLED

pay_payslip / pay_bank_file / pay_statutory_file

Column	Type	Constraints	Notes
payslip.run_employee_id	uuid	FK, UNIQUE
payslip.payslip_no	text	UNIQUE per tenant
payslip.file_id, password_protected, published_at	uuid, bool, timestamptz		Object Lock
bank_file.run_id, debit_account_id	uuid	FK (org_bank_account)
bank_file.bank_format	text		NEFT_GENERIC, bank-specific adapters
bank_file.file_id, total_paise, row_count, status	uuid, bigint, int, text		GENERATED, UPLOADED, CONFIRMED, FAILED
statutory_file.scheme, state	text		EPF_ECR, ESIC_MC, PT, LWF, TDS_24Q
statutory_file.adapter_version, file_id, status	text, uuid, text		Regeneration creates a new row

pay_loan / pay_loan_ledger

Salary advances (SP-PAY-07) and loans.

Column	Type	Constraints	Notes
loan.loan_type	text	CHECK	SALARY_ADVANCE, LOAN
loan.principal_paise, instalment_paise, outstanding_paise	bigint		outstanding_paise is the ledger projection
loan.interest_rate, instalments, first_recovery_period_id	numeric(9,4), int, uuid
loan.status	text	CHECK	REQUESTED, APPROVED, ACTIVE, CLOSED, WRITTEN_OFF, REJECTED
ledger.entry_type	text	CHECK	DISBURSAL, RECOVERY, ADJUSTMENT, FNF_RECOVERY, WRITE_OFF
ledger.seq, amount_paise, effective_date	bigint, bigint, date	append-only
ledger.run_employee_id	uuid		The recovering run row

pay_tax_declaration / pay_tax_proof

Class C3.

Column	Type	Constraints	Notes
declaration.employee_id, financial_year	uuid, text	UNIQUE pair
declaration.regime	text	CHECK	NEW, OLD
declaration.items, previous_employer	jsonb		Section-wise amounts, rent, prior income and TDS
declaration.status	text	CHECK	DRAFT, SUBMITTED, LOCKED, PROOF_WINDOW, VERIFIED
proof.item_code, claimed_paise, approved_paise	text, bigint, bigint
proof.file_id, status	uuid, text

pay_statutory_registration

Column	Type	Constraints	Notes
legal_entity_id	uuid	FK
scheme	text	CHECK	EPF, ESI, PT, LWF, TAN
state	text		Per state for ESI, PT and LWF (SP-PAY-03)
registration_no	text	NOT NULL
valid_from, valid_to	date

pay_statutory_rate (global reference)

No tenant_id; read-only to the app; imported from the control plane's published statutory pack. A tenant's runs record which pack_version they used.

Column	Type	Constraints	Notes
scheme	text		EPF, ESI, PT, LWF, TDS, GRATUITY, BONUS, MIN_WAGE
state	text		Null for national schemes
pack_version	text	NOT NULL	For example 2026.10.1
rate_table	jsonb		Rates as decimal strings, slabs as arrays. All values are examples to verify.
valid_from, valid_to	date

---

NTF: notifications and workflow

Table names follow the Notifications & Workflow module spec.

ntf_workflow_def / ntf_workflow_version / ntf_workflow_step

Column	Type	Constraints	Notes
def.request_type	text		lve.request, att.request, clm.claim, pay.run, ext.separation, …
version.def_id, version_no	uuid, int	UNIQUE pair
version.on_behalf_mode	text	CHECK	AUTO_APPROVE, SKIP_OWN_STEP, NORMAL
version.applicability, priority, valid_from, valid_to	jsonb, int, date, date		Same resolution as policies
step.step_no	int	UNIQUE (version_id, step_no)
step.resolver, resolver_args	text, jsonb		MANAGER (level N), DEPT_HEAD, ROLE + scope, USER, EMPLOYEE_FIELD
step.condition	jsonb		Expression over the instance context (decision pending: JSONLogic or a CEL subset)
step.mode	text	CHECK	ANY, ALL
step.remind_after_hours, escalate_after_hours, escalate_to, timeout_action	int, int, jsonb, text		SLA handling

ntf_instance

Column	Type	Constraints	Notes
version_id	uuid	FK	Pinned at start
request_type, subject_id	text, uuid	partial UNIQUE while PENDING	One live instance per subject
requester_employee_id, initiator_user_id	uuid		Differ for on-behalf requests
current_step	int
status	text	CHECK	PENDING, APPROVED, REJECTED, WITHDRAWN, CANCELLED
context	jsonb		Facts captured at start (C0–C2)

ntf_task / ntf_action

Column	Type	Constraints	Notes
task.instance_id, step_no	uuid, int
task.assignee_user_id, original_assignee_user_id	uuid	FK iam_user	Index (tenant_id, assignee_user_id, status) for the inbox
task.status	text	CHECK	OPEN, APPROVED, REJECTED, DELEGATED, ESCALATED, SKIPPED, EXPIRED
task.due_at, escalation_level	timestamptz, int
action.actor_user_id, action	uuid, text	append-only; action CHECK	APPROVE, REJECT, SEND_BACK, REASSIGN, COMMENT
action.comment	text	required for REJECT
action.auto, acted_at	bool, timestamptz		Auto-approval and SLA actions are marked

ntf_delegation

Column	Type	Constraints	Notes
delegator_user_id, delegate_user_id	uuid	FK; CHECK they differ
request_types	jsonb		Null = all
valid_from, valid_to	date

ntf_template / ntf_message / ntf_delivery / ntf_preference

Column	Type	Constraints	Notes
template.event_key, channel, locale, version_no	text, text, text, int	UNIQUE set	Tenant overrides shadow platform defaults (rows with a null tenant_id are platform defaults under a separate read-only policy)
template.subject, body	text		Handlebars (sandboxed)
message.recipient_user_id, event_key, channel	uuid, text, text	partitioned monthly by created_at	IN_APP, EMAIL, SMS, WHATSAPP, PUSH
message.status, read_at	text, timestamptz		QUEUED, SENT, DELIVERED, FAILED, SUPPRESSED
delivery.message_id, provider, provider_ref, status, attempt	uuid, text, text, text, int		One row per provider attempt
preference.user_id, event_key, channels	uuid, text, jsonb		Security messages can't be disabled

ntf_webhook_subscription / ntf_webhook_delivery

Column	Type	Constraints	Notes
subscription.url	text	CHECK https://	SSRF checks at create and at send
subscription.event_types	jsonb		Webhook-exposed events only
subscription.secret_enc, secret_next_enc	bytea		HMAC secrets; two active during rotation
subscription.active, consecutive_failures	bool, int		Circuit breaker
delivery.subscription_id, outbox_event_id, attempt	uuid, uuid, int	UNIQUE triple; partitioned monthly
delivery.status_code, duration_ms	int
delivery.status	text	CHECK	PENDING, SUCCEEDED, FAILED_RETRYING, DEAD_LETTERED, REPLAYED
delivery.next_attempt_at	timestamptz

---

RPT: reporting

Table	Key columns	Notes
rpt_dataset	PK key, module, columns, filters, required_permission	Platform-defined (no tenant_id), for example att.monthly_register, pay.register
rpt_saved_view	owner_user_id, dataset_key, columns, filters, shared	Scope is applied at run time from the viewer's grants
rpt_schedule	view_id, cron, format (XLSX, CSV, PDF), recipients, active	Runs in the tenant time zone
rpt_export_job	requested_by, dataset_key, params, format, status, row_count, file_id, expires_at	The API's job resource for exports
rpt_dashboard / rpt_widget	persona, layout; widget dataset_key, chart_type, aggregation	Persona dashboards

Other asynchronous operations (imports, recomputes) use sys_job_run.

---

CLM: claims

Table names follow the Claims module spec.

clm_category

Column	Type	Constraints	Notes
code, name	text	UNIQUE per tenant	Food, travel, mileage, lodging…
kind	text	CHECK	RECEIPT, MILEAGE, PER_DIEM
receipt_required, gst_applicable, is_active	bool

Expense limits live in clm_policy / clm_policy_version / clm_policy_assignment (shared policy shape, kind clm.expense).

clm_claim

Column	Type	Constraints	Notes
claim_no	text	UNIQUE per tenant	From org_id_sequence
employee_id	uuid	FK
source	text	CHECK	MANUAL, MILEAGE_FROM_TRAVEL
requested_paise, verified_paise, approved_paise	bigint
status	text	CHECK	DRAFT, SUBMITTED, SENT_BACK, VERIFIED, APPROVED, PAID, REJECTED, WITHDRAWN
payout_mode	text	CHECK	PAYROLL, BANK_TRANSFER, CASH
policy_version_id	uuid	FK	Limits applied at submission
workflow_instance_id	uuid	FK ntf_instance	Verify, then approve
payout_batch_id	uuid	FK

clm_line / clm_attachment

Column	Type	Constraints	Notes
line.claim_id, category_id	uuid	FK
line.expense_date, bill_no, vendor_name, vendor_gstin	date, text, text, text
line.amount_paise, gst_paise, quantity	bigint, bigint, numeric(9,2)		Quantity = km for mileage
line.verified_paise	bigint		Per-line verified amount (observed in Spryple)
line.line_status	text	CHECK	PENDING, ACCEPTED, REDUCED, REJECTED
line.policy_result	jsonb		Limit checks at submission
line.duplicate_hash	bytea	indexed	Bill number + amount + date + vendor
line.source_ref	uuid		ffm_daily_travel row for mileage lines
attachment.line_id, file_id, image_phash	uuid, uuid, bytea		Perceptual hash for duplicate images

clm_advance / clm_ledger_entry / clm_payout_batch

Column	Type	Constraints	Notes
advance.employee_id, amount_paise, purpose, status	uuid, bigint, text, text		Travel advances, settled against claims
ledger.employee_id, entry_type	uuid, text	append-only	ADVANCE_PAID, CLAIM_APPROVED, ADVANCE_SETTLED, PAYOUT, RECOVERY
ledger.amount_paise, source_type, source_id, effective_date	bigint, text, uuid, date	UNIQUE (source_type, source_id, entry_type)
batch.mode, pay_period_id, total_paise, status	text, uuid, bigint, text	pay_period_id soft ref	Payroll or bank-file payout

---

EXT: exit and full-and-final

ext_reason

Column	Type	Constraints	Notes
kind	text	CHECK	RESIGNATION, TERMINATION, RETIREMENT, DEATH, ABSCONDING, CONTRACT_END
name, is_active	text, bool	UNIQUE (kind, name)

ext_separation / ext_lwd_proposal

Column	Type	Constraints	Notes
employee_id	uuid	FK; one open separation per employee
separation_type, reason_id	text, uuid
requested_on, requested_lwd, approved_lwd	date
notice_days_required, notice_days_served	int		From the ext.notice policy
notice_treatment	text	CHECK	SERVE, RECOVER, WAIVE, PAY_IN_LIEU
status	text	CHECK	SUBMITTED, IN_APPROVAL, APPROVED, REJECTED, WITHDRAWN, CLEARANCE, SETTLED, CLOSED
rehire_eligible	bool
workflow_instance_id	uuid	FK	Employee → line manager → HR
lwd_proposal.proposed_by, proposed_lwd, note	uuid, date, text	append-only	LWD negotiation history

Clearance items are emp_task rows with context = OFFBOARDING and context_id = the separation.

ext_exit_interview / ext_fnf_settlement / ext_fnf_line

Column	Type	Constraints	Notes
interview.answers, status	jsonb, text		C2; restricted to HR (ext.interview.read)
settlement.separation_id	uuid	FK, UNIQUE
settlement.pay_run_id	uuid	FK pay_run (EXT is downstream of PAY)	An FNF run
settlement.total_payable_paise, total_recoverable_paise, net_paise	bigint
settlement.due_date, status	date, text	CHECK status	DRAFT, PREPARED, APPROVED, PAID, CLOSED
line.item	text	CHECK	SALARY_TILL_LWD, LEAVE_ENCASHMENT, GRATUITY, NOTICE_PAY, NOTICE_RECOVERY, LOAN_RECOVERY, ASSET_RECOVERY, BONUS, OTHER
line.quantity, amount_paise, basis	numeric(9,2), bigint, text

---

ENG: engagement

Table	Key columns	Notes
eng_announcement	topic, title, body, publish_from, publish_to, ack_required, pinned, status (DRAFT, SCHEDULED, PUBLISHED, STOPPED, COMPLETED, CANCELLED), author_user_id	Spryple statuses (SP-ENG-01) plus targeting
eng_announcement_audience	announcement_id, rule jsonb, resolved_count	Locations, departments, designations
eng_announcement_read	announcement_id, employee_id UNIQUE pair, read_at, acknowledged_at	Read receipts
eng_recognition_type	name, icon, manager_only, is_active	Birthday, Job Well Done, …
eng_recognition	type_id, from_employee_id, to_employee_id, message (≤ 280 chars), visibility, sent_at	CHECK sender ≠ receiver
eng_reaction	recognition_id, employee_id, emoji_code	UNIQUE per employee and code
eng_celebration	employee_id, kind (BIRTHDAY, WORK_ANNIVERSARY), celebration_date	Generated daily; respects the employee's privacy setting

---

FFM: field force and CRM

Table names follow the Field Force & CRM module spec; accounts are the shared org_account.

Table	Key columns	Notes
ffm_pipeline / ffm_stage	pipeline name, template_code; stage name, sort_order, probability, is_won, is_lost	Configurable stages replace the demo-centric pipeline
ffm_opportunity	account_id, pipeline_id, stage_id, owner_employee_id, expected_value_paise, final_value_paise, stage_entered_on, status	Lead age = today − stage_entered_on
ffm_associate	account_id, associate_type, name, qualification, specialization	For example Doctor, MBBS, Cardiology
ffm_activity	account_id, opportunity_id, employee_id, activity_type (VISIT, CALL, MESSAGE, MAIL, DEMO), due_at, done_at, outcome	Follow-ups
ffm_visit	account_id, employee_id, beat_stop_id, checked_in_at, checked_out_at, latitude, longitude (C4), distance_from_account_m, location_result (MATCHED, NOT_MATCHED, NOT_AVAILABLE), photo_file_id, client_ref UNIQUE	Enforcement mode from the ffm.tracking policy
ffm_beat_plan / ffm_beat_stop	plan employee_id, plan_date UNIQUE pair, status; stop account_id, sequence	Route plans
ffm_track_session	employee_id, work_date, mode (POINT_TO_POINT, CONTINUOUS), consent_id NOT NULL, started_at, ended_at	Only between punch-in and punch-out
ffm_location_point	PK (id, recorded_at), session_id, latitude, longitude, accuracy_m, speed_mps, battery_pct, is_mock	C4; monthly partitions; 90-day retention; no FKs
ffm_daily_travel	session_id, employee_id, work_date, computed_m, revised_m, revise_reason, visit_count, status (PENDING, VERIFIED, APPROVED, CLAIMED, REJECTED), claim_id	Distances in metres; feeds CLM mileage claims
ffm_payment	opportunity_id, method, amount_paise, collected_at, collected_by	Field collections

---

TSM: timesheets

Table	Key columns	Notes
tsm_project	account_id, code UNIQUE, name, manager_employee_id, start_date, end_date, estimated_minutes, billable_default, status
tsm_project_member	project_id, employee_id, project_role, allocation_pct, bill_rate_paise, valid_from, valid_to	Bench = active employees with no current membership
tsm_task	project_id, name, assignee_employee_id, planned_minutes, planned_start, planned_end, status	Effort variance = (actual − planned) ÷ planned
tsm_timesheet	employee_id, week_start UNIQUE pair, total_minutes, status (DRAFT, SUBMITTED, APPROVED, REJECTED), workflow_instance_id	Weekly grid
tsm_entry	timesheet_id, task_id, work_date, minutes, billable, progress_pct, description	Optional cap: daily total ≤ att_day.worked_minutes
tsm_period_lock	period_start, period_end, status

Durations are stored as minutes (the module spec shows hours in the UI).

---

LMS: learning

Table	Key columns	Notes
lms_course	title, category, difficulty, content_type (NATIVE, SCORM), scorm_package_file_id, instructor_user_id, recurrence_months, status	Mandatory compliance courses first (POSH, safety)
lms_module / lms_lesson	module course_id, title, sort_order; lesson module_id, lesson_type, content_file_id, duration_minutes	Module → lesson
lms_assessment / lms_question	pass_percent, max_attempts; question prompt, options, answer_key	answer_key is never sent to learners
lms_assignment_rule	course_id, applicability, trigger (ON_JOIN, ON_JOB_CHANGE, RECURRING), due_days
lms_enrollment	course_id, employee_id, source, due_date, status, score_percent, completed_at
lms_progress	enrollment_id, lesson_id UNIQUE pair, status
lms_attempt	enrollment_id, attempt_no, score_percent, passed
lms_certificate	enrollment_id UNIQUE, certificate_no UNIQUE, file_id, issued_at	Public verification by number
lms_badge / lms_badge_award	badge name, criteria; award badge_id, employee_id, awarded_at	Leaderboards

---

Control-plane database

Separate database, no RLS by tenant (platform staff only), no module prefix. Standard columns apply except tenant_id.

tenant

Column	Type	Constraints	Notes
slug	text	UNIQUE, CHECK lowercase letters, digits and hyphens, 3–31 chars	Immutable
company_code	text	UNIQUE	Human-friendly mobile code
legal_name, display_name	text
status	text	CHECK	PROVISIONING, TRIAL, ACTIVE, PAST_DUE, SUSPENDED, CHURNED, DELETED
cell_id	uuid	FK cell	Routing
region	text	default ap-south-1
owner_email	citext		Invite target
trial_ends_on, activated_at, suspended_at, delete_after	date/timestamptz		Lifecycle
legal_hold	bool		Blocks deletion

tenant_domain / tenant_lifecycle_event / slug_alias

Column	Type	Constraints	Notes
domain.hostname	text	UNIQUE	Custom domain (Enterprise)
domain.verification_token, status, verified_at, cert_status	text, text, timestamptz, text		PENDING_DNS, VERIFIED, ACTIVE, FAILED
lifecycle.from_status, to_status, reason, actor, occurred_at	text…	append-only
slug_alias.old_slug	text	UNIQUE	Redirects after a rename

cell

Column	Type	Constraints	Notes
code	text	UNIQUE	p1, d-acme
type	text	CHECK	POOLED, DEDICATED_DB, DEDICATED_STACK
region	text
api_base_url	text
db_secret_ref	text		Secrets Manager ARN; never the DSN itself
app_version	text		Current release
wave_id	uuid	FK rollout_wave
status	text	CHECK	ACTIVE, DRAINING, MAINTENANCE, RETIRED
capacity	jsonb		Latest tenant count, employee count, DB size, CPU peak
maintenance_window	text		For dedicated cells

plan / plan_entitlement / addon

Column	Type	Constraints	Notes
plan.code	text	UNIQUE	LITE, STANDARD, PROFESSIONAL, ENTERPRISE
plan.pepm_paise, minimum_monthly_paise	bigint		Commercials pending (ADR-013)
plan.max_employees	int		Lite = 50
entitlement.feature_key	text	UNIQUE (plan_id, feature_key)	module.clm, sso.oidc, api.public, limit.storage_gb
entitlement.limit_value	bigint		Null = boolean feature
addon.code, metric, unit_price_paise	text, text, bigint		FFM per tracked user, FACE per enrolled employee, WHATSAPP per message

tenant_subscription / subscription_addon / entitlement_override

Column	Type	Constraints	Notes
subscription.tenant_id, plan_id	uuid	one active per tenant
subscription.billing_cycle	text	CHECK	MONTHLY, ANNUAL
subscription.razorpay_subscription_id	text	UNIQUE
subscription.status	text	CHECK	TRIALING, ACTIVE, PAST_DUE, CANCELLED
subscription.current_period_start, current_period_end	date
subscription_addon.addon_id, quantity	uuid, int
override.feature_key, limit_value, valid_to, reason, approved_by	text, bigint, date, text, uuid		Audited

usage_daily / invoice

Column	Type	Constraints	Notes
usage.tenant_id, usage_date, metric	uuid, date, text	UNIQUE triple	ACTIVE_EMPLOYEES, TRACKED_USERS, SMS, WHATSAPP, STORAGE_MB, FACE_ENROLLED
usage.value	bigint
invoice.number	text	UNIQUE	GST series
invoice.period_start, period_end, subtotal_paise, gst_paise, total_paise	date, date, bigint…
invoice.status	text	CHECK	DRAFT, ISSUED, PAID, OVERDUE, VOID
invoice.lines	jsonb		Plan, add-ons, metered usage

provisioning_run / provisioning_step

Column	Type	Constraints	Notes
run.tenant_id	uuid	FK
run.idempotency_key	text	UNIQUE	From the Idempotency-Key header
run.status	text	CHECK	PENDING, RUNNING, SUCCEEDED, FAILED, COMPENSATED
step.step_key	text	UNIQUE (run_id, step_key)	VALIDATE, SELECT_CELL, CREATE_TENANT_ROW, SEED_REFERENCE_DATA, SEED_ROLES, SEED_POLICIES, CREATE_OWNER_INVITE, CONFIGURE_DOMAIN, SEND_WELCOME
step.attempt, status, error, finished_at	int, text, jsonb, timestamptz		Resumable from the failed step

feature_flag / flag_rule

Column	Type	Constraints	Notes
flag.key	text	UNIQUE	pay.new-run-ui, kill.face-verification
flag.type, default_value	text, jsonb		BOOLEAN, STRING, NUMBER, JSON
flag.is_kill_switch, owner, expires_on	bool, text, date		Stale flags are reported
rule.target_type	text	CHECK	TENANT, CELL, PLAN, PERCENTAGE
rule.target_id, value, priority	text, jsonb, int

release / rollout_wave / cell_rollout

Column	Type	Constraints	Notes
release.git_sha	text	UNIQUE
release.version, migration_ids, notes	text, jsonb, text
wave.wave_no	int	UNIQUE	0 internal/canary, 1 pooled, 2 dedicated
wave.soak_hours	int		24 or 48 (ADR-010)
cell_rollout.cell_id, release_id	uuid	UNIQUE pair
cell_rollout.status	text	CHECK	PENDING, MIGRATING, DEPLOYED, HALTED, ROLLED_BACK
cell_rollout.migrated_at, deployed_at, slo_check	timestamptz, timestamptz, jsonb

platform_user / support_access_grant / audit_event

Column	Type	Constraints	Notes
platform_user.email	citext	UNIQUE	Staff only; MFA mandatory
platform_user.role	text	CHECK	platform_admin, support_engineer, billing_admin, release_manager
grant.tenant_id, platform_user_id	uuid	FK
grant.reason, ticket_ref	text	NOT NULL
grant.access_mode	text	CHECK	READ_ONLY, READ_WRITE
grant.expires_at	timestamptz	≤ 8 h after start
grant.consented_by, consented_at	uuid, timestamptz		Tenant owner, when the plan requires consent
grant.status	text	CHECK	REQUESTED, ACTIVE, EXPIRED, REVOKED, DENIED
audit_event	as sys_audit_event	append-only	Control-plane actions

statutory_pack / statutory_pack_item

Column	Type	Constraints	Notes
pack.version	text	UNIQUE
pack.valid_from, status, published_at, published_by	date, text, timestamptz, uuid		DRAFT, REVIEWED, PUBLISHED
pack.change_log	text		Shown to payroll managers
item.scheme, state_code, rates	text, text, jsonb		Copied into each cell's pay_statutory_rate with the pack version. All values are examples to verify.