Data model

One Postgres database is the system of record for every tenant: relational tables, the Apache AGE knowledge graph, and the pgvector indexes. 34 tables across six schema files in packages/db/src/schema/. Roughly a third of them are deliberately not tenant-scoped — see Multi-Tenancy for why that distinction exists and how it is enforced.

Schema files

File	Tables	Purpose
tenancy.ts	5	Tenants, module entitlements, module metadata, custom fields, audit log
org-structure.ts	7	Departments, programmes, branches, batches, staff categories, class sections, subjects
persons.ts	5	The canonical Person entity plus its addresses, sensitive identifiers, enrollment, and employment records
attendance.ts	4	Student per-class attendance, staff biometric attendance, faculty assignments, delegation requests
reference-data.ts	6	Global demographic lookups (nationality, religion, community, caste, mother tongue, blood group)
auth.ts	7	Better Auth's own tables, generated by better-auth generate

Tenancy and metadata

tenants — one row per institution. Onboarding a new institution is one row here, one Better Auth organization row, and one module_entitlements row. No schema change, no new deployment. Carries regulatoryFramework (defaults to naac_nba_nirf) and betterAuthOrgId, which is the single link between this table and Better Auth's own organization model.

module_entitlements — which of the 20 modules a tenant has licensed. This is the packaging/pricing mechanism, not a separate system. Has a read API and no write API yet.

module_definitions — the metadata-driven engine's core table. Each of M1–M20 is a row whose definition JSONB holds entities, fields, relationships, and permission rules. Seeded by seed.ts. The storage layer is real; the generic consumer that renders routes and forms from this metadata does not exist yet — see What's Built Today.

custom_field_definitions — the mechanism that makes the schema genuinely non-restrictive. An admin adds a row through the settings UI (no migration, no deploy) and it becomes an editable field on that entity's form, stored in the target row's attributes JSONB column and validated at write time against a Zod schema generated from this definition. Supports four entity types (person, studentEnrollment, staffEmployment, attendanceRecord), five field types (text, number, date, boolean, enum), a required flag, and a visibility of public / institution / private — the field-level privacy control M10 promises.

audit_log — populated by mutation-service.ts, never by database triggers. Every row records entityType, entityId, action, before/after JSONB, and the acting person or auth user. Written inside the same transaction as the data write it describes, so the two cannot diverge. The choice of an app-level chokepoint over triggers is deliberate and documented in the source: triggers would be fully automatic but are PL/pgSQL magic outside a TypeScript-first stack, while a shared chokepoint stays consistent with every other enforcement mechanism here and is still atomic.

The canonical Person entity

persons is the one entity every module reads and writes — students, faculty, staff, alumni, guests, and parents all live in it, discriminated by person_type. The design principle is that typed columns are kept to what is genuinely universal and constantly queried; everything long-tail flows through attributes JSONB validated against that tenant's custom_field_definitions.

Two details worth knowing:

• search_vector is a generated, stored tsvector column over first/middle/last name, with a GIN index. Postgres full-text search is effectively free to add now versus reaching for a search service later for something this basic. There is no native tsvector type in drizzle-orm 0.38, so it is declared via customType.
• person_sensitive_identifiers is split into its own table (Aadhaar, ABC ID, passport, PAN) specifically to make field-level privacy possible. Full field-level ACLs are a fast-follow; what matters is that the table boundary exists now, because retrofitting it later is painful.

The demographic reference FKs are all nullable on purpose — not every institution tracks all of them, and the custom-field mechanism covers whatever the typed columns do not.

Enrollment and employment

student_enrollments links a person to a branch, an optional batch, and an optional class section. Note that batch_id is nullable, and that is a data-driven decision rather than a loose one: the real legacy student_admission_master has it nullable too, and 9,021 of 12,954 real historical rows have it NULL. Making it required would have silently dropped 70% of real enrollment history on import. student_status carries the ten legacy statuses (Continuing, Discontinued, Deceased, Debarred, Completed, Long Absent, Drop Out, Withheld, Break of Study, Wrong Entry).

staff_employment is one row per person (unique person_id) with department, staff category, designation, joining/relieving dates, and a working_status of working / relieved / wrong_entry.

Both are soft-deleted — institutional records are almost never physically removed, for compliance and audit reasons ("we need last year's roster for NAAC evidence"). There is deliberately no hard-delete helper in mutation-service.ts.

Academic structure

departments → programmes → branches → class_sections → subjects, all modeled on their legacy counterparts (master_department, master_branch, master_batch, etc.).

class_sections is the concrete "class" unit that attendance and subjects hang off: branch + batch

• semester + section label + academic year, unique on all five within a tenant. It is implicit in the legacy schema's time_table_master_* tables rather than being a distinct table there — this build makes it explicit.

subjects is deliberately a lightweight curriculum entry — code, name, credits, core/elective. It is not full M1 Curriculum: no outcome mapping, no prerequisite graph, no what-if simulator. It exists to make M0's "add curriculum" work as base data.

Attendance

There are two independent attendance concepts here, and conflating them is a common mistake:

attendance_records — a student's presence in a specific class. One row per student per class section per date per subject (periodRef holds the subject code). This is period-level, not a collapsed daily status: attendance follows the real timetable, so a student marked present in period 1 and absent in period 4 has two rows. Where the legacy data has multiple periods of the same subject on the same day, the importer collapses them into one row by majority vote, with ties resolving to present. Uniqueness is enforced on (personId, classSectionId, date, periodRef).

staff_attendance_records — a staff member's own presence, biometric in/out, not tied to any class section. One row per (person, date, session) where session is FN/AN (forenoon/afternoon). The legacy source table only logs presence events — there is no explicit "absent" row for a missed session — so the importer never produces absent; that status exists only for a future manual-correction write path.

Authorization data also lives here. faculty_subject_assignments is not just a scheduling convenience: whether a faculty member may mark attendance for a class section is an authorization fact in the database, checked by the attendance route before allowing a write. It is unique on (tenantId, facultyPersonId, subjectId, classSectionId, academicTerm).

attendance_delegations is the "ask another faculty to take my attendance" feature — a plain table with a simple accept/decline API. It is deliberately not routed through the workflow engine: a same-day delegation request is not a multi-month durable process, and forcing it through DBOS/XState this early would couple M0 to a service that is not proven yet. It would graduate to the workflow service only if delegation grows into multi-level approval chains.

Reference data

Six small lookup tables (ref_nationalities, ref_religions, ref_communities, ref_castes, ref_mother_tongues, ref_blood_groups) that are global, not tenant-scoped — they are platform-managed and rarely change. This is why they are excluded from RLS and why /reference-data is served from an in-memory cache rather than being re-queried per request.

The knowledge graph

The graph is derived from these tables and kept in sync, not hand-maintained:

• Vertices: Person, plus an allowlisted set of org vertices — Department, Branch, Batch, ClassSection, Subject
• Edges: Person -[:ENROLLED_IN]-> Branch, Person -[:TEACHES]-> Subject (with classSectionId as an edge property)

Every vertex and edge carries tenantId as a property, and every query matches on it inside the pattern rather than filtering afterwards. Cypher labels cannot be parameterized, so upsertOrgVertex's label is constrained to a compile-time allowlist rather than accepting an arbitrary string — a correct constraint for a stable ontology, not a limitation.

Rules to follow when extending the schema

1. Every new tenant-scoped table carries tenant_id, and gets added to TENANT_SCOPED_TABLES in schema/index.ts. That one array drives RLS setup, force, hardening, and the policy expression — adding a table anywhere else means it silently has no isolation.
2. Writes go through mutation-service.ts. Direct inserts skip both the audit trail and graph sync, and the audit trail is the only reason the system can answer "who changed this".
3. Reads go through withTenant(). Reading through the raw db object means no app.current_tenant_id is set, and RLS denies every row — which fails closed, but shows up as an empty result set rather than an error, so it is worth checking first when a query "returns nothing".
4. Prefer nullable over required when modeling legacy data. Two schema decisions here exist solely because a real institution had NULLs where the design expected values.