# ZubLMS data dictionary

Schema 55 · PostgreSQL 18 · 120 tables.
Generated from an empty disposable PostgreSQL database after all Go-admin migrations, including River; no customer rows exported.

Generated from migrations. This is not a production inventory. Logical associations without SQL foreign keys are not inferred. Keycloak and OpenBao internal databases are outside this application schema.

## activities

Catalogue & publishing: Draft learning activities and structured content.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| module_id | uuid | no | — |
| title | text | no | — |
| kind | text | no | — |
| position | integer | no | — |
| revision | bigint | no | 1 |
| body | text | no | ''::text |
| video_source | jsonb | yes | — |
| body_format | text | no | 'plain'::text |
| package_id | uuid | yes | — |

### Constraints

```sql
activities_body_format_check: CHECK ((body_format = ANY (ARRAY['plain'::text, 'html'::text])))
activities_kind: CHECK ((kind = ANY (ARRAY['text'::text, 'video'::text, 'pdf'::text, 'quiz'::text, 'scorm'::text])))
activities_module_fk: FOREIGN KEY (organisation_id, module_id) REFERENCES modules(organisation_id, id)
activities_pkey: PRIMARY KEY (organisation_id, id)
activities_position_nonnegative: CHECK (("position" >= 0))
activities_position_once: UNIQUE (organisation_id, module_id, "position")
activities_revision_positive: CHECK ((revision > 0))
activities_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
activity_package_fk: FOREIGN KEY (organisation_id, package_id) REFERENCES learning_packages(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX activities_pkey ON public.activities USING btree (organisation_id, id)
CREATE UNIQUE INDEX activities_position_once ON public.activities USING btree (organisation_id, module_id, "position")
```

### Policies

```sql
{"name":"activities_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00009_drafts.sql, migrations/00014_quiz_attempts.sql, migrations/00035_native_workspace.sql, migrations/00045_experience.sql, migrations/00046_course_builder.sql, migrations/00049_learning_packages.sql

## activity_progress

Learning & assessment: Persisted progress for an enrolment activity.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| activity_id | uuid | no | — |
| state | text | no | — |
| server_revision | bigint | no | — |

### Constraints

```sql
activity_progress_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
activity_progress_pkey: PRIMARY KEY (organisation_id, enrolment_id, activity_id)
activity_progress_revision_positive: CHECK ((server_revision > 0))
activity_progress_state: CHECK ((state = ANY (ARRAY['not_started'::text, 'completed'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX activity_progress_pkey ON public.activity_progress USING btree (organisation_id, enrolment_id, activity_id)
```

### Policies

```sql
{"name":"activity_progress_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00013_learning_progress.sql

## activity_questions

Catalogue & publishing: Draft assessment questions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| activity_id | uuid | no | — |
| prompt | text | no | — |
| position | integer | no | — |
| options | text[] | no | — |
| correct_index | integer | no | — |

### Constraints

```sql
activity_questions_activity_fk: FOREIGN KEY (organisation_id, activity_id) REFERENCES activities(organisation_id, id)
activity_questions_correct: CHECK ((correct_index >= 0))
activity_questions_options: CHECK ((cardinality(options) >= 2))
activity_questions_pkey: PRIMARY KEY (organisation_id, id)
activity_questions_position_nonnegative: CHECK (("position" >= 0))
activity_questions_position_once: UNIQUE (organisation_id, activity_id, "position")
activity_questions_prompt_present: CHECK (((prompt = btrim(prompt)) AND (prompt <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX activity_questions_pkey ON public.activity_questions USING btree (organisation_id, id)
CREATE UNIQUE INDEX activity_questions_position_once ON public.activity_questions USING btree (organisation_id, activity_id, "position")
```

### Policies

```sql
{"name":"activity_questions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## activity_quiz_policies

Catalogue & publishing: Draft assessment attempt/pass policies.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| activity_id | uuid | no | — |
| max_attempts | integer | no | — |
| pass_percent | integer | no | — |
| feedback | text | no | — |

### Constraints

```sql
activity_quiz_policies_activity_fk: FOREIGN KEY (organisation_id, activity_id) REFERENCES activities(organisation_id, id)
activity_quiz_policies_attempts: CHECK ((max_attempts > 0))
activity_quiz_policies_feedback: CHECK ((feedback = ANY (ARRAY['never'::text, 'after_submit'::text, 'after_pass'::text])))
activity_quiz_policies_pass: CHECK (((pass_percent >= 0) AND (pass_percent <= 100)))
activity_quiz_policies_pkey: PRIMARY KEY (organisation_id, activity_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX activity_quiz_policies_pkey ON public.activity_quiz_policies USING btree (organisation_id, activity_id)
```

### Policies

```sql
{"name":"activity_quiz_policies_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## ai_company_policy

Resources & AI: Operator-governed company AI enablement, provider permissions and attempt ceiling.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| revision | bigint | no | 1 |
| enabled | boolean | no | false |
| allow_company_keys | boolean | no | false |
| providers | jsonb | no | '["openrouter"]'::jsonb |
| daily_limit | integer | no | 20 |

### Constraints

```sql
ai_company_policy_daily_limit_check: CHECK (((daily_limit >= 1) AND (daily_limit <= 1000)))
ai_company_policy_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
ai_company_policy_pkey: PRIMARY KEY (organisation_id)
ai_company_policy_providers_check: CHECK (((jsonb_typeof(providers) = 'array'::text) AND ((jsonb_array_length(providers) >= 1) AND (jsonb_array_length(providers) <= 4))))
```

### Indexes

```sql
CREATE UNIQUE INDEX ai_company_policy_pkey ON public.ai_company_policy USING btree (organisation_id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00052_ai_provider_management.sql

## ai_company_routing

Resources & AI: Explicit course/answer connection selections and company daily attempt limit.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| revision | bigint | no | 1 |
| course | text | no | 'deployment'::text |
| answer | text | no | 'deployment'::text |
| daily_limit | integer | no | 20 |

### Constraints

```sql
ai_company_routing_daily_limit_check: CHECK (((daily_limit >= 1) AND (daily_limit <= 1000)))
ai_company_routing_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
ai_company_routing_pkey: PRIMARY KEY (organisation_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX ai_company_routing_pkey ON public.ai_company_routing USING btree (organisation_id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00052_ai_provider_management.sql

## ai_connections

Resources & AI: Named, revisioned AI connections with encrypted credentials, explicit test and activation state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| revision | bigint | no | 1 |
| name | text | no | — |
| provider | text | no | — |
| model | text | no | — |
| credential_ciphertext | text | no | — |
| privacy_reviewed | boolean | no | false |
| active | boolean | no | false |
| tested_revision | bigint | no | 0 |
| tested_at | timestamp with time zone | yes | — |

### Constraints

```sql
ai_connections_check: CHECK (((NOT active) OR ((tested_revision = revision) AND privacy_reviewed AND (credential_ciphertext <> ''::text))))
ai_connections_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
ai_connections_pkey: PRIMARY KEY (organisation_id, id)
ai_connections_provider_check: CHECK ((provider = ANY (ARRAY['openrouter'::text, 'openai'::text, 'anthropic'::text, 'deepseek'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX ai_connections_pkey ON public.ai_connections USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00052_ai_provider_management.sql

## ai_draft_requests

Resources & AI: Tenant-scoped AI request/quota/provenance metadata; not a prompt or response transcript.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| user_id | uuid | no | — |
| model | text | no | — |
| state | text | no | — |
| created_at | timestamp with time zone | no | now() |
| finished_at | timestamp with time zone | yes | — |
| kind | text | no | 'course'::text |
| sources | jsonb | no | '[]'::jsonb |
| provider | text | no | 'openrouter'::text |
| connection_id | uuid | yes | — |
| configuration | text | no | ''::text |
| input_tokens | bigint | yes | — |
| output_tokens | bigint | yes | — |

### Constraints

```sql
ai_draft_connection_fk: FOREIGN KEY (organisation_id, connection_id) REFERENCES ai_connections(organisation_id, id)
ai_draft_requests_input_tokens_check: CHECK ((input_tokens >= 0))
ai_draft_requests_kind_check: CHECK ((kind = ANY (ARRAY['course'::text, 'answer'::text, 'test'::text])))
ai_draft_requests_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
ai_draft_requests_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
ai_draft_requests_output_tokens_check: CHECK ((output_tokens >= 0))
ai_draft_requests_pkey: PRIMARY KEY (organisation_id, id)
ai_draft_requests_sources_check: CHECK (((jsonb_typeof(sources) = 'array'::text) AND (jsonb_array_length(sources) <= 3)))
ai_draft_requests_state_check: CHECK ((state = ANY (ARRAY['started'::text, 'succeeded'::text, 'failed'::text])))
```

### Indexes

```sql
CREATE INDEX ai_draft_daily ON public.ai_draft_requests USING btree (organisation_id, created_at)
CREATE UNIQUE INDEX ai_draft_requests_pkey ON public.ai_draft_requests USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00050_ai_draft_requests.sql, migrations/00051_ai_resource_assistance.sql, migrations/00052_ai_provider_management.sql

## api_keys

Integrations & extensions: Hashed API-key authority records; table presence does not establish a public machine-auth API.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| name | text | no | — |
| key_prefix | text | no | — |
| secret_hash | bytea | no | — |
| created_at | timestamp with time zone | no | — |
| revoked_at | timestamp with time zone | yes | — |
| rotated_from_id | uuid | yes | — |

### Constraints

```sql
api_keys_hash_once: UNIQUE (secret_hash)
api_keys_hash_present: CHECK ((octet_length(secret_hash) = 32))
api_keys_name_present: CHECK (((name = btrim(name)) AND (name <> ''::text)))
api_keys_pkey: PRIMARY KEY (organisation_id, id)
api_keys_prefix_present: CHECK (((key_prefix = btrim(key_prefix)) AND (char_length(key_prefix) >= 8)))
api_keys_rotated_fk: FOREIGN KEY (organisation_id, rotated_from_id) REFERENCES api_keys(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX api_keys_hash_once ON public.api_keys USING btree (secret_hash)
CREATE UNIQUE INDEX api_keys_pkey ON public.api_keys USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"api_keys_by_hash","check":null,"roles":["public"],"using":"(secret_hash = decode(NULLIF(current_setting('app.api_key_hash'::text, true), ''::text), 'hex'::text))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"api_keys_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00033_api_keys_webhooks.sql

## attempt_answers

Learning & assessment: Answers associated with an assessment attempt.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| attempt_id | uuid | no | — |
| question_id | uuid | no | — |
| selected_index | integer | no | — |
| correct | boolean | no | — |

### Constraints

```sql
attempt_answers_attempt_fk: FOREIGN KEY (organisation_id, attempt_id) REFERENCES attempts(organisation_id, id)
attempt_answers_pkey: PRIMARY KEY (organisation_id, attempt_id, question_id)
attempt_answers_question_fk: FOREIGN KEY (organisation_id, question_id) REFERENCES course_version_questions(organisation_id, id)
attempt_answers_selected: CHECK ((selected_index >= 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX attempt_answers_pkey ON public.attempt_answers USING btree (organisation_id, attempt_id, question_id)
```

### Policies

```sql
{"name":"attempt_answers_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## attempts

Learning & assessment: Assessment attempts with server-calculated result state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| enrolment_id | uuid | no | — |
| activity_id | uuid | no | — |
| user_id | uuid | no | — |
| attempt_number | integer | no | — |
| state | text | no | — |
| score_percent | integer | yes | — |
| passed | boolean | yes | — |
| started_at | timestamp with time zone | no | — |
| submitted_at | timestamp with time zone | yes | — |
| graded_at | timestamp with time zone | yes | — |

### Constraints

```sql
attempts_activity_fk: FOREIGN KEY (organisation_id, activity_id) REFERENCES course_version_activities(organisation_id, id)
attempts_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
attempts_number_positive: CHECK ((attempt_number > 0))
attempts_once: UNIQUE (organisation_id, enrolment_id, activity_id, attempt_number)
attempts_pkey: PRIMARY KEY (organisation_id, id)
attempts_score: CHECK (((score_percent IS NULL) OR ((score_percent >= 0) AND (score_percent <= 100))))
attempts_state: CHECK ((state = ANY (ARRAY['started'::text, 'submitted'::text, 'graded'::text, 'abandoned'::text])))
attempts_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX attempts_once ON public.attempts USING btree (organisation_id, enrolment_id, activity_id, attempt_number)
CREATE UNIQUE INDEX attempts_pkey ON public.attempts USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"attempts_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## auth_transactions

Identity & access: Short-lived browser OIDC transaction state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| state_hash | bytea | no | — |
| organisation_id | uuid | no | — |
| issuer | text | no | — |
| nonce | text | no | — |
| verifier | text | no | — |
| expires_at | timestamp with time zone | no | — |
| broker_alias | text | no | ''::text |
| broker_revision | bigint | no | 0 |

### Constraints

```sql
auth_transactions_issuer_present: CHECK (((issuer = btrim(issuer)) AND (issuer <> ''::text)))
auth_transactions_nonce_present: CHECK ((nonce <> ''::text))
auth_transactions_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
auth_transactions_pkey: PRIMARY KEY (state_hash)
auth_transactions_verifier_present: CHECK ((verifier <> ''::text))
```

### Indexes

```sql
CREATE UNIQUE INDEX auth_transactions_pkey ON public.auth_transactions USING btree (state_hash)
```

### Policies

```sql
{"name":"auth_transactions_by_hash","check":"(state_hash = decode(NULLIF(current_setting('app.login_state_hash'::text, true), ''::text), 'hex'::text))","roles":["public"],"using":"(state_hash = decode(NULLIF(current_setting('app.login_state_hash'::text, true), ''::text), 'hex'::text))","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00005_browser_sessions.sql, migrations/00041_organisation_administration.sql

## binding_decisions

Federation & provisioning: Recorded identity binding decisions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| invitation_id | uuid | no | — |
| actor_id | uuid | no | — |
| decision | text | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
binding_decisions_actor_fk: FOREIGN KEY (organisation_id, actor_id) REFERENCES users(organisation_id, id)
binding_decisions_decision: CHECK ((decision = ANY (ARRAY['approved'::text, 'rejected'::text])))
binding_decisions_invitation_fk: FOREIGN KEY (organisation_id, invitation_id) REFERENCES invitations(organisation_id, id)
binding_decisions_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX binding_decisions_pkey ON public.binding_decisions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"binding_decisions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00006_invitations.sql

## browser_sessions

Identity & access: Server-side browser session authority and expiry.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| session_hash | bytea | no | — |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| authz_version | bigint | no | — |
| auth_time | timestamp with time zone | no | — |
| csrf_hash | bytea | no | — |
| created_at | timestamp with time zone | no | — |
| last_seen_at | timestamp with time zone | no | — |
| absolute_expires_at | timestamp with time zone | no | — |
| assurance | text | no | ''::text |
| oidc_issuer | text | no | ''::text |
| oidc_subject_hash | bytea | yes | — |
| oidc_sid_hash | bytea | yes | — |

### Constraints

```sql
browser_oidc_sid_hash: CHECK (((oidc_sid_hash IS NULL) OR (octet_length(oidc_sid_hash) = 32)))
browser_oidc_subject_hash: CHECK (((oidc_subject_hash IS NULL) OR (octet_length(oidc_subject_hash) = 32)))
browser_sessions_absolute: CHECK ((absolute_expires_at > created_at))
browser_sessions_csrf_present: CHECK ((octet_length(csrf_hash) = 32))
browser_sessions_hash_present: CHECK ((octet_length(session_hash) = 32))
browser_sessions_pkey: PRIMARY KEY (session_hash)
browser_sessions_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE INDEX browser_oidc_sid ON public.browser_sessions USING btree (oidc_issuer, oidc_sid_hash) WHERE (oidc_sid_hash IS NOT NULL)
CREATE INDEX browser_oidc_subject ON public.browser_sessions USING btree (oidc_issuer, oidc_subject_hash)
CREATE UNIQUE INDEX browser_sessions_pkey ON public.browser_sessions USING btree (session_hash)
```

### Policies

```sql
{"name":"browser_sessions_by_actor","check":"((organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid) AND (user_id = (NULLIF(current_setting('app.actor_id'::text, true), ''::text))::uuid))","roles":["public"],"using":"((organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid) AND (user_id = (NULLIF(current_setting('app.actor_id'::text, true), ''::text))::uuid))","command":"ALL","permissive":"PERMISSIVE"}
{"name":"browser_sessions_by_hash","check":"(session_hash = decode(NULLIF(current_setting('app.session_hash'::text, true), ''::text), 'hex'::text))","roles":["public"],"using":"(session_hash = decode(NULLIF(current_setting('app.session_hash'::text, true), ''::text), 'hex'::text))","command":"ALL","permissive":"PERMISSIVE"}
{"name":"browser_sessions_for_logout","check":"false","roles":["public"],"using":"((oidc_issuer = NULLIF(current_setting('app.logout_issuer'::text, true), ''::text)) AND ((NULLIF(current_setting('app.logout_sid'::text, true), ''::text) IS NOT NULL) OR (NULLIF(current_setting('app.logout_subject'::text, true), ''::text) IS NOT NULL)) AND ((NULLIF(current_setting('app.logout_sid'::text, true), ''::text) IS NULL) OR (oidc_sid_hash = decode(current_setting('app.logout_sid'::text, true), 'hex'::text))) AND ((NULLIF(current_setting('app.logout_subject'::text, true), ''::text) IS NULL) OR (oidc_subject_hash = decode(current_setting('app.logout_subject'::text, true), 'hex'::text))))","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00005_browser_sessions.sql, migrations/00008_profiles.sql, migrations/00040_oidc_session_revocation.sql, migrations/00055_support_views.sql

## certificate_versions

Evidence & reporting: Versioned rendered certificate evidence and object references.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| certificate_id | uuid | no | — |
| version_number | integer | no | — |
| pdf_object_id | text | no | — |
| html_object_id | text | no | — |
| template_version | text | no | — |
| rendered_at | timestamp with time zone | no | — |

### Constraints

```sql
certificate_versions_certificate_fk: FOREIGN KEY (organisation_id, certificate_id) REFERENCES certificates(organisation_id, id)
certificate_versions_html_present: CHECK (((html_object_id = btrim(html_object_id)) AND (html_object_id <> ''::text)))
certificate_versions_number: CHECK ((version_number > 0))
certificate_versions_once: UNIQUE (organisation_id, certificate_id, version_number)
certificate_versions_pdf_present: CHECK (((pdf_object_id = btrim(pdf_object_id)) AND (pdf_object_id <> ''::text)))
certificate_versions_pkey: PRIMARY KEY (organisation_id, id)
certificate_versions_template_present: CHECK (((template_version = btrim(template_version)) AND (template_version <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX certificate_versions_once ON public.certificate_versions USING btree (organisation_id, certificate_id, version_number)
CREATE UNIQUE INDEX certificate_versions_pkey ON public.certificate_versions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"certificate_versions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00018_certificates.sql

## certificates

Evidence & reporting: Certificate identity and current lifecycle.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| completion_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| user_id | uuid | no | — |
| course_title | text | no | — |
| learner_name | text | no | — |
| state | text | no | — |
| created_at | timestamp with time zone | no | — |
| updated_at | timestamp with time zone | no | — |
| valid_from | timestamp with time zone | yes | — |
| valid_until | timestamp with time zone | yes | — |
| revoked_at | timestamp with time zone | yes | — |
| revoked_by | uuid | yes | — |
| revoke_reason | text | yes | — |
| render_generation | bigint | no | 0 |
| render_started_at | timestamp with time zone | yes | — |

### Constraints

```sql
certificates_completion_fk: FOREIGN KEY (organisation_id, completion_id) REFERENCES completions(organisation_id, id)
certificates_completion_once: UNIQUE (organisation_id, completion_id)
certificates_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
certificates_learner_present: CHECK (((learner_name = btrim(learner_name)) AND (learner_name <> ''::text)))
certificates_pkey: PRIMARY KEY (organisation_id, id)
certificates_revoke_complete: CHECK ((((revoked_at IS NULL) AND (revoked_by IS NULL) AND (revoke_reason IS NULL)) OR ((revoked_at IS NOT NULL) AND (revoked_by IS NOT NULL) AND (revoke_reason = btrim(revoke_reason)) AND (revoke_reason <> ''::text))))
certificates_revoked_by_fk: FOREIGN KEY (organisation_id, revoked_by) REFERENCES users(organisation_id, id)
certificates_state: CHECK ((state = ANY (ARRAY['queued'::text, 'rendering'::text, 'ready'::text, 'failed'::text])))
certificates_title_present: CHECK (((course_title = btrim(course_title)) AND (course_title <> ''::text)))
certificates_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
certificates_validity_order: CHECK (((valid_until IS NULL) OR (valid_from IS NULL) OR (valid_until >= valid_from)))
```

### Indexes

```sql
CREATE UNIQUE INDEX certificates_completion_once ON public.certificates USING btree (organisation_id, completion_id)
CREATE UNIQUE INDEX certificates_pkey ON public.certificates USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"certificates_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00018_certificates.sql, migrations/00019_certificate_revocation.sql, migrations/00020_refresher_schedule.sql, migrations/00038_certificate_render_leases.sql

## collection_courses

Catalogue & publishing: Ordered course references without duplicating content.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| collection_id | uuid | no | — |
| course_id | uuid | no | — |
| position | integer | no | — |

### Constraints

```sql
collection_courses_organisation_id_collection_id_fkey: FOREIGN KEY (organisation_id, collection_id) REFERENCES course_collections(organisation_id, id)
collection_courses_organisation_id_collection_id_position_key: UNIQUE (organisation_id, collection_id, "position")
collection_courses_organisation_id_course_id_fkey: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
collection_courses_pkey: PRIMARY KEY (organisation_id, collection_id, course_id)
collection_courses_position_check: CHECK (("position" >= 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX collection_courses_organisation_id_collection_id_position_key ON public.collection_courses USING btree (organisation_id, collection_id, "position")
CREATE UNIQUE INDEX collection_courses_pkey ON public.collection_courses USING btree (organisation_id, collection_id, course_id)
```

### Policies

```sql
{"name":"collection_courses_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## collection_follows

Catalogue & publishing: Personal interest in collections; never an enrolment grant.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| collection_id | uuid | no | — |
| user_id | uuid | no | — |

### Constraints

```sql
collection_follows_organisation_id_collection_id_fkey: FOREIGN KEY (organisation_id, collection_id) REFERENCES course_collections(organisation_id, id)
collection_follows_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
collection_follows_pkey: PRIMARY KEY (organisation_id, collection_id, user_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX collection_follows_pkey ON public.collection_follows USING btree (organisation_id, collection_id, user_id)
```

### Policies

```sql
{"name":"collection_follows_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## collection_recommendations

Catalogue & publishing: Manager recommendations of collections to people, separate from follows and enrolments.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| collection_id | uuid | no | — |
| user_id | uuid | no | — |
| recommended_by | uuid | no | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
collection_recommendations_organisation_id_collection_id_fkey: FOREIGN KEY (organisation_id, collection_id) REFERENCES course_collections(organisation_id, id)
collection_recommendations_organisation_id_recommended_by_fkey: FOREIGN KEY (organisation_id, recommended_by) REFERENCES users(organisation_id, id)
collection_recommendations_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
collection_recommendations_pkey: PRIMARY KEY (organisation_id, collection_id, user_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX collection_recommendations_pkey ON public.collection_recommendations USING btree (organisation_id, collection_id, user_id)
```

### Policies

```sql
{"name":"collection_recommendations_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00054_people_directory.sql

## company_domain_claims

Tenancy & company: DNS ownership claims awaiting separately qualified hosting activation.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| hostname | text | no | — |
| token | text | no | — |
| verified_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
company_domain_claims_hostname_check: CHECK ((((length(hostname) >= 4) AND (length(hostname) <= 253)) AND (hostname = lower(hostname))))
company_domain_claims_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
company_domain_claims_pkey: PRIMARY KEY (organisation_id, hostname)
```

### Indexes

```sql
CREATE UNIQUE INDEX company_domain_claim_owner ON public.company_domain_claims USING btree (hostname) WHERE (verified_at IS NOT NULL)
CREATE UNIQUE INDEX company_domain_claims_pkey ON public.company_domain_claims USING btree (organisation_id, hostname)
```

### Policies

```sql
{"name":"company_domain_claims_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00048_company_addresses.sql

## completions

Evidence & reporting: Durable learning-completion evidence.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| enrolment_id | uuid | no | — |
| completed_at | timestamp with time zone | no | — |

### Constraints

```sql
completions_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
completions_enrolment_once: UNIQUE (organisation_id, enrolment_id)
completions_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX completions_enrolment_once ON public.completions USING btree (organisation_id, enrolment_id)
CREATE UNIQUE INDEX completions_pkey ON public.completions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"completions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00015_completions.sql, migrations/00018_certificates.sql

## course_bookmarks

Catalogue & publishing: Personal saved-course relationships.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| course_id | uuid | no | — |

### Constraints

```sql
course_bookmarks_organisation_id_course_id_fkey: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
course_bookmarks_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
course_bookmarks_pkey: PRIMARY KEY (organisation_id, user_id, course_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_bookmarks_pkey ON public.course_bookmarks USING btree (organisation_id, user_id, course_id)
```

### Policies

```sql
{"name":"course_bookmarks_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00043_course_bookmarks.sql

## course_collections

Catalogue & publishing: Curated course discovery with revision, publication and team audience.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| revision | bigint | no | — |
| title | text | no | — |
| description | text | no | ''::text |
| colour | text | no | 'violet'::text |
| audience_unit | uuid | yes | — |
| published | boolean | no | false |
| archived | boolean | no | false |

### Constraints

```sql
course_collections_organisation_id_audience_unit_fkey: FOREIGN KEY (organisation_id, audience_unit) REFERENCES units(organisation_id, id)
course_collections_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
course_collections_pkey: PRIMARY KEY (organisation_id, id)
course_collections_revision_check: CHECK ((revision > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX course_collections_pkey ON public.course_collections USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"course_collections_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql, migrations/00054_people_directory.sql

## course_draft_assets

Catalogue & publishing: Draft-to-upload asset bindings.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| course_id | uuid | no | — |
| quarantine_id | uuid | no | — |

### Constraints

```sql
course_draft_assets_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
course_draft_assets_pkey: PRIMARY KEY (organisation_id, course_id, quarantine_id)
course_draft_assets_quarantine_fk: FOREIGN KEY (organisation_id, quarantine_id) REFERENCES quarantine_uploads(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_draft_assets_pkey ON public.course_draft_assets USING btree (organisation_id, course_id, quarantine_id)
```

### Policies

```sql
{"name":"course_draft_assets_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00012_quarantine.sql

## course_draft_renditions

Media: Draft media-rendition bindings.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| course_id | uuid | no | — |
| media_asset_id | uuid | no | — |

### Constraints

```sql
course_draft_renditions_asset_fk: FOREIGN KEY (organisation_id, media_asset_id) REFERENCES media_assets(organisation_id, id)
course_draft_renditions_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
course_draft_renditions_pkey: PRIMARY KEY (organisation_id, course_id, media_asset_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_draft_renditions_pkey ON public.course_draft_renditions USING btree (organisation_id, course_id, media_asset_id)
```

### Policies

```sql
{"name":"course_draft_renditions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00017_media_renditions.sql

## course_version_activities

Catalogue & publishing: Activities pinned to published modules.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| module_id | uuid | no | — |
| title | text | no | — |
| kind | text | no | — |
| position | integer | no | — |
| body | text | no | ''::text |
| video_source | jsonb | yes | — |
| body_format | text | no | 'plain'::text |
| package_id | uuid | yes | — |

### Constraints

```sql
course_version_activities_body_format_check: CHECK ((body_format = ANY (ARRAY['plain'::text, 'html'::text])))
course_version_activities_kind: CHECK ((kind = ANY (ARRAY['text'::text, 'video'::text, 'pdf'::text, 'quiz'::text, 'scorm'::text])))
course_version_activities_module_fk: FOREIGN KEY (organisation_id, module_id) REFERENCES course_version_modules(organisation_id, id)
course_version_activities_pkey: PRIMARY KEY (organisation_id, id)
course_version_activities_position_nonnegative: CHECK (("position" >= 0))
course_version_activities_position_once: UNIQUE (organisation_id, module_id, "position")
course_version_activities_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
version_activity_package_fk: FOREIGN KEY (organisation_id, package_id) REFERENCES learning_packages(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_activities_pkey ON public.course_version_activities USING btree (organisation_id, id)
CREATE UNIQUE INDEX course_version_activities_position_once ON public.course_version_activities USING btree (organisation_id, module_id, "position")
```

### Policies

```sql
{"name":"course_version_activities_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00010_course_versions.sql, migrations/00014_quiz_attempts.sql, migrations/00035_native_workspace.sql, migrations/00045_experience.sql, migrations/00046_course_builder.sql, migrations/00049_learning_packages.sql

## course_version_assets

Catalogue & publishing: Published immutable asset bindings.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| course_version_id | uuid | no | — |
| quarantine_id | uuid | no | — |
| object_id | text | no | — |
| byte_length | bigint | no | — |
| sha256 | bytea | no | — |

### Constraints

```sql
course_version_assets_byte_length: CHECK ((byte_length >= 0))
course_version_assets_pkey: PRIMARY KEY (organisation_id, course_version_id, quarantine_id)
course_version_assets_sha256: CHECK ((octet_length(sha256) = 32))
course_version_assets_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_assets_pkey ON public.course_version_assets USING btree (organisation_id, course_version_id, quarantine_id)
```

### Policies

```sql
{"name":"course_version_assets_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00012_quarantine.sql

## course_version_modules

Catalogue & publishing: Modules pinned to a published course version.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| course_version_id | uuid | no | — |
| title | text | no | — |
| position | integer | no | — |
| summary | text | no | ''::text |

### Constraints

```sql
course_version_modules_pkey: PRIMARY KEY (organisation_id, id)
course_version_modules_position_nonnegative: CHECK (("position" >= 0))
course_version_modules_position_once: UNIQUE (organisation_id, course_version_id, "position")
course_version_modules_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
course_version_modules_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_modules_pkey ON public.course_version_modules USING btree (organisation_id, id)
CREATE UNIQUE INDEX course_version_modules_position_once ON public.course_version_modules USING btree (organisation_id, course_version_id, "position")
```

### Policies

```sql
{"name":"course_version_modules_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00010_course_versions.sql, migrations/00046_course_builder.sql

## course_version_questions

Catalogue & publishing: Published immutable assessment questions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| activity_id | uuid | no | — |
| prompt | text | no | — |
| position | integer | no | — |
| options | text[] | no | — |
| correct_index | integer | no | — |

### Constraints

```sql
course_version_questions_activity_fk: FOREIGN KEY (organisation_id, activity_id) REFERENCES course_version_activities(organisation_id, id)
course_version_questions_correct: CHECK ((correct_index >= 0))
course_version_questions_options: CHECK ((cardinality(options) >= 2))
course_version_questions_pkey: PRIMARY KEY (organisation_id, id)
course_version_questions_position_nonnegative: CHECK (("position" >= 0))
course_version_questions_position_once: UNIQUE (organisation_id, activity_id, "position")
course_version_questions_prompt_present: CHECK (((prompt = btrim(prompt)) AND (prompt <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_questions_pkey ON public.course_version_questions USING btree (organisation_id, id)
CREATE UNIQUE INDEX course_version_questions_position_once ON public.course_version_questions USING btree (organisation_id, activity_id, "position")
```

### Policies

```sql
{"name":"course_version_questions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## course_version_quiz_policies

Catalogue & publishing: Published assessment policies.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| activity_id | uuid | no | — |
| max_attempts | integer | no | — |
| pass_percent | integer | no | — |
| feedback | text | no | — |

### Constraints

```sql
course_version_quiz_policies_activity_fk: FOREIGN KEY (organisation_id, activity_id) REFERENCES course_version_activities(organisation_id, id)
course_version_quiz_policies_attempts: CHECK ((max_attempts > 0))
course_version_quiz_policies_feedback: CHECK ((feedback = ANY (ARRAY['never'::text, 'after_submit'::text, 'after_pass'::text])))
course_version_quiz_policies_pass: CHECK (((pass_percent >= 0) AND (pass_percent <= 100)))
course_version_quiz_policies_pkey: PRIMARY KEY (organisation_id, activity_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_quiz_policies_pkey ON public.course_version_quiz_policies USING btree (organisation_id, activity_id)
```

### Policies

```sql
{"name":"course_version_quiz_policies_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00014_quiz_attempts.sql

## course_version_renditions

Media: Published pinned media-rendition bindings.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| course_version_id | uuid | no | — |
| media_asset_id | uuid | no | — |
| master_object_id | text | no | — |
| attempt_id | uuid | no | — |

### Constraints

```sql
course_version_renditions_master_present: CHECK (((master_object_id = btrim(master_object_id)) AND (master_object_id <> ''::text)))
course_version_renditions_pkey: PRIMARY KEY (organisation_id, course_version_id, media_asset_id)
course_version_renditions_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_renditions_pkey ON public.course_version_renditions USING btree (organisation_id, course_version_id, media_asset_id)
```

### Policies

```sql
{"name":"course_version_renditions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00017_media_renditions.sql

## course_version_scorm

Learning packages: SCORM package references associated with published activities.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| course_version_id | uuid | no | — |
| package_id | uuid | no | — |
| object_id | text | no | — |
| sha256 | bytea | no | — |
| title | text | no | — |
| item_title | text | no | — |
| launch_href | text | no | — |

### Constraints

```sql
course_version_scorm_launch_present: CHECK (((launch_href = btrim(launch_href)) AND (launch_href <> ''::text)))
course_version_scorm_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
course_version_scorm_package_fk: FOREIGN KEY (organisation_id, package_id) REFERENCES scorm_packages(organisation_id, id)
course_version_scorm_pkey: PRIMARY KEY (organisation_id, course_version_id)
course_version_scorm_sha256: CHECK ((octet_length(sha256) = 32))
course_version_scorm_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX course_version_scorm_pkey ON public.course_version_scorm USING btree (organisation_id, course_version_id)
```

### Policies

```sql
{"name":"course_version_scorm_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00027_scorm_packages.sql

## course_versions

Catalogue & publishing: Immutable published course snapshots.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| course_id | uuid | no | — |
| version_number | integer | no | — |
| title | text | no | — |
| source_revision | bigint | no | — |
| published_at | timestamp with time zone | no | — |
| metadata | jsonb | no | '{}'::jsonb |

### Constraints

```sql
course_versions_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
course_versions_number_once: UNIQUE (organisation_id, course_id, version_number)
course_versions_number_positive: CHECK ((version_number > 0))
course_versions_pkey: PRIMARY KEY (organisation_id, id)
course_versions_source_positive: CHECK ((source_revision > 0))
course_versions_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX course_versions_number_once ON public.course_versions USING btree (organisation_id, course_id, version_number)
CREATE UNIQUE INDEX course_versions_pkey ON public.course_versions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"course_versions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00010_course_versions.sql, migrations/00011_enrolments.sql, migrations/00012_quarantine.sql, migrations/00017_media_renditions.sql, migrations/00027_scorm_packages.sql, migrations/00028_scorm_launch_registrations.sql, migrations/00030_dispatch_destinations.sql, migrations/00031_dispatch_registrations.sql, migrations/00035_native_workspace.sql, migrations/00053_collections_pathways.sql

## courses

Catalogue & publishing: Mutable course drafts and authoring metadata.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| title | text | no | — |
| revision | bigint | no | 1 |
| metadata | jsonb | no | '{}'::jsonb |

### Constraints

```sql
courses_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
courses_pkey: PRIMARY KEY (organisation_id, id)
courses_revision_positive: CHECK ((revision > 0))
courses_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX courses_pkey ON public.courses USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"courses_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00009_drafts.sql, migrations/00010_course_versions.sql, migrations/00011_enrolments.sql, migrations/00012_quarantine.sql, migrations/00017_media_renditions.sql, migrations/00020_refresher_schedule.sql, migrations/00027_scorm_packages.sql, migrations/00030_dispatch_destinations.sql, migrations/00035_native_workspace.sql, migrations/00043_course_bookmarks.sql, migrations/00053_collections_pathways.sql

## dispatch_deployments

Hosted distribution: Versioned course distribution deployments.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| destination_id | uuid | no | — |
| course_id | uuid | no | — |
| course_version_id | uuid | no | — |
| revision | integer | no | — |
| revoked_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_deployments_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
dispatch_deployments_destination_fk: FOREIGN KEY (organisation_id, destination_id) REFERENCES dispatch_destinations(organisation_id, id)
dispatch_deployments_pkey: PRIMARY KEY (organisation_id, id)
dispatch_deployments_revision_once: UNIQUE (organisation_id, destination_id, course_id, revision)
dispatch_deployments_revision_positive: CHECK ((revision > 0))
dispatch_deployments_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_deployments_pkey ON public.dispatch_deployments USING btree (organisation_id, id)
CREATE UNIQUE INDEX dispatch_deployments_revision_once ON public.dispatch_deployments USING btree (organisation_id, destination_id, course_id, revision)
```

### Policies

```sql
{"name":"dispatch_deployments_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00030_dispatch_destinations.sql, migrations/00031_dispatch_registrations.sql

## dispatch_destinations

Hosted distribution: Destination LMS configuration for outbound distribution services.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| name | text | no | — |
| approved_origins | text[] | no | — |
| credential_hash | bytea | no | — |
| credential_version | integer | no | — |
| enabled | boolean | no | — |
| revoked_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_destinations_credential: CHECK ((octet_length(credential_hash) = 32))
dispatch_destinations_credential_version: CHECK ((credential_version > 0))
dispatch_destinations_name_present: CHECK (((name = btrim(name)) AND (name <> ''::text)))
dispatch_destinations_origins: CHECK ((cardinality(approved_origins) > 0))
dispatch_destinations_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_destinations_pkey ON public.dispatch_destinations USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"dispatch_destinations_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00030_dispatch_destinations.sql, migrations/00031_dispatch_registrations.sql

## dispatch_external_learners

Hosted distribution: Restricted external learner identities.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| destination_id | uuid | no | — |
| external_learner_id | text | no | — |
| user_id | uuid | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_external_destination_fk: FOREIGN KEY (organisation_id, destination_id) REFERENCES dispatch_destinations(organisation_id, id)
dispatch_external_learner_present: CHECK (((external_learner_id = btrim(external_learner_id)) AND (external_learner_id <> ''::text)))
dispatch_external_learners_pkey: PRIMARY KEY (organisation_id, destination_id, external_learner_id)
dispatch_external_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
dispatch_external_user_once: UNIQUE (organisation_id, user_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_external_learners_pkey ON public.dispatch_external_learners USING btree (organisation_id, destination_id, external_learner_id)
CREATE UNIQUE INDEX dispatch_external_user_once ON public.dispatch_external_learners USING btree (organisation_id, user_id)
```

### Policies

```sql
{"name":"dispatch_external_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00031_dispatch_registrations.sql

## dispatch_grants

Hosted distribution: Distribution access grants.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| capability_hash | bytea | no | — |
| destination_id | uuid | no | — |
| deployment_id | uuid | no | — |
| external_learner_id | text | no | — |
| external_attempt_id | text | no | — |
| expires_at | timestamp with time zone | no | — |
| redeemed_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_grants_attempt_present: CHECK (((external_attempt_id = btrim(external_attempt_id)) AND (external_attempt_id <> ''::text)))
dispatch_grants_deployment_fk: FOREIGN KEY (organisation_id, deployment_id) REFERENCES dispatch_deployments(organisation_id, id)
dispatch_grants_destination_fk: FOREIGN KEY (organisation_id, destination_id) REFERENCES dispatch_destinations(organisation_id, id)
dispatch_grants_hash_once: UNIQUE (capability_hash)
dispatch_grants_hash_present: CHECK ((octet_length(capability_hash) = 32))
dispatch_grants_learner_present: CHECK (((external_learner_id = btrim(external_learner_id)) AND (external_learner_id <> ''::text)))
dispatch_grants_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_grants_hash_once ON public.dispatch_grants USING btree (capability_hash)
CREATE UNIQUE INDEX dispatch_grants_pkey ON public.dispatch_grants USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"dispatch_grants_by_hash","check":null,"roles":["public"],"using":"(capability_hash = decode(NULLIF(current_setting('app.dispatch_grant_hash'::text, true), ''::text), 'hex'::text))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"dispatch_grants_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00031_dispatch_registrations.sql

## dispatch_launcher_packages

Hosted distribution: Stored hosted-course launcher package metadata.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| deployment_id | uuid | no | — |
| sha256 | bytea | no | — |
| byte_length | bigint | no | — |
| revoked_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_launcher_bytes: CHECK ((byte_length > 0))
dispatch_launcher_deployment_fk: FOREIGN KEY (organisation_id, deployment_id) REFERENCES dispatch_deployments(organisation_id, id)
dispatch_launcher_packages_pkey: PRIMARY KEY (organisation_id, id)
dispatch_launcher_sha256: CHECK ((octet_length(sha256) = 32))
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_launcher_packages_pkey ON public.dispatch_launcher_packages USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"dispatch_launcher_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00030_dispatch_destinations.sql

## dispatch_registrations

Hosted distribution: Destination learner launch registrations.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| destination_id | uuid | no | — |
| deployment_id | uuid | no | — |
| external_learner_id | text | no | — |
| external_attempt_id | text | no | — |
| user_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| course_version_id | uuid | no | — |
| deployment_revision | integer | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_reg_deployment_fk: FOREIGN KEY (organisation_id, deployment_id) REFERENCES dispatch_deployments(organisation_id, id)
dispatch_reg_destination_fk: FOREIGN KEY (organisation_id, destination_id) REFERENCES dispatch_destinations(organisation_id, id)
dispatch_reg_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
dispatch_reg_tuple_once: UNIQUE (organisation_id, destination_id, external_learner_id, deployment_id, external_attempt_id)
dispatch_reg_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
dispatch_reg_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
dispatch_registrations_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_reg_tuple_once ON public.dispatch_registrations USING btree (organisation_id, destination_id, external_learner_id, deployment_id, external_attempt_id)
CREATE UNIQUE INDEX dispatch_registrations_pkey ON public.dispatch_registrations USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"dispatch_reg_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00031_dispatch_registrations.sql, migrations/00032_dispatch_results.sql

## dispatch_results

Hosted distribution: Recorded destination runtime results.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| registration_id | uuid | no | — |
| revision | integer | no | — |
| lesson_status | text | no | — |
| acknowledged_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
dispatch_results_pkey: PRIMARY KEY (organisation_id, registration_id, revision)
dispatch_results_registration_fk: FOREIGN KEY (organisation_id, registration_id) REFERENCES dispatch_registrations(organisation_id, id)
dispatch_results_revision_positive: CHECK ((revision > 0))
dispatch_results_status: CHECK ((lesson_status = ANY (ARRAY['passed'::text, 'completed'::text, 'failed'::text, 'incomplete'::text, 'browsed'::text, 'not attempted'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX dispatch_results_pkey ON public.dispatch_results USING btree (organisation_id, registration_id, revision)
```

### Policies

```sql
{"name":"dispatch_results_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00032_dispatch_results.sql

## domains

Tenancy & company: Application host resolution records; distinct from unactivated custom-domain claims.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| hostname | text | no | — |
| verified | boolean | no | false |

### Constraints

```sql
domains_normalised: CHECK (((hostname = lower(hostname)) AND (hostname = btrim(hostname)) AND ("right"(hostname, 1) <> '.'::text) AND (hostname !~ '[[:space:]]'::text)))
domains_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
domains_pkey: PRIMARY KEY (organisation_id, hostname)
```

### Indexes

```sql
CREATE UNIQUE INDEX domains_one_verified_hostname ON public.domains USING btree (hostname) WHERE verified
CREATE UNIQUE INDEX domains_pkey ON public.domains USING btree (organisation_id, hostname)
```

### Policies

```sql
{"name":"domains_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
{"name":"domains_verified_lookup","check":null,"roles":["public"],"using":"verified","command":"SELECT","permissive":"PERMISSIVE"}
```

Migration references: migrations/00003_organisations.sql

## enrolment_summaries

Evidence & reporting: Read-model summaries derived from enrolment evidence.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| user_id | uuid | no | — |
| course_id | uuid | no | — |
| course_title | text | no | — |
| learner_name | text | no | — |
| state | text | no | — |
| due_at | timestamp with time zone | yes | — |
| access_expires_at | timestamp with time zone | yes | — |
| completed_at | timestamp with time zone | yes | — |
| refreshed_at | timestamp with time zone | no | — |

### Constraints

```sql
enrolment_summaries_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
enrolment_summaries_pkey: PRIMARY KEY (organisation_id, enrolment_id)
enrolment_summaries_state: CHECK ((state = ANY (ARRAY['assigned'::text, 'in_progress'::text, 'completed'::text, 'expired'::text, 'cancelled'::text])))
enrolment_summaries_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX enrolment_summaries_pkey ON public.enrolment_summaries USING btree (organisation_id, enrolment_id)
```

### Policies

```sql
{"name":"enrolment_summaries_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00021_report_exports.sql

## enrolments

Learning & assessment: One assigned learning cycle, pinned to a course version, with independent due/access-expiry dates.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| user_id | uuid | no | — |
| course_id | uuid | no | — |
| course_version_id | uuid | no | — |
| cycle_number | integer | no | — |
| state | text | no | — |
| due_at | timestamp with time zone | yes | — |
| access_expires_at | timestamp with time zone | yes | — |

### Constraints

```sql
enrolments_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
enrolments_cycle_once: UNIQUE (organisation_id, user_id, course_id, cycle_number)
enrolments_cycle_positive: CHECK ((cycle_number > 0))
enrolments_pkey: PRIMARY KEY (organisation_id, id)
enrolments_state: CHECK ((state = ANY (ARRAY['assigned'::text, 'in_progress'::text, 'completed'::text, 'expired'::text, 'cancelled'::text])))
enrolments_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
enrolments_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX enrolments_cycle_once ON public.enrolments USING btree (organisation_id, user_id, course_id, cycle_number)
CREATE UNIQUE INDEX enrolments_one_active ON public.enrolments USING btree (organisation_id, user_id, course_id) WHERE (state = ANY (ARRAY['assigned'::text, 'in_progress'::text]))
CREATE INDEX enrolments_people_summary ON public.enrolments USING btree (organisation_id, user_id, state) INCLUDE (due_at)
CREATE UNIQUE INDEX enrolments_pkey ON public.enrolments USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"enrolments_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00011_enrolments.sql, migrations/00013_learning_progress.sql, migrations/00014_quiz_attempts.sql, migrations/00015_completions.sql, migrations/00016_media_leases.sql, migrations/00018_certificates.sql, migrations/00021_report_exports.sql, migrations/00028_scorm_launch_registrations.sql, migrations/00029_scorm_cmi_state.sql, migrations/00031_dispatch_registrations.sql, migrations/00034_extensions.sql, migrations/00045_experience.sql, migrations/00049_learning_packages.sql, migrations/00053_collections_pathways.sql, migrations/00054_people_directory.sql

## experience_deliveries

Activity & analytics: Activity-event delivery state for configured integrations.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| event_id | uuid | no | — |
| provider | text | no | — |
| state | text | no | 'queued'::text |
| reason | text | no | ''::text |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
experience_deliveries_organisation_id_event_id_fkey: FOREIGN KEY (organisation_id, event_id) REFERENCES experience_events(organisation_id, id)
experience_deliveries_pkey: PRIMARY KEY (organisation_id, event_id, provider)
experience_deliveries_state_check: CHECK ((state = ANY (ARRAY['queued'::text, 'accepted'::text, 'skipped'::text, 'failed'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX experience_deliveries_pkey ON public.experience_deliveries USING btree (organisation_id, event_id, provider)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00045_experience.sql

## experience_events

Activity & analytics: Accepted consent-aware activity events.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| actor | text | no | — |
| user_id | uuid | yes | — |
| event_name | text | no | — |
| page | text | no | — |
| payload | jsonb | no | — |
| browser_context | jsonb | no | '{}'::jsonb |
| payload_hash | text | no | — |
| analytics | boolean | no | — |
| marketing | boolean | no | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
experience_events_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE INDEX experience_events_actor_time ON public.experience_events USING btree (organisation_id, actor, created_at)
CREATE UNIQUE INDEX experience_events_pkey ON public.experience_events USING btree (organisation_id, id)
CREATE INDEX experience_events_recent ON public.experience_events USING btree (organisation_id, created_at DESC)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00045_experience.sql

## experience_preferences

Activity & analytics: User activity/analytics consent preferences.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| actor | text | no | — |
| analytics | boolean | no | false |
| marketing | boolean | no | false |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
experience_preferences_pkey: PRIMARY KEY (organisation_id, actor)
```

### Indexes

```sql
CREATE UNIQUE INDEX experience_preferences_pkey ON public.experience_preferences USING btree (organisation_id, actor)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00045_experience.sql

## experience_settings

Activity & analytics: Company analytics/integration configuration.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| revision | bigint | no | 1 |
| destinations | jsonb | no | '[]'::jsonb |

### Constraints

```sql
experience_settings_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
experience_settings_pkey: PRIMARY KEY (organisation_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX experience_settings_pkey ON public.experience_settings USING btree (organisation_id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00045_experience.sql

## extension_activity_records

Integrations & extensions: Extension-owned activity records through the extension boundary.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| extension_id | text | no | — |
| extension_version | text | no | — |
| enrolment_id | uuid | no | — |
| payload | jsonb | no | — |
| completed | boolean | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
extension_activity_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
extension_activity_id_present: CHECK (((extension_id = btrim(extension_id)) AND (extension_id <> ''::text)))
extension_activity_records_pkey: PRIMARY KEY (organisation_id, id)
extension_activity_version_present: CHECK (((extension_version = btrim(extension_version)) AND (extension_version <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX extension_activity_records_pkey ON public.extension_activity_records USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"extension_activity_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00034_extensions.sql

## extension_report_records

Integrations & extensions: Extension report records through the extension boundary.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| extension_id | text | no | — |
| extension_version | text | no | — |
| parameters | jsonb | no | — |
| result | jsonb | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
extension_report_id_present: CHECK (((extension_id = btrim(extension_id)) AND (extension_id <> ''::text)))
extension_report_records_pkey: PRIMARY KEY (organisation_id, id)
extension_report_version_present: CHECK (((extension_version = btrim(extension_version)) AND (extension_version <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX extension_report_records_pkey ON public.extension_report_records USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"extension_report_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00034_extensions.sql

## goose_db_version

Platform & recovery: Goose application migration ledger.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| id | integer | no | — |
| version_id | bigint | no | — |
| is_applied | boolean | no | — |
| tstamp | timestamp without time zone | no | now() |

### Constraints

```sql
goose_db_version_pkey: PRIMARY KEY (id)
```

### Indexes

```sql
CREATE UNIQUE INDEX goose_db_version_pkey ON public.goose_db_version USING btree (id)
```

Migration references: River versioned migrations from pinned Go dependency.

## group_memberships

Identity & access: User membership of a group.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| group_id | uuid | no | — |
| user_id | uuid | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
group_memberships_group_fk: FOREIGN KEY (organisation_id, group_id) REFERENCES groups(organisation_id, id)
group_memberships_pkey: PRIMARY KEY (organisation_id, group_id, user_id)
group_memberships_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX group_memberships_pkey ON public.group_memberships USING btree (organisation_id, group_id, user_id)
CREATE INDEX groups_member_user ON public.group_memberships USING btree (organisation_id, user_id, group_id)
```

### Policies

```sql
{"name":"group_memberships_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00024_scim_groups.sql, migrations/00054_people_directory.sql

## groups

Identity & access: Managed group records; their presence does not establish a completed pathway UI.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| name | text | no | — |
| scim_resource_id | uuid | yes | — |
| created_at | timestamp with time zone | no | — |
| description | text | no | ''::text |
| revision | bigint | no | 1 |
| archived | boolean | no | false |

### Constraints

```sql
groups_name_present: CHECK (((name = btrim(name)) AND (name <> ''::text)))
groups_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
groups_pkey: PRIMARY KEY (organisation_id, id)
groups_revision_check: CHECK ((revision > 0))
groups_scim_fk: FOREIGN KEY (organisation_id, scim_resource_id) REFERENCES scim_resources(organisation_id, id)
```

### Indexes

```sql
CREATE INDEX groups_name_order ON public.groups USING btree (organisation_id, lower(name), id)
CREATE UNIQUE INDEX groups_pkey ON public.groups USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"groups_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00024_scim_groups.sql, migrations/00054_people_directory.sql

## identity_links

Identity & access: External issuer/subject links to tenant users.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| issuer | text | no | — |
| subject | text | no | — |
| user_id | uuid | no | — |

### Constraints

```sql
identity_links_issuer_present: CHECK (((issuer = btrim(issuer)) AND (issuer <> ''::text)))
identity_links_pkey: PRIMARY KEY (organisation_id, issuer, subject)
identity_links_subject_present: CHECK (((subject = btrim(subject)) AND (subject <> ''::text)))
identity_links_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX identity_links_pkey ON public.identity_links USING btree (organisation_id, issuer, subject)
```

### Policies

```sql
{"name":"identity_links_for_subject","check":null,"roles":["public"],"using":"((current_setting('app.identity_issuer'::text, true) <> ''::text) AND (issuer = current_setting('app.identity_issuer'::text, true)) AND (subject = current_setting('app.identity_subject'::text, true)))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"identity_links_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00004_identity.sql, migrations/00008_profiles.sql

## invitations

Identity & access: Invitation records and acceptance lifecycle.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| token_hash | bytea | no | — |
| user_id | uuid | no | — |
| connection_id | uuid | no | — |
| user_authz_version | bigint | no | — |
| connection_version | bigint | no | — |
| created_by | uuid | no | — |
| expires_at | timestamp with time zone | no | — |
| consumed_at | timestamp with time zone | yes | — |

### Constraints

```sql
invitations_connection_fk: FOREIGN KEY (organisation_id, connection_id) REFERENCES oidc_connections(organisation_id, id)
invitations_creator_fk: FOREIGN KEY (organisation_id, created_by) REFERENCES users(organisation_id, id)
invitations_hash_present: CHECK ((octet_length(token_hash) = 32))
invitations_pkey: PRIMARY KEY (organisation_id, id)
invitations_token_once: UNIQUE (organisation_id, token_hash)
invitations_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX invitations_pkey ON public.invitations USING btree (organisation_id, id)
CREATE UNIQUE INDEX invitations_token_once ON public.invitations USING btree (organisation_id, token_hash)
```

### Policies

```sql
{"name":"invitations_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00006_invitations.sql

## learning_package_attempts

Learning packages: Learner attempts and bounded SCORM runtime state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| enrolment_id | uuid | no | — |
| activity_id | uuid | no | — |
| package_id | uuid | no | — |
| user_id | uuid | no | — |
| attempt | integer | no | 1 |
| data | jsonb | no | '{}'::jsonb |
| revision | bigint | no | 0 |
| finished | boolean | no | false |
| created_at | timestamp with time zone | no | now() |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
learning_package_attempts_organisation_id_activity_id_fkey: FOREIGN KEY (organisation_id, activity_id) REFERENCES course_version_activities(organisation_id, id)
learning_package_attempts_organisation_id_enrolment_id_acti_key: UNIQUE (organisation_id, enrolment_id, activity_id, attempt)
learning_package_attempts_organisation_id_enrolment_id_fkey: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
learning_package_attempts_organisation_id_package_id_fkey: FOREIGN KEY (organisation_id, package_id) REFERENCES learning_packages(organisation_id, id)
learning_package_attempts_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
learning_package_attempts_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_package_attempts_organisation_id_enrolment_id_acti_key ON public.learning_package_attempts USING btree (organisation_id, enrolment_id, activity_id, attempt)
CREATE UNIQUE INDEX learning_package_attempts_pkey ON public.learning_package_attempts USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00049_learning_packages.sql

## learning_package_launches

Learning packages: Launch capabilities and session/attempt bindings.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| token_hash | bytea | no | — |
| package_id | uuid | no | — |
| user_id | uuid | no | — |
| attempt_id | uuid | yes | — |
| preview | boolean | no | — |
| initial_data | jsonb | no | '{}'::jsonb |
| session_centiseconds | bigint | no | 0 |
| last_sequence | bigint | no | 0 |
| last_hash | text | no | ''::text |
| finished | boolean | no | false |
| session_hash | bytea | no | — |
| revoked | boolean | no | false |
| expires_at | timestamp with time zone | no | — |

### Constraints

```sql
learning_package_launches_check: CHECK ((preview = (attempt_id IS NULL)))
learning_package_launches_organisation_id_attempt_id_fkey: FOREIGN KEY (organisation_id, attempt_id) REFERENCES learning_package_attempts(organisation_id, id)
learning_package_launches_organisation_id_package_id_fkey: FOREIGN KEY (organisation_id, package_id) REFERENCES learning_packages(organisation_id, id)
learning_package_launches_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
learning_package_launches_pkey: PRIMARY KEY (organisation_id, id)
learning_package_launches_session_hash_check: CHECK ((octet_length(session_hash) = 32))
learning_package_launches_token_hash_check: CHECK ((octet_length(token_hash) = 32))
learning_package_launches_token_hash_key: UNIQUE (token_hash)
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_package_launches_pkey ON public.learning_package_launches USING btree (organisation_id, id)
CREATE UNIQUE INDEX learning_package_launches_token_hash_key ON public.learning_package_launches USING btree (token_hash)
CREATE INDEX learning_package_live ON public.learning_package_launches USING btree (organisation_id, attempt_id, expires_at)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00049_learning_packages.sql

## learning_packages

Learning packages: Versioned application-facing SCORM package metadata and immutable object references.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| family_id | uuid | no | — |
| version | integer | no | — |
| title | text | no | — |
| filename | text | no | — |
| object_id | text | no | — |
| sha256 | text | no | — |
| byte_length | bigint | no | — |
| launch_href | text | no | — |
| state | text | no | — |
| created_by | uuid | no | — |
| request_id | uuid | no | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
learning_packages_byte_length_check: CHECK ((byte_length > 0))
learning_packages_organisation_id_created_by_fkey: FOREIGN KEY (organisation_id, created_by) REFERENCES users(organisation_id, id)
learning_packages_organisation_id_family_id_version_key: UNIQUE (organisation_id, family_id, version)
learning_packages_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
learning_packages_organisation_id_request_id_key: UNIQUE (organisation_id, request_id)
learning_packages_pkey: PRIMARY KEY (organisation_id, id)
learning_packages_state_check: CHECK ((state = ANY (ARRAY['validated'::text, 'ready'::text])))
learning_packages_version_check: CHECK ((version > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_packages_organisation_id_family_id_version_key ON public.learning_packages USING btree (organisation_id, family_id, version)
CREATE UNIQUE INDEX learning_packages_organisation_id_request_id_key ON public.learning_packages USING btree (organisation_id, request_id)
CREATE UNIQUE INDEX learning_packages_pkey ON public.learning_packages USING btree (organisation_id, id)
CREATE INDEX learning_packages_recent ON public.learning_packages USING btree (organisation_id, created_at DESC)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00049_learning_packages.sql

## learning_pathways

Catalogue & publishing: Editable programme draft and archival state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| revision | bigint | no | — |
| draft | jsonb | no | — |
| archived | boolean | no | false |

### Constraints

```sql
learning_pathways_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
learning_pathways_pkey: PRIMARY KEY (organisation_id, id)
learning_pathways_revision_check: CHECK ((revision > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_pathways_pkey ON public.learning_pathways USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"learning_pathways_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## learning_resources

Resources & AI: Company text resources with revisions and archival state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| title | text | no | — |
| summary | text | no | ''::text |
| category | text | no | — |
| kind | text | no | — |
| body | text | no | — |
| revision | bigint | no | 1 |
| archived | boolean | no | false |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
learning_resources_body_check: CHECK (((length(body) >= 1) AND (length(body) <= 30000)))
learning_resources_category_check: CHECK (((length(category) >= 1) AND (length(category) <= 60)))
learning_resources_kind_check: CHECK ((kind = ANY (ARRAY['guide'::text, 'checklist'::text, 'template'::text, 'policy'::text])))
learning_resources_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
learning_resources_pkey: PRIMARY KEY (organisation_id, id)
learning_resources_revision_check: CHECK ((revision > 0))
learning_resources_summary_check: CHECK ((length(summary) <= 600))
learning_resources_title_check: CHECK (((length(title) >= 1) AND (length(title) <= 160)))
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_resources_pkey ON public.learning_resources USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"learning_resources_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00042_resource_library.sql

## learning_sessions

Learning & assessment: Authorised native learning sessions for enrolments.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| enrolment_id | uuid | no | — |
| user_id | uuid | no | — |
| created_at | timestamp with time zone | no | — |
| closed_at | timestamp with time zone | yes | — |

### Constraints

```sql
learning_sessions_closed: CHECK (((closed_at IS NULL) OR (closed_at >= created_at)))
learning_sessions_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
learning_sessions_pkey: PRIMARY KEY (organisation_id, id)
learning_sessions_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX learning_sessions_pkey ON public.learning_sessions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"learning_sessions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00013_learning_progress.sql, migrations/00016_media_leases.sql, migrations/00029_scorm_cmi_state.sql

## media_assets

Media: Media preparation and approved rendition selection.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| quarantine_id | uuid | no | — |
| source_object_id | text | no | — |
| state | text | no | — |
| lease_generation | bigint | no | 0 |
| winning_attempt_id | uuid | yes | — |
| ready_master_object_id | text | yes | — |
| created_at | timestamp with time zone | no | — |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
media_assets_generation: CHECK ((lease_generation >= 0))
media_assets_pkey: PRIMARY KEY (organisation_id, id)
media_assets_quarantine_fk: FOREIGN KEY (organisation_id, quarantine_id) REFERENCES quarantine_uploads(organisation_id, id)
media_assets_quarantine_once: UNIQUE (organisation_id, quarantine_id)
media_assets_ready_master: CHECK ((((state = 'ready'::text) AND (winning_attempt_id IS NOT NULL) AND (ready_master_object_id IS NOT NULL)) OR ((state <> 'ready'::text) AND (winning_attempt_id IS NULL) AND (ready_master_object_id IS NULL))))
media_assets_source_present: CHECK (((source_object_id = btrim(source_object_id)) AND (source_object_id <> ''::text)))
media_assets_state: CHECK ((state = ANY (ARRAY['queued'::text, 'processing'::text, 'ready'::text, 'failed'::text, 'cancelled'::text])))
media_assets_winning_attempt_fk: FOREIGN KEY (organisation_id, winning_attempt_id) REFERENCES media_attempts(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX media_assets_pkey ON public.media_assets USING btree (organisation_id, id)
CREATE UNIQUE INDEX media_assets_quarantine_once ON public.media_assets USING btree (organisation_id, quarantine_id)
```

### Policies

```sql
{"name":"media_assets_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00017_media_renditions.sql

## media_attempt_objects

Media: Immutable objects emitted by a processing attempt.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| attempt_id | uuid | no | — |
| object_id | text | no | — |
| role | text | no | — |
| byte_length | bigint | no | — |
| sha256 | bytea | no | — |

### Constraints

```sql
media_attempt_objects_attempt_fk: FOREIGN KEY (organisation_id, attempt_id) REFERENCES media_attempts(organisation_id, id)
media_attempt_objects_byte_length: CHECK ((byte_length >= 0))
media_attempt_objects_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
media_attempt_objects_pkey: PRIMARY KEY (organisation_id, attempt_id, object_id)
media_attempt_objects_role: CHECK ((role = ANY (ARRAY['master'::text, 'playlist'::text, 'init'::text, 'segment'::text])))
media_attempt_objects_sha256: CHECK ((octet_length(sha256) = 32))
```

### Indexes

```sql
CREATE UNIQUE INDEX media_attempt_objects_pkey ON public.media_attempt_objects USING btree (organisation_id, attempt_id, object_id)
```

### Policies

```sql
{"name":"media_attempt_objects_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00017_media_renditions.sql

## media_attempts

Media: Individual processing attempts and leases.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| media_asset_id | uuid | no | — |
| generation | bigint | no | — |
| state | text | no | — |
| profile_version | text | no | — |
| diagnostic_code | text | yes | — |
| created_at | timestamp with time zone | no | — |
| finished_at | timestamp with time zone | yes | — |

### Constraints

```sql
media_attempts_asset_fk: FOREIGN KEY (organisation_id, media_asset_id) REFERENCES media_assets(organisation_id, id)
media_attempts_generation: CHECK ((generation > 0))
media_attempts_generation_once: UNIQUE (organisation_id, media_asset_id, generation)
media_attempts_pkey: PRIMARY KEY (organisation_id, id)
media_attempts_profile: CHECK (((profile_version = btrim(profile_version)) AND (profile_version <> ''::text)))
media_attempts_state: CHECK ((state = ANY (ARRAY['processing'::text, 'complete'::text, 'failed'::text, 'stale'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX media_attempts_generation_once ON public.media_attempts USING btree (organisation_id, media_asset_id, generation)
CREATE UNIQUE INDEX media_attempts_pkey ON public.media_attempts USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"media_attempts_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00017_media_renditions.sql

## media_leases

Media: Scoped playback authorisation leases.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| capability_hash | bytea | no | — |
| purpose | text | no | — |
| session_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| user_id | uuid | no | — |
| object_id | text | no | — |
| expires_at | timestamp with time zone | no | — |
| revoked_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
media_leases_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
media_leases_hash_once: UNIQUE (capability_hash)
media_leases_hash_present: CHECK ((octet_length(capability_hash) = 32))
media_leases_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
media_leases_pkey: PRIMARY KEY (organisation_id, id)
media_leases_purpose: CHECK ((purpose = 'learning'::text))
media_leases_session_fk: FOREIGN KEY (organisation_id, session_id) REFERENCES learning_sessions(organisation_id, id)
media_leases_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX media_leases_hash_once ON public.media_leases USING btree (capability_hash)
CREATE UNIQUE INDEX media_leases_pkey ON public.media_leases USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"media_leases_by_hash","check":null,"roles":["public"],"using":"(capability_hash = decode(NULLIF(current_setting('app.media_capability_hash'::text, true), ''::text), 'hex'::text))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"media_leases_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00016_media_leases.sql

## message_deliveries

Messaging: Recipient/channel delivery attempts and status.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| message_id | uuid | no | — |
| user_id | uuid | no | — |
| channel | text | no | — |
| state | text | no | — |
| reason | text | no | ''::text |
| read_at | timestamp with time zone | yes | — |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
message_deliveries_channel_check: CHECK ((channel = ANY (ARRAY['in_app'::text, 'email'::text])))
message_deliveries_organisation_id_message_id_fkey: FOREIGN KEY (organisation_id, message_id) REFERENCES messages(organisation_id, id)
message_deliveries_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
message_deliveries_pkey: PRIMARY KEY (organisation_id, message_id, user_id, channel)
message_deliveries_state_check: CHECK ((state = ANY (ARRAY['available'::text, 'queued'::text, 'accepted'::text, 'failed'::text, 'skipped'::text, 'uncertain'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX message_deliveries_pkey ON public.message_deliveries USING btree (organisation_id, message_id, user_id, channel)
CREATE INDEX message_deliveries_recipient ON public.message_deliveries USING btree (organisation_id, user_id, message_id)
```

### Policies

```sql
{"name":"message_deliveries_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00044_messaging.sql

## message_preferences

Messaging: Recipient message channel preferences.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| email | boolean | no | false |

### Constraints

```sql
message_preferences_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
message_preferences_pkey: PRIMARY KEY (organisation_id, user_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX message_preferences_pkey ON public.message_preferences USING btree (organisation_id, user_id)
```

### Policies

```sql
{"name":"message_preferences_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00044_messaging.sql

## messages

Messaging: Company announcement content and audience/channel configuration.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| sender_id | uuid | no | — |
| subject | text | no | — |
| body | text | no | — |
| revision | bigint | no | 1 |
| state | text | no | 'draft'::text |
| recipients | uuid[] | no | — |
| channels | text[] | no | — |
| created_at | timestamp with time zone | no | now() |
| updated_at | timestamp with time zone | no | now() |
| sent_at | timestamp with time zone | yes | — |

### Constraints

```sql
messages_body_check: CHECK (((length(body) >= 1) AND (length(body) <= 8000)))
messages_organisation_id_sender_id_fkey: FOREIGN KEY (organisation_id, sender_id) REFERENCES users(organisation_id, id)
messages_pkey: PRIMARY KEY (organisation_id, id)
messages_revision_check: CHECK ((revision > 0))
messages_state_check: CHECK ((state = ANY (ARRAY['draft'::text, 'sent'::text])))
messages_subject_check: CHECK (((length(subject) >= 1) AND (length(subject) <= 160)))
```

### Indexes

```sql
CREATE UNIQUE INDEX messages_pkey ON public.messages USING btree (organisation_id, id)
CREATE INDEX messages_sender ON public.messages USING btree (organisation_id, sender_id, created_at DESC, id DESC)
```

### Policies

```sql
{"name":"messages_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00044_messaging.sql

## modules

Catalogue & publishing: Ordered modules of a mutable course draft.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| course_id | uuid | no | — |
| title | text | no | — |
| position | integer | no | — |
| revision | bigint | no | 1 |
| summary | text | no | ''::text |

### Constraints

```sql
modules_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
modules_pkey: PRIMARY KEY (organisation_id, id)
modules_position_nonnegative: CHECK (("position" >= 0))
modules_position_once: UNIQUE (organisation_id, course_id, "position")
modules_revision_positive: CHECK ((revision > 0))
modules_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX modules_pkey ON public.modules USING btree (organisation_id, id)
CREATE UNIQUE INDEX modules_position_once ON public.modules USING btree (organisation_id, course_id, "position")
```

### Policies

```sql
{"name":"modules_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00009_drafts.sql, migrations/00046_course_builder.sql

## oidc_connections

Federation & provisioning: Identity connection records used by the identity service.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| issuer | text | no | — |
| enabled | boolean | no | false |
| version | bigint | no | 1 |
| required_assurance | text | no | ''::text |

### Constraints

```sql
oidc_connections_issuer_once: UNIQUE (organisation_id, issuer)
oidc_connections_issuer_present: CHECK (((issuer = btrim(issuer)) AND (issuer <> ''::text)))
oidc_connections_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
oidc_connections_pkey: PRIMARY KEY (organisation_id, id)
oidc_connections_version_positive: CHECK ((version > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX oidc_connections_issuer_once ON public.oidc_connections USING btree (organisation_id, issuer)
CREATE UNIQUE INDEX oidc_connections_pkey ON public.oidc_connections USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"oidc_connections_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00006_invitations.sql, migrations/00008_profiles.sql

## oidc_logout_receipts

Identity & access: Deduplication receipts for verified provider logout events.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| issuer | text | no | — |
| jti_hash | bytea | no | — |
| subject_hash | bytea | yes | — |
| sid_hash | bytea | yes | — |
| issued_at | timestamp with time zone | no | — |
| expires_at | timestamp with time zone | no | — |

### Constraints

```sql
oidc_logout_receipts_check: CHECK (((subject_hash IS NOT NULL) OR (sid_hash IS NOT NULL)))
oidc_logout_receipts_jti_hash_check: CHECK ((octet_length(jti_hash) = 32))
oidc_logout_receipts_pkey: PRIMARY KEY (issuer, jti_hash)
oidc_logout_receipts_sid_hash_check: CHECK (((sid_hash IS NULL) OR (octet_length(sid_hash) = 32)))
oidc_logout_receipts_subject_hash_check: CHECK (((subject_hash IS NULL) OR (octet_length(subject_hash) = 32)))
```

### Indexes

```sql
CREATE INDEX oidc_logout_expiry ON public.oidc_logout_receipts USING btree (issuer, expires_at)
CREATE UNIQUE INDEX oidc_logout_receipts_pkey ON public.oidc_logout_receipts USING btree (issuer, jti_hash)
```

### Policies

```sql
{"name":"oidc_logout_issuer","check":"(issuer = NULLIF(current_setting('app.logout_issuer'::text, true), ''::text))","roles":["public"],"using":"(issuer = NULLIF(current_setting('app.logout_issuer'::text, true), ''::text))","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00040_oidc_session_revocation.sql

## organisation_access_commands

Tenancy & company: Idempotent administrative command receipts.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| actor_id | uuid | no | — |
| payload_hash | bytea | no | — |
| restriction | jsonb | yes | — |
| journal_recorded | boolean | no | false |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
organisation_access_commands_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
organisation_access_commands_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX organisation_access_commands_pkey ON public.organisation_access_commands USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"organisation_access_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00041_organisation_administration.sql

## organisation_audit

Tenancy & company: Audited administrative actions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| actor_id | uuid | no | — |
| action | text | no | — |
| resource_id | text | no | ''::text |
| created_at | timestamp with time zone | no | now() |
| metadata | jsonb | no | '{}'::jsonb |

### Constraints

```sql
organisation_audit_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
organisation_audit_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX organisation_audit_pkey ON public.organisation_audit USING btree (organisation_id, id)
CREATE INDEX organisation_audit_recent ON public.organisation_audit USING btree (organisation_id, created_at DESC)
```

### Policies

```sql
{"name":"organisation_audit_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00041_organisation_administration.sql, migrations/00055_support_views.sql

## organisation_extensions

Integrations & extensions: Organisation extension registrations.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| extension_id | text | no | — |
| extension_version | text | no | — |
| enabled | boolean | no | — |
| installed_at | timestamp with time zone | no | — |
| disabled_at | timestamp with time zone | yes | — |

### Constraints

```sql
organisation_extensions_id_present: CHECK (((extension_id = btrim(extension_id)) AND (extension_id <> ''::text)))
organisation_extensions_pkey: PRIMARY KEY (organisation_id, extension_id)
organisation_extensions_version_present: CHECK (((extension_version = btrim(extension_version)) AND (extension_version <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX organisation_extensions_pkey ON public.organisation_extensions USING btree (organisation_id, extension_id)
```

### Policies

```sql
{"name":"organisation_extensions_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00034_extensions.sql

## organisation_slugs

Tenancy & company: Current and reserved company sign-in slugs.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| slug | text | no | — |
| organisation_id | uuid | no | — |

### Constraints

```sql
organisation_slugs_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
organisation_slugs_pkey: PRIMARY KEY (slug)
organisation_slugs_slug_check: CHECK ((slug ~ '^[a-z0-9][a-z0-9-]{1,61}[a-z0-9]$'::text))
```

### Indexes

```sql
CREATE UNIQUE INDEX organisation_slugs_pkey ON public.organisation_slugs USING btree (slug)
```

### Policies

```sql
{"name":"slug_public_lookup","check":null,"roles":["public"],"using":"true","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"slug_tenant_insert","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":null,"command":"INSERT","permissive":"PERMISSIVE"}
```

Migration references: migrations/00048_company_addresses.sql

## organisation_sso

Federation & provisioning: Company SSO configuration records.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| revision | bigint | no | 1 |
| checked_revision | bigint | no | 0 |
| config_ciphertext | text | no | — |
| status | text | no | 'draft'::text |
| message | text | no | ''::text |
| desired_enabled | boolean | no | false |
| published_revision | bigint | no | 0 |
| last_sign_in | timestamp with time zone | yes | — |
| updated_at | timestamp with time zone | no | now() |
| id | uuid | no | — |
| name | text | no | 'Company sign-in'::text |
| protocol | text | no | ''::text |
| broker_alias | text | yes | (('zublms-'::text \|\| (organisation_id)::text) \|\| CASE     WHEN (id = organisation_id) THEN ''::text     ELSE ('-'::text \|\| (id)::text) END) |

### Constraints

```sql
organisation_sso_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
organisation_sso_pkey: PRIMARY KEY (organisation_id, id)
organisation_sso_protocol_check: CHECK ((protocol = ANY (ARRAY[''::text, 'saml'::text, 'oidc'::text])))
organisation_sso_status_check: CHECK ((status = ANY (ARRAY['draft'::text, 'checked'::text, 'pending'::text, 'active'::text, 'disabling'::text, 'disabled'::text, 'error'::text])))
```

### Indexes

```sql
CREATE INDEX organisation_sso_active ON public.organisation_sso USING btree (organisation_id, id) WHERE ((status = 'active'::text) AND desired_enabled AND (published_revision = revision))
CREATE UNIQUE INDEX organisation_sso_alias ON public.organisation_sso USING btree (broker_alias)
CREATE UNIQUE INDEX organisation_sso_pkey ON public.organisation_sso USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"organisation_sso_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00041_organisation_administration.sql, migrations/00047_multi_company_sso.sql

## organisation_sso_domains

Federation & provisioning: Company SSO email-domain hints; not website domain hosting.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| connection_id | uuid | no | — |
| domain | text | no | — |

### Constraints

```sql
organisation_sso_domains_domain_check: CHECK (((domain = lower(domain)) AND ((length(domain) >= 3) AND (length(domain) <= 253))))
organisation_sso_domains_organisation_id_connection_id_fkey: FOREIGN KEY (organisation_id, connection_id) REFERENCES organisation_sso(organisation_id, id)
organisation_sso_domains_pkey: PRIMARY KEY (organisation_id, domain)
```

### Indexes

```sql
CREATE INDEX organisation_sso_domains_connection ON public.organisation_sso_domains USING btree (organisation_id, connection_id)
CREATE UNIQUE INDEX organisation_sso_domains_pkey ON public.organisation_sso_domains USING btree (organisation_id, domain)
```

### Policies

```sql
{"name":"organisation_sso_domains_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00047_multi_company_sso.sql

## organisations

Tenancy & company: Company identity and tenant boundary.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| id | uuid | no | — |
| organisation_id | uuid | no | — |
| name | text | no | — |
| timezone | text | no | 'UTC'::text |
| scope_epoch | bigint | no | 1 |
| settings_revision | bigint | no | 0 |
| logo_data | text | no | ''::text |
| accent | text | no | 'violet'::text |
| support_email | text | no | ''::text |
| support_url | text | no | ''::text |
| workspace_slug | text | no | ''::text |
| address_revision | bigint | no | 0 |
| allow_slug_changes | boolean | no | true |
| allow_custom_domains | boolean | no | false |

### Constraints

```sql
organisations_pkey: PRIMARY KEY (id)
organisations_scope_epoch_positive: CHECK ((scope_epoch > 0))
organisations_self: CHECK ((organisation_id = id))
organisations_timezone_present: CHECK (((timezone = btrim(timezone)) AND (timezone <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX organisations_pkey ON public.organisations USING btree (id)
```

### Policies

```sql
{"name":"organisations_for_subject","check":null,"roles":["public"],"using":"(id IN ( SELECT identity_links.organisation_id\n   FROM identity_links\n  WHERE ((current_setting('app.identity_issuer'::text, true) <> ''::text) AND (identity_links.issuer = current_setting('app.identity_issuer'::text, true)) AND (identity_links.subject = current_setting('app.identity_subject'::text, true)))))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"organisations_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

### Triggers

```sql
CREATE TRIGGER organisation_recovery_registry AFTER INSERT ON public.organisations FOR EACH ROW EXECUTE FUNCTION register_recovery_organisation()
```

Migration references: migrations/00003_organisations.sql, migrations/00004_identity.sql, migrations/00005_browser_sessions.sql, migrations/00006_invitations.sql, migrations/00007_step_up.sql, migrations/00008_profiles.sql, migrations/00009_drafts.sql, migrations/00012_quarantine.sql, migrations/00020_refresher_schedule.sql, migrations/00022_report_download_authority.sql, migrations/00023_scim_users.sql, migrations/00024_scim_groups.sql, migrations/00035_native_workspace.sql, migrations/00037_recovery_admission.sql, migrations/00039_report_requests.sql, migrations/00041_organisation_administration.sql, migrations/00042_resource_library.sql, migrations/00045_experience.sql, migrations/00048_company_addresses.sql, migrations/00049_learning_packages.sql, migrations/00050_ai_draft_requests.sql, migrations/00052_ai_provider_management.sql, migrations/00053_collections_pathways.sql

## outbox_events

Platform & recovery: Durable domain events awaiting downstream handling.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| event_type | text | no | — |
| aggregate_id | uuid | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
outbox_events_pkey: PRIMARY KEY (organisation_id, id)
outbox_events_type_present: CHECK (((event_type = btrim(event_type)) AND (event_type <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX outbox_events_pkey ON public.outbox_events USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"outbox_events_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00015_completions.sql

## pathway_assignments

Learning & assessment: Learner assignment to a stable programme version and deadline.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| pathway_id | uuid | no | — |
| version_id | uuid | no | — |
| user_id | uuid | no | — |
| assigned_by | uuid | no | — |
| assigned_at | timestamp with time zone | no | now() |
| due_at | timestamp with time zone | yes | — |
| started_at | timestamp with time zone | yes | — |
| cancelled_at | timestamp with time zone | yes | — |

### Constraints

```sql
pathway_assignments_organisation_id_assigned_by_fkey: FOREIGN KEY (organisation_id, assigned_by) REFERENCES users(organisation_id, id)
pathway_assignments_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
pathway_assignments_organisation_id_version_id_pathway_id_fkey: FOREIGN KEY (organisation_id, version_id, pathway_id) REFERENCES pathway_versions(organisation_id, id, pathway_id)
pathway_assignments_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX pathway_assignments_pkey ON public.pathway_assignments USING btree (organisation_id, id)
CREATE INDEX pathway_assignments_user ON public.pathway_assignments USING btree (organisation_id, user_id, assigned_at)
CREATE UNIQUE INDEX pathway_one_live_assignment ON public.pathway_assignments USING btree (organisation_id, pathway_id, user_id) WHERE (cancelled_at IS NULL)
```

### Policies

```sql
{"name":"pathway_assignments_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## pathway_course_links

Learning & assessment: Explicit links to credited or launched enrolment cycles.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| assignment_id | uuid | no | — |
| course_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| credited | boolean | no | false |

### Constraints

```sql
pathway_course_links_organisation_id_assignment_id_fkey: FOREIGN KEY (organisation_id, assignment_id) REFERENCES pathway_assignments(organisation_id, id)
pathway_course_links_organisation_id_course_id_fkey: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
pathway_course_links_organisation_id_enrolment_id_fkey: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
pathway_course_links_pkey: PRIMARY KEY (organisation_id, assignment_id, course_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX pathway_course_links_pkey ON public.pathway_course_links USING btree (organisation_id, assignment_id, course_id)
```

### Policies

```sql
{"name":"pathway_course_links_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## pathway_version_courses

Catalogue & publishing: Tenant-scoped foreign keys for pinned programme course versions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| version_id | uuid | no | — |
| course_id | uuid | no | — |
| course_version_id | uuid | no | — |

### Constraints

```sql
pathway_version_courses_organisation_id_course_id_fkey: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
pathway_version_courses_organisation_id_course_version_id_fkey: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
pathway_version_courses_organisation_id_version_id_fkey: FOREIGN KEY (organisation_id, version_id) REFERENCES pathway_versions(organisation_id, id)
pathway_version_courses_pkey: PRIMARY KEY (organisation_id, version_id, course_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX pathway_version_courses_pkey ON public.pathway_version_courses USING btree (organisation_id, version_id, course_id)
```

### Policies

```sql
{"name":"pathway_version_courses_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## pathway_versions

Catalogue & publishing: Immutable programme publication with pinned course definitions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| pathway_id | uuid | no | — |
| version_number | integer | no | — |
| source_revision | bigint | no | — |
| definition | jsonb | no | — |
| published_at | timestamp with time zone | no | now() |

### Constraints

```sql
pathway_versions_organisation_id_id_pathway_id_key: UNIQUE (organisation_id, id, pathway_id)
pathway_versions_organisation_id_pathway_id_fkey: FOREIGN KEY (organisation_id, pathway_id) REFERENCES learning_pathways(organisation_id, id)
pathway_versions_organisation_id_pathway_id_source_revision_key: UNIQUE (organisation_id, pathway_id, source_revision)
pathway_versions_organisation_id_pathway_id_version_number_key: UNIQUE (organisation_id, pathway_id, version_number)
pathway_versions_pkey: PRIMARY KEY (organisation_id, id)
pathway_versions_version_number_check: CHECK ((version_number > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX pathway_versions_organisation_id_id_pathway_id_key ON public.pathway_versions USING btree (organisation_id, id, pathway_id)
CREATE UNIQUE INDEX pathway_versions_organisation_id_pathway_id_source_revision_key ON public.pathway_versions USING btree (organisation_id, pathway_id, source_revision)
CREATE UNIQUE INDEX pathway_versions_organisation_id_pathway_id_version_number_key ON public.pathway_versions USING btree (organisation_id, pathway_id, version_number)
CREATE UNIQUE INDEX pathway_versions_pkey ON public.pathway_versions USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"pathway_versions_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00053_collections_pathways.sql

## pending_bindings

Federation & provisioning: Pending identity binding decisions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| invitation_id | uuid | no | — |
| user_id | uuid | no | — |
| connection_id | uuid | no | — |
| issuer | text | no | — |
| subject | text | no | — |
| user_authz_version | bigint | no | — |
| connection_version | bigint | no | — |
| status | text | no | 'pending'::text |

### Constraints

```sql
pending_bindings_connection_fk: FOREIGN KEY (organisation_id, connection_id) REFERENCES oidc_connections(organisation_id, id)
pending_bindings_invitation_fk: FOREIGN KEY (organisation_id, invitation_id) REFERENCES invitations(organisation_id, id)
pending_bindings_invitation_once: UNIQUE (organisation_id, invitation_id)
pending_bindings_pkey: PRIMARY KEY (organisation_id, id)
pending_bindings_status: CHECK ((status = ANY (ARRAY['pending'::text, 'approved'::text, 'rejected'::text])))
pending_bindings_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX pending_bindings_invitation_once ON public.pending_bindings USING btree (organisation_id, invitation_id)
CREATE UNIQUE INDEX pending_bindings_pkey ON public.pending_bindings USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"pending_bindings_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00006_invitations.sql

## people_bulk_results

Identity & access: Atomic per-person results that prevent a confirmed bulk effect from repeating.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| run_id | uuid | no | — |
| user_id | uuid | no | — |
| result | jsonb | no | — |

### Constraints

```sql
people_bulk_results_organisation_id_run_id_fkey: FOREIGN KEY (organisation_id, run_id) REFERENCES people_bulk_runs(organisation_id, id)
people_bulk_results_pkey: PRIMARY KEY (organisation_id, run_id, user_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX people_bulk_results_pkey ON public.people_bulk_results USING btree (organisation_id, run_id, user_id)
```

### Policies

```sql
{"name":"people_bulk_results_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00054_people_directory.sql

## people_bulk_runs

Identity & access: Tenant and actor bound hash of each reviewed explicit people action for safe retries.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| actor_id | uuid | no | — |
| payload_hash | text | no | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
people_bulk_runs_organisation_id_actor_id_fkey: FOREIGN KEY (organisation_id, actor_id) REFERENCES users(organisation_id, id)
people_bulk_runs_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX people_bulk_runs_pkey ON public.people_bulk_runs USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"people_bulk_runs_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00054_people_directory.sql

## platform_object_location

Platform & recovery: Object backend routing and tombstone metadata.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| object_id | text | no | — |
| purpose | text | no | — |
| backend | text | no | — |
| storage_key | text | no | — |
| byte_length | bigint | no | — |
| sha256 | text | no | — |
| generation | bigint | no | — |
| tombstoned_at | timestamp with time zone | yes | — |
| updated_at | timestamp with time zone | no | now() |

### Constraints

```sql
platform_object_location_backend_check: CHECK ((backend = ANY (ARRAY['filesystem'::text, 's3'::text])))
platform_object_location_byte_length_check: CHECK ((byte_length >= 0))
platform_object_location_generation_check: CHECK ((generation >= 1))
platform_object_location_pkey: PRIMARY KEY (object_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX platform_object_location_pkey ON public.platform_object_location USING btree (object_id)
```

Migration references: migrations/00002_platform_object_location.sql

## platform_organisation_registry

Tenancy & company: Platform operator enumeration of registered companies.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |

### Constraints

```sql
platform_organisation_registry_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
platform_organisation_registry_pkey: PRIMARY KEY (organisation_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX platform_organisation_registry_pkey ON public.platform_organisation_registry USING btree (organisation_id)
```

Migration references: migrations/00037_recovery_admission.sql

## platform_tenant_smoke

Platform & recovery: Synthetic tenant-boundary test table retained in the migration history.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| label | text | no | — |

### Constraints

```sql
platform_tenant_smoke_pkey: PRIMARY KEY (organisation_id, label)
```

### Indexes

```sql
CREATE UNIQUE INDEX platform_tenant_smoke_pkey ON public.platform_tenant_smoke USING btree (organisation_id, label)
```

### Policies

```sql
{"name":"platform_tenant_smoke_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00001_synthetic_tenant_guard.sql

## profiles

Identity & access: Protected user profile values.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| name_envelope | bytea | no | — |
| email_envelope | bytea | no | — |
| phone_envelope | bytea | no | — |
| address_envelope | bytea | no | — |
| attributes_envelope | bytea | no | — |
| email_index | bytea | no | — |

### Constraints

```sql
profiles_email_index_once: UNIQUE (organisation_id, email_index)
profiles_pkey: PRIMARY KEY (organisation_id, user_id)
profiles_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX profiles_email_index_once ON public.profiles USING btree (organisation_id, email_index)
CREATE UNIQUE INDEX profiles_pkey ON public.profiles USING btree (organisation_id, user_id)
```

### Policies

```sql
{"name":"profiles_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00008_profiles.sql

## progress_commands

Learning & assessment: Idempotent native progress command receipts.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| enrolment_id | uuid | no | — |
| session_id | uuid | no | — |
| command_key | text | no | — |
| activity_id | uuid | no | — |
| kind | text | no | — |
| server_revision | bigint | no | — |
| accepted_at | timestamp with time zone | no | — |

### Constraints

```sql
progress_commands_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
progress_commands_key_present: CHECK (((command_key = btrim(command_key)) AND (command_key <> ''::text)))
progress_commands_kind: CHECK ((kind = 'acknowledge'::text))
progress_commands_once: UNIQUE (organisation_id, enrolment_id, session_id, command_key)
progress_commands_pkey: PRIMARY KEY (organisation_id, id)
progress_commands_revision_positive: CHECK ((server_revision > 0))
progress_commands_session_fk: FOREIGN KEY (organisation_id, session_id) REFERENCES learning_sessions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX progress_commands_once ON public.progress_commands USING btree (organisation_id, enrolment_id, session_id, command_key)
CREATE UNIQUE INDEX progress_commands_pkey ON public.progress_commands USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"progress_commands_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00013_learning_progress.sql

## quarantine_uploads

Media: Untrusted uploaded objects pending review/scanning.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| object_id | text | no | — |
| byte_length | bigint | no | — |
| sha256 | bytea | no | — |
| scan_state | text | no | 'pending'::text |

### Constraints

```sql
quarantine_uploads_byte_length: CHECK ((byte_length >= 0))
quarantine_uploads_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
quarantine_uploads_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
quarantine_uploads_pkey: PRIMARY KEY (organisation_id, id)
quarantine_uploads_scan_state: CHECK ((scan_state = ANY (ARRAY['pending'::text, 'clean'::text, 'failed'::text])))
quarantine_uploads_sha256: CHECK ((octet_length(sha256) = 32))
```

### Indexes

```sql
CREATE UNIQUE INDEX quarantine_uploads_pkey ON public.quarantine_uploads USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"quarantine_uploads_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00012_quarantine.sql, migrations/00017_media_renditions.sql

## recovery_checkpoints

Platform & recovery: Recorded recovery-journal replay checkpoints.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| sequence | bigint | no | — |
| head_hash | text | no | — |
| verified_at | timestamp with time zone | no | now() |

### Constraints

```sql
recovery_checkpoints_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
recovery_checkpoints_pkey: PRIMARY KEY (organisation_id)
recovery_checkpoints_sequence_check: CHECK ((sequence >= 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX recovery_checkpoints_pkey ON public.recovery_checkpoints USING btree (organisation_id)
```

### Policies

```sql
{"name":"recovery_checkpoint_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00037_recovery_admission.sql

## refresher_occurrences

Learning & assessment: Idempotent occurrences of refresher rules.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| rule_id | uuid | no | — |
| user_id | uuid | no | — |
| certificate_id | uuid | no | — |
| scheduled_instant | timestamp with time zone | no | — |
| state | text | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
refresher_occurrences_certificate_fk: FOREIGN KEY (organisation_id, certificate_id) REFERENCES certificates(organisation_id, id)
refresher_occurrences_once: UNIQUE (organisation_id, rule_id, user_id, scheduled_instant)
refresher_occurrences_pkey: PRIMARY KEY (organisation_id, id)
refresher_occurrences_rule_fk: FOREIGN KEY (organisation_id, rule_id) REFERENCES refresher_rules(organisation_id, id)
refresher_occurrences_state: CHECK ((state = ANY (ARRAY['scheduled'::text, 'skipped'::text, 'enqueued'::text])))
refresher_occurrences_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX refresher_occurrences_once ON public.refresher_occurrences USING btree (organisation_id, rule_id, user_id, scheduled_instant)
CREATE UNIQUE INDEX refresher_occurrences_pkey ON public.refresher_occurrences USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"refresher_occurrences_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00020_refresher_schedule.sql

## refresher_rules

Learning & assessment: Rules for ordinary-learner refresher cycles.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| course_id | uuid | no | — |
| name | text | no | — |

### Constraints

```sql
refresher_rules_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
refresher_rules_name_present: CHECK (((name = btrim(name)) AND (name <> ''::text)))
refresher_rules_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX refresher_rules_pkey ON public.refresher_rules USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"refresher_rules_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00020_refresher_schedule.sql

## report_export_artefacts

Evidence & reporting: Generated report objects and access metadata.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| lease_id | uuid | no | — |
| attempt_id | uuid | no | — |
| lease_generation | bigint | no | — |
| object_id | text | no | — |
| byte_length | bigint | no | — |
| sha256 | bytea | no | — |
| published_at | timestamp with time zone | no | — |
| published_principal_authz_version | bigint | no | 1 |
| published_org_scope_epoch | bigint | no | 1 |
| requesting_principal_id | uuid | yes | — |

### Constraints

```sql
report_export_artefacts_attempt_once: UNIQUE (organisation_id, lease_id, attempt_id)
report_export_artefacts_authz_positive: CHECK ((published_principal_authz_version > 0))
report_export_artefacts_byte_length: CHECK ((byte_length >= 0))
report_export_artefacts_lease_fk: FOREIGN KEY (organisation_id, lease_id) REFERENCES report_export_leases(organisation_id, id)
report_export_artefacts_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
report_export_artefacts_pkey: PRIMARY KEY (organisation_id, id)
report_export_artefacts_principal_fk: FOREIGN KEY (organisation_id, requesting_principal_id) REFERENCES users(organisation_id, id)
report_export_artefacts_scope_positive: CHECK ((published_org_scope_epoch > 0))
report_export_artefacts_sha256: CHECK ((octet_length(sha256) = 32))
```

### Indexes

```sql
CREATE UNIQUE INDEX report_export_artefacts_attempt_once ON public.report_export_artefacts USING btree (organisation_id, lease_id, attempt_id)
CREATE UNIQUE INDEX report_export_artefacts_pkey ON public.report_export_artefacts USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"report_export_artefacts_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00021_report_exports.sql, migrations/00022_report_download_authority.sql, migrations/00039_report_requests.sql

## report_export_leases

Evidence & reporting: Bounded export ownership and authorisation leases.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| report_key | text | no | — |
| lease_generation | bigint | no | — |
| attempt_id | uuid | no | — |
| state | text | no | — |
| expires_at | timestamp with time zone | no | — |
| created_at | timestamp with time zone | no | — |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
report_export_leases_active_once: UNIQUE (organisation_id, report_key)
report_export_leases_generation: CHECK ((lease_generation > 0))
report_export_leases_key_present: CHECK (((report_key = btrim(report_key)) AND (report_key <> ''::text)))
report_export_leases_pkey: PRIMARY KEY (organisation_id, id)
report_export_leases_state: CHECK ((state = ANY (ARRAY['running'::text, 'succeeded'::text, 'cancelled'::text, 'lost'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX report_export_leases_active_once ON public.report_export_leases USING btree (organisation_id, report_key)
CREATE UNIQUE INDEX report_export_leases_pkey ON public.report_export_leases USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"report_export_leases_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00021_report_exports.sql

## report_requests

Evidence & reporting: Asynchronous report request lifecycle.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| user_id | uuid | no | — |
| state | text | no | 'queued'::text |
| artefact_id | uuid | yes | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
report_requests_organisation_id_artefact_id_fkey: FOREIGN KEY (organisation_id, artefact_id) REFERENCES report_export_artefacts(organisation_id, id)
report_requests_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
report_requests_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
report_requests_pkey: PRIMARY KEY (organisation_id, id)
report_requests_state_check: CHECK ((state = ANY (ARRAY['queued'::text, 'ready'::text, 'failed'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX report_requests_pkey ON public.report_requests USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"report_request_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00039_report_requests.sql

## resource_bookmarks

Resources & AI: Personal saved-resource relationships.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| resource_id | uuid | no | — |

### Constraints

```sql
resource_bookmarks_organisation_id_resource_id_fkey: FOREIGN KEY (organisation_id, resource_id) REFERENCES learning_resources(organisation_id, id)
resource_bookmarks_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
resource_bookmarks_pkey: PRIMARY KEY (organisation_id, user_id, resource_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX resource_bookmarks_pkey ON public.resource_bookmarks USING btree (organisation_id, user_id, resource_id)
```

### Policies

```sql
{"name":"resource_bookmarks_tenant","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00042_resource_library.sql

## river_client

River job engine: River worker client heartbeat records.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| id | text | no | — |
| created_at | timestamp with time zone | no | now() |
| metadata | jsonb | no | '{}'::jsonb |
| paused_at | timestamp with time zone | yes | — |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
name_length: CHECK (((char_length(id) > 0) AND (char_length(id) < 128)))
river_client_pkey: PRIMARY KEY (id)
```

### Indexes

```sql
CREATE UNIQUE INDEX river_client_pkey ON public.river_client USING btree (id)
```

Migration references: River versioned migrations from pinned Go dependency.

## river_client_queue

River job engine: River client-to-queue metadata.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| river_client_id | text | no | — |
| name | text | no | — |
| created_at | timestamp with time zone | no | now() |
| max_workers | bigint | no | 0 |
| metadata | jsonb | no | '{}'::jsonb |
| num_jobs_completed | bigint | no | 0 |
| num_jobs_running | bigint | no | 0 |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
name_length: CHECK (((char_length(name) > 0) AND (char_length(name) < 128)))
num_jobs_completed_zero_or_positive: CHECK ((num_jobs_completed >= 0))
num_jobs_running_zero_or_positive: CHECK ((num_jobs_running >= 0))
river_client_queue_pkey: PRIMARY KEY (river_client_id, name)
river_client_queue_river_client_id_fkey: FOREIGN KEY (river_client_id) REFERENCES river_client(id) ON DELETE CASCADE
```

### Indexes

```sql
CREATE UNIQUE INDEX river_client_queue_pkey ON public.river_client_queue USING btree (river_client_id, name)
```

Migration references: River versioned migrations from pinned Go dependency.

## river_job

River job engine: River persistent job queue and execution state.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| id | bigint | no | nextval('river_job_id_seq'::regclass) |
| state | river_job_state | no | 'available'::river_job_state |
| attempt | smallint | no | 0 |
| max_attempts | smallint | no | — |
| attempted_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | now() |
| finalized_at | timestamp with time zone | yes | — |
| scheduled_at | timestamp with time zone | no | now() |
| priority | smallint | no | 1 |
| args | jsonb | no | — |
| attempted_by | text[] | yes | — |
| errors | jsonb[] | yes | — |
| kind | text | no | — |
| metadata | jsonb | no | '{}'::jsonb |
| queue | text | no | 'default'::text |
| tags | character varying(255)[] | no | '{}'::character varying[] |
| unique_key | bytea | yes | — |
| unique_states | bit(8) | yes | — |

### Constraints

```sql
finalized_or_finalized_at_null: CHECK ((((finalized_at IS NULL) AND (state <> ALL (ARRAY['cancelled'::river_job_state, 'completed'::river_job_state, 'discarded'::river_job_state]))) OR ((finalized_at IS NOT NULL) AND (state = ANY (ARRAY['cancelled'::river_job_state, 'completed'::river_job_state, 'discarded'::river_job_state])))))
kind_length: CHECK (((char_length(kind) > 0) AND (char_length(kind) < 128)))
max_attempts_is_positive: CHECK ((max_attempts > 0))
priority_in_range: CHECK (((priority >= 1) AND (priority <= 4)))
queue_length: CHECK (((char_length(queue) > 0) AND (char_length(queue) < 128)))
river_job_pkey: PRIMARY KEY (id)
```

### Indexes

```sql
CREATE INDEX river_job_args_index ON public.river_job USING gin (args)
CREATE INDEX river_job_kind ON public.river_job USING btree (kind)
CREATE INDEX river_job_metadata_index ON public.river_job USING gin (metadata)
CREATE UNIQUE INDEX river_job_pkey ON public.river_job USING btree (id)
CREATE INDEX river_job_prioritized_fetching_index ON public.river_job USING btree (state, queue, priority, scheduled_at, id)
CREATE INDEX river_job_state_and_finalized_at_index ON public.river_job USING btree (state, finalized_at) WHERE (finalized_at IS NOT NULL)
CREATE UNIQUE INDEX river_job_unique_idx ON public.river_job USING btree (unique_key) WHERE ((unique_key IS NOT NULL) AND (unique_states IS NOT NULL) AND river_job_state_in_bitmask(unique_states, state))
```

Migration references: River versioned migrations from pinned Go dependency.

## river_leader

River job engine: River leader election records.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| elected_at | timestamp with time zone | no | — |
| expires_at | timestamp with time zone | no | — |
| leader_id | text | no | — |
| name | text | no | 'default'::text |

### Constraints

```sql
leader_id_length: CHECK (((char_length(leader_id) > 0) AND (char_length(leader_id) < 128)))
name_length: CHECK ((name = 'default'::text))
river_leader_pkey: PRIMARY KEY (name)
```

### Indexes

```sql
CREATE UNIQUE INDEX river_leader_pkey ON public.river_leader USING btree (name)
```

Migration references: River versioned migrations from pinned Go dependency.

## river_migration

River job engine: River-owned schema migration ledger.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| line | text | no | — |
| version | bigint | no | — |
| created_at | timestamp with time zone | no | now() |

### Constraints

```sql
line_length: CHECK (((char_length(line) > 0) AND (char_length(line) < 128)))
river_migration_pkey1: PRIMARY KEY (line, version)
version_gte_1: CHECK ((version >= 1))
```

### Indexes

```sql
CREATE UNIQUE INDEX river_migration_pkey1 ON public.river_migration USING btree (line, version)
```

Migration references: River versioned migrations from pinned Go dependency.

## river_queue

River job engine: River queue pause/metadata state.

RLS enabled: false; forced: false.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| name | text | no | — |
| created_at | timestamp with time zone | no | now() |
| metadata | jsonb | no | '{}'::jsonb |
| paused_at | timestamp with time zone | yes | — |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
river_queue_pkey: PRIMARY KEY (name)
```

### Indexes

```sql
CREATE UNIQUE INDEX river_queue_pkey ON public.river_queue USING btree (name)
```

Migration references: River versioned migrations from pinned Go dependency.

## role_bindings

Identity & access: Role grants scoped to a user and optional organisational unit.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| user_id | uuid | no | — |
| role | text | no | — |
| unit_id | uuid | yes | — |

### Constraints

```sql
role_bindings_once: UNIQUE NULLS NOT DISTINCT (organisation_id, user_id, role, unit_id)
role_bindings_pkey: PRIMARY KEY (organisation_id, id)
role_bindings_role: CHECK ((role = ANY (ARRAY['owner'::text, 'administrator'::text, 'unit_manager'::text, 'author'::text, 'learner'::text, 'integration_principal'::text])))
role_bindings_scope: CHECK ((((role = ANY (ARRAY['owner'::text, 'administrator'::text])) AND (unit_id IS NULL)) OR ((role = 'unit_manager'::text) AND (unit_id IS NOT NULL)) OR (role = ANY (ARRAY['author'::text, 'learner'::text, 'integration_principal'::text]))))
role_bindings_unit_fk: FOREIGN KEY (organisation_id, unit_id) REFERENCES units(organisation_id, id)
role_bindings_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX role_bindings_once ON public.role_bindings USING btree (organisation_id, user_id, role, unit_id) NULLS NOT DISTINCT
CREATE UNIQUE INDEX role_bindings_pkey ON public.role_bindings USING btree (organisation_id, id)
CREATE INDEX role_bindings_unit_user ON public.role_bindings USING btree (organisation_id, unit_id, user_id)
```

### Policies

```sql
{"name":"role_bindings_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

### Triggers

```sql
CREATE TRIGGER report_roles_changed AFTER INSERT OR DELETE OR UPDATE ON public.role_bindings FOR EACH ROW EXECUTE FUNCTION invalidate_report_scope()
CREATE TRIGGER role_bindings_bump_authz AFTER INSERT OR DELETE OR UPDATE ON public.role_bindings FOR EACH ROW EXECUTE FUNCTION role_bindings_bump_authz()
```

Migration references: migrations/00004_identity.sql, migrations/00039_report_requests.sql, migrations/00054_people_directory.sql

## scim_connections

Federation & provisioning: Provisioning connection configuration.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| name | text | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
scim_connections_name_present: CHECK (((name = btrim(name)) AND (name <> ''::text)))
scim_connections_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
scim_connections_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX scim_connections_pkey ON public.scim_connections USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"scim_connections_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00023_scim_users.sql

## scim_resources

Federation & provisioning: Provisioning resource mappings and versions.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| connection_id | uuid | no | — |
| resource_type | text | no | — |
| external_id_blind | bytea | yes | — |
| external_id_ciphertext | bytea | yes | — |
| display_name | text | no | — |
| active | boolean | no | true |
| user_id | uuid | yes | — |
| version | bigint | no | 1 |
| created_at | timestamp with time zone | no | — |
| updated_at | timestamp with time zone | no | — |
| email_index | bytea | yes | — |
| tombstoned_at | timestamp with time zone | yes | — |

### Constraints

```sql
scim_resources_connection_fk: FOREIGN KEY (organisation_id, connection_id) REFERENCES scim_connections(organisation_id, id)
scim_resources_display_present: CHECK (((display_name = btrim(display_name)) AND (display_name <> ''::text)))
scim_resources_email_index_len: CHECK (((email_index IS NULL) OR (octet_length(email_index) = 32)))
scim_resources_external_pair: CHECK ((((external_id_blind IS NULL) AND (external_id_ciphertext IS NULL)) OR ((octet_length(external_id_blind) = 32) AND (external_id_ciphertext IS NOT NULL))))
scim_resources_pkey: PRIMARY KEY (organisation_id, id)
scim_resources_type: CHECK ((resource_type = ANY (ARRAY['User'::text, 'Group'::text])))
scim_resources_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
scim_resources_version_positive: CHECK ((version > 0))
```

### Indexes

```sql
CREATE UNIQUE INDEX scim_resources_external_once ON public.scim_resources USING btree (organisation_id, connection_id, resource_type, external_id_blind) WHERE ((external_id_blind IS NOT NULL) AND (tombstoned_at IS NULL))
CREATE UNIQUE INDEX scim_resources_pkey ON public.scim_resources USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"scim_resources_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00023_scim_users.sql, migrations/00024_scim_groups.sql, migrations/00025_scim_email_index.sql, migrations/00026_scim_tombstone.sql

## scorm_cmi_state

Learning packages: Earlier standards runtime CMI state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| activity_id | uuid | no | — |
| launch_registration_id | uuid | no | — |
| learning_session_id | uuid | no | — |
| committed_status | text | no | — |
| pending_status | text | yes | — |
| open | boolean | no | — |
| updated_at | timestamp with time zone | no | — |

### Constraints

```sql
scorm_cmi_committed: CHECK ((committed_status = ANY (ARRAY['passed'::text, 'completed'::text, 'failed'::text, 'incomplete'::text, 'browsed'::text, 'not attempted'::text])))
scorm_cmi_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
scorm_cmi_launch_fk: FOREIGN KEY (organisation_id, launch_registration_id) REFERENCES scorm_launch_registrations(organisation_id, id)
scorm_cmi_pending: CHECK (((pending_status IS NULL) OR (pending_status = ANY (ARRAY['passed'::text, 'completed'::text, 'failed'::text, 'incomplete'::text, 'browsed'::text, 'not attempted'::text]))))
scorm_cmi_session_fk: FOREIGN KEY (organisation_id, learning_session_id) REFERENCES learning_sessions(organisation_id, id)
scorm_cmi_state_pkey: PRIMARY KEY (organisation_id, enrolment_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX scorm_cmi_state_pkey ON public.scorm_cmi_state USING btree (organisation_id, enrolment_id)
```

### Policies

```sql
{"name":"scorm_cmi_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00029_scorm_cmi_state.sql

## scorm_launch_registrations

Learning packages: Earlier standards runtime launch registrations.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| capability_hash | bytea | no | — |
| enrolment_id | uuid | no | — |
| user_id | uuid | no | — |
| course_version_id | uuid | no | — |
| package_id | uuid | no | — |
| launch_origin | text | no | — |
| launch_href | text | no | — |
| expires_at | timestamp with time zone | no | — |
| revoked_at | timestamp with time zone | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
scorm_launch_enrolment_fk: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
scorm_launch_hash_once: UNIQUE (capability_hash)
scorm_launch_hash_present: CHECK ((octet_length(capability_hash) = 32))
scorm_launch_href_present: CHECK (((launch_href = btrim(launch_href)) AND (launch_href <> ''::text)))
scorm_launch_origin_present: CHECK (((launch_origin = btrim(launch_origin)) AND (launch_origin <> ''::text)))
scorm_launch_package_fk: FOREIGN KEY (organisation_id, package_id) REFERENCES scorm_packages(organisation_id, id)
scorm_launch_registrations_pkey: PRIMARY KEY (organisation_id, id)
scorm_launch_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
scorm_launch_version_fk: FOREIGN KEY (organisation_id, course_version_id) REFERENCES course_versions(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX scorm_launch_hash_once ON public.scorm_launch_registrations USING btree (capability_hash)
CREATE UNIQUE INDEX scorm_launch_registrations_pkey ON public.scorm_launch_registrations USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"scorm_launch_by_hash","check":null,"roles":["public"],"using":"(capability_hash = decode(NULLIF(current_setting('app.scorm_launch_capability_hash'::text, true), ''::text), 'hex'::text))","command":"SELECT","permissive":"PERMISSIVE"}
{"name":"scorm_launch_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00028_scorm_launch_registrations.sql, migrations/00029_scorm_cmi_state.sql

## scorm_packages

Learning packages: Earlier standards-service package records; distinct from the current learning-packages UI model.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| course_id | uuid | no | — |
| object_id | text | no | — |
| sha256 | bytea | no | — |
| byte_length | bigint | no | — |
| title | text | no | — |
| item_title | text | no | — |
| launch_href | text | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
scorm_packages_byte_length: CHECK ((byte_length > 0))
scorm_packages_course_fk: FOREIGN KEY (organisation_id, course_id) REFERENCES courses(organisation_id, id)
scorm_packages_course_once: UNIQUE (organisation_id, course_id)
scorm_packages_launch_present: CHECK (((launch_href = btrim(launch_href)) AND (launch_href <> ''::text)))
scorm_packages_object_present: CHECK (((object_id = btrim(object_id)) AND (object_id <> ''::text)))
scorm_packages_pkey: PRIMARY KEY (organisation_id, id)
scorm_packages_sha256: CHECK ((octet_length(sha256) = 32))
scorm_packages_title_present: CHECK (((title = btrim(title)) AND (title <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX scorm_packages_course_once ON public.scorm_packages USING btree (organisation_id, course_id)
CREATE UNIQUE INDEX scorm_packages_pkey ON public.scorm_packages USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"scorm_packages_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00027_scorm_packages.sql, migrations/00028_scorm_launch_registrations.sql

## step_up_challenges

Identity & access: Recent-authentication challenge state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| state_hash | bytea | no | — |
| organisation_id | uuid | no | — |
| session_hash | bytea | no | — |
| user_id | uuid | no | — |
| issuer | text | no | — |
| subject | text | no | — |
| action | text | no | — |
| nonce | text | no | — |
| verifier | text | no | — |
| created_at | timestamp with time zone | no | — |
| expires_at | timestamp with time zone | no | — |

### Constraints

```sql
step_up_action_present: CHECK (((action = btrim(action)) AND (action <> ''::text)))
step_up_challenges_pkey: PRIMARY KEY (state_hash)
step_up_expiry: CHECK ((expires_at > created_at))
step_up_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
step_up_session_hash: CHECK ((octet_length(session_hash) = 32))
step_up_state_hash: CHECK ((octet_length(state_hash) = 32))
step_up_user_fk: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX step_up_challenges_pkey ON public.step_up_challenges USING btree (state_hash)
```

### Policies

```sql
{"name":"step_up_by_hash","check":"(state_hash = decode(NULLIF(current_setting('app.step_up_state_hash'::text, true), ''::text), 'hex'::text))","roles":["public"],"using":"(state_hash = decode(NULLIF(current_setting('app.step_up_state_hash'::text, true), ''::text), 'hex'::text))","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00007_step_up.sql

## support_views

Identity & access: Session-bound, time-limited read-only platform support overlay. Actor and target are distinct; starting and stopping are recorded in organisation_audit.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| session_hash | bytea | no | — |
| organisation_id | uuid | no | — |
| actor_id | uuid | no | — |
| target_organisation_id | uuid | no | — |
| target_user_id | uuid | no | — |
| target_authz_version | bigint | no | — |
| reason | text | no | — |
| created_at | timestamp with time zone | no | now() |
| expires_at | timestamp with time zone | no | — |

### Constraints

```sql
support_views_organisation_id_actor_id_fkey: FOREIGN KEY (organisation_id, actor_id) REFERENCES users(organisation_id, id)
support_views_pkey: PRIMARY KEY (session_hash)
support_views_reason_check: CHECK (((length(reason) >= 10) AND (length(reason) <= 500)))
support_views_session_hash_fkey: FOREIGN KEY (session_hash) REFERENCES browser_sessions(session_hash) ON DELETE CASCADE
support_views_target_organisation_id_target_user_id_fkey: FOREIGN KEY (target_organisation_id, target_user_id) REFERENCES users(organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX support_views_pkey ON public.support_views USING btree (session_hash)
```

### Policies

```sql
{"name":"support_views_session","check":"(session_hash = decode(NULLIF(current_setting('app.session_hash'::text, true), ''::text), 'hex'::text))","roles":["public"],"using":"(session_hash = decode(NULLIF(current_setting('app.session_hash'::text, true), ''::text), 'hex'::text))","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00055_support_views.sql

## tenant_keys

Identity & access: Tenant encryption-key metadata.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| wrapped_dek | text | no | — |
| wrapped_index | text | no | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
tenant_keys_dek_present: CHECK ((wrapped_dek <> ''::text))
tenant_keys_index_present: CHECK ((wrapped_index <> ''::text))
tenant_keys_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
tenant_keys_pkey: PRIMARY KEY (organisation_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX tenant_keys_pkey ON public.tenant_keys USING btree (organisation_id)
```

### Policies

```sql
{"name":"tenant_keys_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00008_profiles.sql

## units

Tenancy & company: Hierarchical organisational units used by scoped access.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| parent_id | uuid | yes | — |
| name | text | no | — |

### Constraints

```sql
units_not_own_parent: CHECK (((parent_id IS NULL) OR (parent_id <> id)))
units_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
units_parent_fk: FOREIGN KEY (organisation_id, parent_id) REFERENCES units(organisation_id, id)
units_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE UNIQUE INDEX units_pkey ON public.units USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"units_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

### Triggers

```sql
CREATE TRIGGER report_units_changed AFTER INSERT OR DELETE OR UPDATE ON public.units FOR EACH ROW EXECUTE FUNCTION invalidate_report_scope()
CREATE TRIGGER units_reject_cycle BEFORE INSERT OR UPDATE OF parent_id ON public.units FOR EACH ROW EXECUTE FUNCTION units_reject_cycle()
```

Migration references: migrations/00003_organisations.sql, migrations/00004_identity.sql, migrations/00039_report_requests.sql, migrations/00053_collections_pathways.sql

## users

Identity & access: Tenant-scoped users linked to external identity; not a local password store.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| display_name | text | no | — |
| suspended | boolean | no | false |
| authz_version | bigint | no | 1 |
| learner_kind | text | no | 'corporate'::text |

### Constraints

```sql
users_authz_version_positive: CHECK ((authz_version > 0))
users_learner_kind: CHECK ((learner_kind = ANY (ARRAY['corporate'::text, 'restricted_external'::text, 'dispatch'::text])))
users_organisation_fk: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
users_pkey: PRIMARY KEY (organisation_id, id)
```

### Indexes

```sql
CREATE INDEX people_name_order ON public.users USING btree (organisation_id, lower(display_name), id)
CREATE UNIQUE INDEX users_pkey ON public.users USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"users_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00004_identity.sql, migrations/00005_browser_sessions.sql, migrations/00006_invitations.sql, migrations/00007_step_up.sql, migrations/00008_profiles.sql, migrations/00011_enrolments.sql, migrations/00013_learning_progress.sql, migrations/00014_quiz_attempts.sql, migrations/00016_media_leases.sql, migrations/00018_certificates.sql, migrations/00019_certificate_revocation.sql, migrations/00020_refresher_schedule.sql, migrations/00021_report_exports.sql, migrations/00022_report_download_authority.sql, migrations/00023_scim_users.sql, migrations/00024_scim_groups.sql, migrations/00026_scim_tombstone.sql, migrations/00028_scorm_launch_registrations.sql, migrations/00031_dispatch_registrations.sql, migrations/00035_native_workspace.sql, migrations/00039_report_requests.sql, migrations/00042_resource_library.sql, migrations/00043_course_bookmarks.sql, migrations/00044_messaging.sql, migrations/00049_learning_packages.sql, migrations/00050_ai_draft_requests.sql, migrations/00053_collections_pathways.sql, migrations/00054_people_directory.sql, migrations/00055_support_views.sql

## video_engagement

Activity & analytics: Persisted video-engagement measurements; not independent proof of learning.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| enrolment_id | uuid | no | — |
| activity_id | uuid | no | — |
| playback_id | uuid | no | — |
| sequence | bigint | no | — |
| position | double precision | no | — |
| duration | double precision | no | — |
| watched_seconds | double precision | no | 0 |
| playing | boolean | no | — |
| rate | double precision | no | — |
| last_at | timestamp with time zone | no | now() |

### Constraints

```sql
video_engagement_organisation_id_activity_id_fkey: FOREIGN KEY (organisation_id, activity_id) REFERENCES course_version_activities(organisation_id, id)
video_engagement_organisation_id_enrolment_id_fkey: FOREIGN KEY (organisation_id, enrolment_id) REFERENCES enrolments(organisation_id, id)
video_engagement_pkey: PRIMARY KEY (organisation_id, enrolment_id, activity_id, playback_id)
```

### Indexes

```sql
CREATE UNIQUE INDEX video_engagement_pkey ON public.video_engagement USING btree (organisation_id, enrolment_id, activity_id, playback_id)
CREATE INDEX video_engagement_resume ON public.video_engagement USING btree (organisation_id, enrolment_id, activity_id, last_at DESC)
```

### Policies

```sql
{"name":"tenant_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00045_experience.sql

## webhook_deliveries

Integrations & extensions: Webhook delivery attempts and retry state.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| endpoint_id | uuid | no | — |
| event_id | text | no | — |
| event_type | text | no | — |
| payload | bytea | no | — |
| attempt_count | integer | no | — |
| next_attempt_at | timestamp with time zone | no | — |
| delivered_at | timestamp with time zone | yes | — |
| failed_at | timestamp with time zone | yes | — |
| last_status | integer | yes | — |
| created_at | timestamp with time zone | no | — |

### Constraints

```sql
webhook_deliveries_attempts: CHECK ((attempt_count >= 0))
webhook_deliveries_endpoint_fk: FOREIGN KEY (organisation_id, endpoint_id) REFERENCES webhook_endpoints(organisation_id, id)
webhook_deliveries_event_present: CHECK (((event_id = btrim(event_id)) AND (event_id <> ''::text)))
webhook_deliveries_payload: CHECK ((octet_length(payload) > 0))
webhook_deliveries_pkey: PRIMARY KEY (organisation_id, id)
webhook_deliveries_type_present: CHECK (((event_type = btrim(event_type)) AND (event_type <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX webhook_deliveries_pkey ON public.webhook_deliveries USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"webhook_deliveries_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00033_api_keys_webhooks.sql

## webhook_endpoints

Integrations & extensions: Registered webhook destinations.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| id | uuid | no | — |
| url | text | no | — |
| secret_hash | bytea | no | — |
| event_types | text[] | no | — |
| enabled | boolean | no | — |
| created_at | timestamp with time zone | no | — |
| disabled_at | timestamp with time zone | yes | — |

### Constraints

```sql
webhook_endpoints_events: CHECK ((cardinality(event_types) > 0))
webhook_endpoints_pkey: PRIMARY KEY (organisation_id, id)
webhook_endpoints_secret: CHECK ((octet_length(secret_hash) = 32))
webhook_endpoints_url_present: CHECK (((url = btrim(url)) AND (url <> ''::text)))
```

### Indexes

```sql
CREATE UNIQUE INDEX webhook_endpoints_pkey ON public.webhook_endpoints USING btree (organisation_id, id)
```

### Policies

```sql
{"name":"webhook_endpoints_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00033_api_keys_webhooks.sql

## workspace_commands

Catalogue & publishing: Command IDs, hashes and outcomes for native workspace writes.

RLS enabled: true; forced: true.

| Column | Type | Nullable | Default |
| --- | --- | --- | --- |
| organisation_id | uuid | no | — |
| user_id | uuid | no | — |
| id | uuid | no | — |
| payload_hash | bytea | no | — |
| created_at | timestamp with time zone | no | now() |
| result | text | no | 'accepted'::text |

### Constraints

```sql
workspace_commands_organisation_id_fkey: FOREIGN KEY (organisation_id) REFERENCES organisations(id)
workspace_commands_organisation_id_user_id_fkey: FOREIGN KEY (organisation_id, user_id) REFERENCES users(organisation_id, id)
workspace_commands_pkey: PRIMARY KEY (organisation_id, user_id, id)
workspace_commands_result_check: CHECK ((result = ANY (ARRAY['accepted'::text, 'incorrect'::text])))
```

### Indexes

```sql
CREATE UNIQUE INDEX workspace_commands_pkey ON public.workspace_commands USING btree (organisation_id, user_id, id)
```

### Policies

```sql
{"name":"workspace_commands_isolation","check":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","roles":["public"],"using":"(organisation_id = (NULLIF(current_setting('app.organisation_id'::text, true), ''::text))::uuid)","command":"ALL","permissive":"PERMISSIVE"}
```

Migration references: migrations/00035_native_workspace.sql, migrations/00036_workspace_command_results.sql