Reference

Database schema — `cro.*` ERD

The canonical data model. Tables are color-coded by category: regulated tables carry provenance and require a Part 11 signature to promote staging → live. Click any table in the diagram to jump to its details card below, or open `/schema?table=study` to deep link.

Tables

Columns

174

Foreign keys

Regulated tables

Regulated9

Carries created_at / updated_at / created_by / provenance_id. Promotion requires a Part 11 signature.

Staging2

Where agents land their writes. State machine: proposed → reviewing → signed → promoted.

Audit2

Append-only provenance and ops logs. Not part of the regulated write path.

Reference2

External or read-only data we index but do not own.

LegendRegulatedStagingAuditReferencePKprimary keyFKforeign key

Table reference

9 of 15 tables shown (Regulated only)

cro

signature_event

Append-only 21 CFR Part 11 signatures. Every promotion staging→live requires one row here. Carries re-auth timestamp and meaning-of-signature text.

Regulated

Column	Type	Key	References	Notes
`id`	uuid	PK	—
`user_id`	uuid	required	—
`user_display_name`	text	required	—
`intent`	enum	required	—	signature_intent enum
`subject_kind`	text	required	—
`subject_id`	text	required	—
`re_auth_at`	timestamptz	required	—
`meaning_of_signature`	text	required	—
`signed_at`	timestamptz	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

model_registry_version

One row per (provider × model_id × prompt_hash). CI fails if a prompt diff lands without a registry row. Powers /models console and provenance joins.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`provider`	text	required	—
`model_id`	text	required	—
`prompt_hash`	text	required	—
`prompt_version`	text	required	—
`registered_at`	timestamptz	required	—
`retired_at`	timestamptz	nullable	—
`notes`	text	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

validation_evidence_run

One row per CI run that emitted CSV/CSA validation evidence. Captures pr_number, commit_sha, and the storage paths of evidence artifacts.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`pr_number`	integer	nullable	—
`commit_sha`	text	required	—
`evidence_paths`	jsonb	required	—
`signed_by_user_id`	text	nullable	—
`signed_at`	timestamptz	nullable	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

live_essential_document

Promoted-from-staging essential documents (TMF artifacts). One row per signed promotion; links back to both the staging row and the signature event.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`promoted_from_staging_id`	uuid	FK	→ cro.staging_record
`signature_id`	uuid	FK	→ cro.signature_event
`study_id`	uuid	nullable	—
`document_type`	text	required	—
`document_title`	text	required	—
`storage_uri`	text	required	—
`metadata`	jsonb	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

study

One row per clinical study. Carries sponsor, phase, status, health, timeline, financials, and the executive summary used by /studies and /digest.

Regulated

Column	Type	Key	References	Notes
`id`	uuid	PK	—
`code`	text	required	—	unique short code
`sponsor`	text	required	—
`phase`	text	required	—
`therapeutic_area`	text	required	—
`indication`	text	required	—
`status`	text	required	—
`health`	text	required	—	green \| yellow \| red
`executive_summary`	text	required	—
`goals`	jsonb	required	—
`start_date`	date	required	—
`planned_end_date`	date	required	—
`current_forecast_end_date`	date	required	—
`contracted_budget`	numeric	required	—
`ebv_spent`	numeric	required	—
`eac_budget`	numeric	required	—
`currency`	text	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

study_kpi

Headline KPI tiles per study (label, value, delta direction, threshold). Position orders them in the /studies/[id] header strip.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`study_id`	uuid	FK	→ cro.study
`label`	text	nullable	—
`value`	text	nullable	—
`delta`	text	nullable	—
`delta_direction`	text	nullable	—
`threshold`	text	nullable	—
`position`	integer	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

study_milestone

Lifecycle milestones per study (FPI, LPI, LPLV, DBL, CSR). Planned vs. actual/forecast dates drive the at-risk indicator.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`study_id`	uuid	FK	→ cro.study
`name`	text	nullable	—
`planned_date`	date	nullable	—
`actual_or_forecast_date`	date	nullable	—
`status`	text	nullable	—
`position`	integer	nullable	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

study_risk

Active and closed risks per study. Severity drives the /risks register heatmap; owner is the named human accountable for mitigation.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`study_id`	uuid	FK	→ cro.study
`title`	text	nullable	—
`owner`	text	nullable	—
`severity`	text	nullable	—
`mitigation`	text	nullable	—
`status`	text	nullable	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event

cro

study_resource

Role-level resource plan per study (headcount, FTE, monthly rate). Feeds the cost-vs-budget rollups on the /costs console.

Regulated

Column	Type	Key	References
`id`	uuid	PK	—
`study_id`	uuid	FK	→ cro.study
`role`	text	nullable	—
`headcount`	integer	nullable	—
`fte`	numeric	nullable	—
`rate_per_month`	numeric	nullable	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event