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

Focused: cro.staging_record clear

Table reference

15 of 15 tables shown

cro

provenance_event

Append-only ALCOA+ provenance spine. Every regulated action emits a row here before any write. Inspectors join through subject_kind/subject_id to reconstruct chain of custody.

Audit

Column	Type	Key	References	Notes
`id`	uuid	PK	—
`study_id`	uuid	nullable	—
`actor_kind`	text	required	—	human \| agent \| system
`actor_id`	text	required	—
`model_provider`	text	nullable	—
`model_id`	text	nullable	—
`model_version_id`	uuid	FK	→ cro.model_registry_version
`action`	text	required	—
`subject_kind`	text	required	—
`subject_id`	text	required	—
`retrieved_context`	jsonb	required	—
`reviewer_user_id`	uuid	nullable	—
`signature_id`	uuid	nullable	—
`occurred_at`	timestamptz	required	—
`payload`	jsonb	required	—

cro

staging_record

The only legal write destination for agents. Proposed state lands here in `proposed`; humans review and sign to advance through `reviewing` → `signed` → `promoted`.

Staging

Column	Type	Key	References	Notes
`id`	uuid	PK	—
`entity_kind`	text	required	—
`entity_id`	uuid	nullable	—
`agent_id`	text	required	—
`model_version_id`	uuid	FK	→ cro.model_registry_version
`provenance_id`	uuid	FK	→ cro.provenance_event
`state`	enum	required	—	staging_state enum
`proposed_state`	jsonb	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—

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

app_log

Ops-support structured log. Also acts as the transactional outbox for buffered events (per CLAUDE.md event bus default). Not regulated; no provenance requirement.

Audit

Column	Type	Key	References
`id`	uuid	PK	—
`level`	text	required	—
`component`	text	required	—
`message`	text	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event
`payload`	jsonb	required	—
`logged_at`	timestamptz	required	—

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

cro

vector_document

pgvector-backed semantic index for the knowledge-RAG agent. Embeddings live alongside source text and citation metadata.

Reference

Column	Type	Key	References	Notes
`id`	uuid	PK	—
`source_kind`	text	required	—
`source_uri`	text	required	—
`chunk_index`	integer	required	—
`content`	text	required	—
`embedding`	jsonb	required	—	vector(1536)
`metadata`	jsonb	required	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—

cro

sponsor_request

Sponsor portal intake. Every inbound request lands as a staging row first; promotion to an actionable item still requires the same A2 gate.

Staging

Column	Type	Key	References
`id`	uuid	PK	—
`sponsor_id`	text	required	—
`study_id`	uuid	nullable	—
`subject`	text	required	—
`body`	text	required	—
`priority`	text	required	—
`status`	text	required	—
`provenance_id`	uuid	FK	→ cro.provenance_event
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—
`created_by`	uuid	required	—

auth

users

Supabase Auth user table. Referenced by created_by / created_by_user_id on every regulated row. Owned by Supabase Auth; we read but do not write.

Reference

Column	Type	Key	References
`id`	uuid	PK	—
`email`	text	nullable	—
`encrypted_password`	text	nullable	—
`email_confirmed_at`	timestamptz	nullable	—
`raw_app_meta_data`	jsonb	nullable	—
`raw_user_meta_data`	jsonb	nullable	—
`last_sign_in_at`	timestamptz	nullable	—
`created_at`	timestamptz	required	—
`updated_at`	timestamptz	required	—