Data Model

Data model

This page is a map of the relational schema: the 47 entities grouped by area, the conventions they all follow, and the design rules that keep the model typed rather than generic. It is for anyone reading packages/database/prisma/schema.prisma or adding a table to it.

The schema is the single source of truth. Where this page and the schema disagree, the schema is right.

Where it lives

Path	Contents
packages/database/prisma/schema.prisma	The whole model. 47 models, 59 enums.
packages/database/prisma/migrations/	Two migrations. See Upgrades and migrations.
packages/database/src/enums.ts	Every enum mirrored as a literal tuple, with a compile-time parity proof.
packages/database/src/schemas/	The Zod write contract per aggregate. Pure Zod, imports nothing generated.
packages/database/src/tenant.ts	The tenant scoping client extension.
packages/database/src/audit.ts	The audit hash chain.
packages/database/src/forms.ts	The form promotion rule.
packages/database/src/seed/	A deterministic synthetic demo practice.

The package is deliberately storage only. FHIR serialisation is @openrunic/fhir's job.

The core cluster

erDiagram
    Organisation ||--o{ Facility : "has"
    Organisation ||--o{ User : "has"
    Organisation ||--o{ Patient : "has"
    Patient ||--o{ Coverage : "insured by"
    Payer ||--o{ Coverage : "issues"
    Patient ||--o{ Appointment : "books"
    Facility ||--o{ Appointment : "hosts"
    User ||--o{ Appointment : "attends"
    Appointment ||--o| Encounter : "becomes"
    Patient ||--o{ Encounter : "has"
    Encounter ||--o{ ClinicalNote : "documents"
    ClinicalNote ||--o{ NoteAddendum : "corrected by"
    Encounter ||--o{ Condition : "diagnoses"
    Encounter ||--o{ ServiceRequest : "orders"
    ServiceRequest ||--o{ DiagnosticReport : "resulted by"
    DiagnosticReport ||--o{ ResultObservation : "contains"
    Encounter ||--o{ ChargeItem : "captures"
    ChargeItem ||--o{ ClaimLine : "billed as"
    Claim ||--o{ ClaimLine : "contains"
    Claim ||--o{ ClaimStatusHistory : "tracked by"
    Remittance ||--o{ RemittanceLine : "contains"
    Payment ||--o{ PaymentAllocation : "applied by"
    Organisation ||--o{ AuditEvent : "records"

Loading

The 47 entities by area

Tenancy and platform

Model	What it represents
Organisation	The tenant root. The only model without a tenantId, because its id is the tenant id. Carries slug, mode (SaaS or self host), status, timezone, and a per-tenant flags JSON object. Deleting it cascades the whole tenant.
Facility	A place of service. Carries code, npi, posCode (the CMS place-of-service code), timezone, and address. Scheduling, encounters, and charges are all facility-scoped.

Identity and access

Model	What it represents
User	Staff and clinicians. There is no separate practitioner table: a practitioner is a User with isProvider true, carrying npi, dea, credential, and a NUCC taxonomyCode.
UserFacility	Which facilities a user may work in. The policy layer intersects these with role permissions.
Role	A named permission bundle. System roles are seeded per tenant, so a tenant can fork them.
Permission	One atomic capability such as patient.read. The catalogue is data rather than code, so a plugin can register its own.
RolePermission	Join table between the two.
RoleAssignment	Binds a user to a role, optionally narrowed to one facility. A null facilityId means org-wide.

Registration and coverage

Model	What it represents
Patient	A slim patient core: MRN, name parts and preferred name, birth date, sex at birth and gender identity code, pronouns, race and ethnicity code arrays, language, primary contact and address, sensitivity class, portal flag.
PatientIdentifier	Every identifier that is not the MRN: driver's licence, payer member id, an external system's id. Carries use, system, value, and a validity period.
RelatedPerson	Guardians, emergency contacts, and portal proxies, with the relationship code and the three role flags that matter operationally.
Payer	An insurance company, with the identifiers X12 needs: x12PayerId, claimFilingCode, eligibilityPayerId.
Coverage	One policy in one coordination-of-benefits slot. Carries rank (primary, secondary, tertiary), memberId, groupNumber, subscriber relationship, copay and deductible in cents, and acceptAssignment.

Scheduling

Model	What it represents
Appointment	A booked slot. Appointment type is carried inline as typeCode plus typeDisplay, so renaming a type never rewrites history. Carries recurrence group and rule, createdVia, room, and checkedInAt. Its indexes are named for the two screens that read it: the day view and the flow board.
AppointmentStatusHistory	An append-only status trail. Wait timers derive from it, so there is no separate timing table.

Clinical

Model	What it represents
Encounter	A visit, created automatically on check-in from the appointment. Holds a one-to-one link back to that appointment, plus class, status, reason, and signature stamps.
ClinicalNote	A block-structured clinical document. blocks is a JSONB block list, not HTML and not a serialised template.
NoteAddendum	A signed note is immutable. Later corrections are addenda carrying their own blocks, reason, and signature.
Condition	Problem-list entries and encounter diagnoses, split by category. Codes default to ICD-10-CM with an optional SNOMED CT code alongside.
MedicationStatement	What the patient reports taking, including prescriptions written elsewhere.
MedicationRequest	A prescription. sig is structured JSON from the sig builder, with the rendered sigText stored alongside. Carries quantity as a decimal, refills, days supply, dispense-as-written, controlled schedule, and the pharmacy's NCPDP id.
AllergyIntolerance	Type, category, criticality, clinical status, substance code, reaction codes, and severity.
Immunization	CVX, MVX, and NDC codes, lot number, dose quantity, administration time, VIS date, refusal reason, and registry reporting time.
Observation	Vitals, in-house labs, social-history and SDOH answers, survey scores. Typed value columns rather than one text blob. Carries a formSubmissionId when the value was promoted out of a form.
ConsentGrant	Recorded consent or authorization, scoped by scope and tracked through status, with revocation time and reason.

Discrete results from an outside lab go to ResultObservation rather than Observation, so a report's lines can never orphan from their report.

Orders, results, documents, and work

Model	What it represents
ServiceRequest	An order. Labs, imaging, procedures, referrals, and therapy are one table split by category. Carries priority, intent, ask-at-order-entry answers, requisition number, and an opaque adapter reference.
Specimen	A collected specimen: accession number, type, collection method, collector, volume, and rejection reason.
DiagnosticReport	A result report. Carries the abnormal flag, a narrative, a storage key for the original message or PDF, and the reviewer stamp. Its index on reviewer and abnormal flag is the sign-off queue.
ResultObservation	One discrete analyte inside a report, sequenced and uniquely keyed within it.
Document	Any stored binary: uploads, scans, inbound faxes, generated PDFs. Bytes never live in Postgres; the row carries a storage key, content type, SHA-256, and size.
Task	The typed inbox. One work engine, where the streams a user sees are filters on type rather than separate systems. Its unique constraint on the source event makes routing idempotent under at-least-once delivery.
MessageThread	One threading model for both portal messaging and staff chat, split by kind.
Message	A message in a thread, with a sender type that covers users, patients, related persons, and the system.

Billing and revenue cycle

Model	What it represents
ChargeItem	A fee-sheet line. Codes default to CPT. diagnosisPointers are 1-based indices into the claim's diagnosis list, matching the 837P service-line field. Money is integer cents.
Claim	The claim lifecycle spine. Carries status, frequency, the diagnosis code list, four money totals, self-relations for secondary claims and corrections, control numbers, and a snapshot of the as-built payload so a resubmission is reproducible even after the encounter is edited.
ClaimLine	A service line, sequenced within the claim, with a foreign key back to the charge it came from.
ClaimStatusHistory	Every claim transition, tagged with its source. Acknowledgements, status responses, and remittances all write here, which is why there is no separate transaction-history screen.
Payment	Money received, from a patient or a payer. Card data is never stored; only an opaque gateway reference.
PaymentAllocation	Applies part of a payment to a claim, line, or charge, carrying the X12 adjustment group and reason codes. A patient's balance is charges minus allocations, one ledger with no second source of truth.
Remittance	An electronic remittance advice. Parsing produces lines; posting produces payments and allocations.
RemittanceLine	One service line within a remittance, with the payer control number used for matching, the money breakdown, adjustment codes, remark codes, and a matched flag. Unmatched lines become exception tasks rather than being dropped.
Statement	A patient statement, its dunning cycle, its generated PDF key, and a single-use pay-link token that rotates on every regeneration.

Forms

FormDefinition, FormSubmission, and FormPromotedValue. See Form engine.

Terminology and audit

Model	What it represents
TerminologyCode	A bring-your-own terminology cache. openrunic ships no code content, because the major code systems carry their own licences. system is a string rather than an enum, so a new system loads without a migration.
AuditEvent	The append-only audit stream. See Audit and hash chain.

Enums

There are 59 enums. Each is mirrored in packages/database/src/enums.ts as a plain literal tuple, so the Zod schemas run without a generated Prisma client. That file ends with a type-only parity proof: adding a value to an enum without updating its tuple fails pnpm --filter @openrunic/database type-check rather than failing at runtime.

Conventions

These are stated in the schema header and hold across the whole model.

Identifiers. UUIDv7, minted in application code, with no database default. No serial columns, no dual keying, no separate public-id overlay. packages/database/src/uuid.ts provides uuidv7, isUuidv7, and uuidv7Timestamp.

Tenant prefixing. Every composite index and every unique constraint is tenant-prefixed, with documented exceptions for child-scoped uniques that reach the tenant through their parent. See Multi-tenancy and isolation.

Enums versus coded strings. A Prisma enum is used only where the value set is closed and owned by the project or by a frozen HL7 or FHIR value set. Anything from a licensed or evolving terminology (ICD-10-CM, CPT, HCPCS, LOINC, SNOMED CT, RxNorm, CVX, NDC, UCUM, and the X12 adjustment and remark code sets) is a plain string code plus a codeSystem string, optionally resolvable against TerminologyCode. Terminology is never vendored into the repository, so it must never be baked into the schema.

Relations versus provenance stamps. A foreign key is declared where the reference is part of the record's meaning and is queried. The who-stamped-this fields (signedById, reviewedById, postedById, publishedById, voidedById, completedById) are plain indexed UUID columns holding a user id with no declared relation, because they must survive a user being deactivated and never drive a join in a hot path.

Money and quantities. Money is integer cents in an Int column, never a float. Genuinely fractional quantities are Decimal with explicit precision, for example Decimal(12,3) for dispense quantity.

Deletion. Clinical rows are never hard-deleted. A correction is a status transition to ENTERED_IN_ERROR, VOIDED, or CANCELLED, plus an audit event.

Timestamps. createdAt and updatedAt on every model, including the append-only ones.

What is deliberately not entity-attribute-value

The schema says this outright in three places, and the reasoning is worth understanding before proposing a generic attribute table.

Patient is a slim core. Custom demographics live in a form submission bound to the patient, never in extra columns on the table and never in an attribute-value table. The primary address and contact points are inline because every screen needs them.

FormSubmission.values is one JSONB document validated against the pinned definition's generated schema. There is exactly one row per submission, never one row per answered field.

FormPromotedValue is a fixed table with typed columns rather than per-form dynamic DDL, because dynamic DDL would fight the expand-and-contract migration discipline. Graphing a promoted field over time is one index scan rather than a join tree.

The failure mode being avoided is a well-known one in this category of software: a customization layer that is genuinely powerful but hostile to use, configured through cryptic option strings with no preview, storing every answer as a row in a generic attribute table. Every query against such a store is a self-join, every value is a string, and nothing is typed.

Client construction

packages/database/src/client.ts exports createPrismaClient, a lazy factory rather than a module-level singleton. Nothing is read from the environment and no client is constructed at import time, so importing the package never requires a configured database. Call it once per process and share the instance; each client owns a connection pool.

It uses the Prisma driver adapter for Postgres:

const adapter = new PrismaPg({ connectionString });
return new PrismaClient({ adapter, ...(options.log ? { log: options.log } : {}) });

The generator block uses the prisma-client generator with ESM output into src/generated/prisma. The datasource block declares only provider = "postgresql"; the connection URL lives in packages/database/prisma.config.ts, which prefers DIRECT_URL over DATABASE_URL so migration commands bypass any pooler. The config omits the datasource key entirely when no URL is set, which keeps prisma generate and prisma validate usable with no database at all.

Seeding

packages/database/src/seed/ builds a synthetic demo practice.

• data.ts exports buildDemoPractice(options), which builds plain rows with no database involved. It is fully deterministic: no random source, no wall clock, and no fixture generator library. Ids come from a UUIDv7 generator wired to a fixed clock and byte source, and every clinical value derives from the patient's index, so two runs produce byte-identical rows.
• index.ts exports seedDemoPractice(client, options), which inserts everything in one transaction across ordered createMany steps that follow the foreign-key graph. Either the whole practice lands or none of it does. It takes a root client on purpose, because it creates the tenant and there is therefore no tenant to scope it to yet.
• run.ts is the CLI entry, run as pnpm --filter @openrunic/database db:seed. It refuses to run when NODE_ENV is production unless OPENRUNIC_SEED_FORCE is set.

Everything in the seed is synthetic by rule. Names like "Testina Patientsson" and payers like "Placeholder Mutual Health", with identifiers drawn from reserved or non-routable ranges. The seed also builds a verifiable audit chain, and a test asserts that verifyAuditChain accepts it.

Package scripts

pnpm --filter @openrunic/database db:generate   # prisma generate
pnpm --filter @openrunic/database db:migrate    # prisma migrate dev
pnpm --filter @openrunic/database db:deploy     # prisma migrate deploy
pnpm --filter @openrunic/database db:seed       # node dist/seed/run.js
pnpm --filter @openrunic/database test

The test suite covers the pure modules only, with 95 percent thresholds on statements, branches, functions, and lines: src/audit.ts, src/uuid.ts, src/forms.ts, and src/schemas/. The client factory, the tenant extension factory, and the seed runner need a generated client and a real database, so they are covered by the API package's integration tests instead.

Related pages

• Multi-tenancy and isolation
• Audit and hash chain
• Form engine
• FHIR boundary
• Upgrades and migrations
• Glossary