> ## Documentation Index
> Fetch the complete documentation index at: https://docs.findmydata.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Data schema

# Data schema reference

This document is the table-by-table reference for the Find My Data persistence layer, written for engineers and DBAs who need to read, extend, or debug the database. It covers every table created by migrations `0001`–`0012` in [`packages/server/migrations/`](../packages/server/migrations/), grouped by migration, and ends with an entity-relationship overview of the most central tables. As of release 26.7.15.0 the schema is a single SQLite database accessed only through role-scoped repository interfaces ([ADR-0003](adr/ADR-0003-persistence-single-sqlite-behind-role-interfaces.md)); see [architecture](architecture.md) for how the modules sit on top of it and the [threat model](threat-model.md) for the security assumptions the schema encodes.

## Conventions

* **IDs** — every primary key is `TEXT`: sortable, opaque, generated by the kernel (`packages/server/src/kernel/ids.ts`).
* **Times** — every timestamp is `INTEGER` UTC epoch **milliseconds** (`created_at`, `observed_at`, `expires_at`, …).
* **JSON columns** — columns holding serialized JSON end in `_json`; where they have a default, it is `'[]'` or `'{}'`.
* **Tenancy** — every business table carries a `NOT NULL tenant_id REFERENCES tenants(id)`, and unique indexes are tenant-prefixed (with a few deliberate exceptions such as join-table composite keys and per-parent uniques like `dataset_members` and `release_evaluations`). Two deliberate exceptions: `scanners` (deployment-level infrastructure, not tenant data) and the `search_fts` virtual table (tenant scoping is enforced by joining through `search_documents`).
* **Deny-by-default reads** — the schema assumes the authz kernel (`packages/server/src/kernel/authz.ts`): access is denied unless a role/ownership rule allows it, and UI hiding is never the enforcement mechanism. Rows do not carry per-row ACLs; scoping columns (`tenant_id`, `domain_id`) are the enforcement inputs.
* **Temporal validity** — slowly-changing facts use `valid_from` / `valid_to` (`valid_to IS NULL` = current) or an `is_current` flag with a partial index (`WHERE is_current = 1`). Observations are appended, never rewritten.
* **Provenance and status enums** — most governed rows carry a `provenance` (e.g. `ModelInferred`) and a `status` lifecycle enforced by `CHECK` constraints. AI output is never silently authoritative: statuses like `ProposedByAI` → `ValidatedByGRC` make the human step explicit ([ADR-0006](adr/ADR-0006-evidence-assertion-provenance.md)).
* **Rebuildable projections** — `graph_nodes`, `graph_edges`, `search_documents`, and `search_fts` are derived read models. They can be dropped and rebuilt from the base tables at any time; nothing authoritative lives in them.
* **Honesty in enums** — enums include values for what could *not* be observed (`not_observable`, `partial`, `unknown`, `not_enabled`) rather than defaulting to an optimistic value.

## Migration 0001 — tenancy, identity, governance taxonomy

`0001_identity_governance.sql`. The multi-tenant spine, identity/principal model, and the human-governed taxonomy (domains, information types, classifications, mappings, Purview labels).

### tenants

Root of all tenancy. Everything else hangs off `tenants.id`.

| Column                  | Type    | Meaning                                                                                                                                                                                         |
| ----------------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `id`                    | TEXT PK | Tenant id                                                                                                                                                                                       |
| `external_ms_tenant_id` | TEXT    | Linked Microsoft tenant, if any                                                                                                                                                                 |
| `region`                | TEXT    | Defaults `'local'`                                                                                                                                                                              |
| `data_handling_profile` | TEXT    | CHECK: `minimized_evidence` (default) \| `metadata_only` \| `enhanced_evidence` — governs how much derived content may be retained ([ADR-0011](adr/ADR-0011-retention-and-evidence-profile.md)) |
| `status`                | TEXT    | CHECK: `active` \| `suspended`                                                                                                                                                                  |
| `config_version`        | INTEGER | Bumped on config changes                                                                                                                                                                        |

### org\_profiles

One row per organization profile used to seed taxonomy generation: `name`, `industries_json`, `geographies_json`, `description`, `ai_provider` (default `'mock'`). `status` CHECK: `draft` | `approved`. FK: `tenant_id`.

### principals

Unified identity table for humans and non-humans. `kind` CHECK: `user` | `group` | `service` | `broad_audience` | `sharing_link`. Identity columns: `issuer`/`subject` (IdP claims, users), `external_id` (source directory id), `display_name`, `email`, `title`, `department`. `source` CHECK: `fixture` | `entra` | `system`; `lifecycle_status` CHECK: `active` | `disabled` | `deleted`.

Unique (partial) indexes: `(tenant_id, issuer, subject)` where `issuer IS NOT NULL`; `(tenant_id, external_id)` where `external_id IS NOT NULL`.

### group\_memberships

Observed group edges: `group_id` and `member_id` both FK `principals(id)`. `kind` CHECK: `direct` | `transitive`; `completeness` CHECK: `complete` | `partial` | `not_observable` (membership expansion may be unobservable and the schema says so). Unique: `(tenant_id, group_id, member_id, kind)`.

### role\_assignments

Application roles per principal. `role` CHECK: `platform_admin` | `governance_admin` | `domain_owner` | `remediation_approver` | `auditor`. Unique: `(tenant_id, principal_id, role)`. These roles are the input to deny-by-default authorization (see [permissions manifest](permissions-manifest.md)).

### sessions

Server sessions: `principal_id`, `auth_mode` CHECK `dev` | `entra` ([ADR-0004](adr/ADR-0004-authentication-dev-mode-and-entra.md)), `csrf_token`, `expires_at`, nullable `revoked_at`.

### generation\_runs

Provenance record for every AI generation: `task`, `provider`, `model`, `template_version`, `input_hash`, `status` CHECK `completed` | `failed`. Taxonomy rows point back here via `generation_run_id`.

### policy\_documents / policy\_sections

Uploaded governance policy documents (`title`, `filename`, `content_hash`, `version`, `status` CHECK `active` | `superseded`, `uploaded_by` FK principals) and their ordered sections (`document_id` FK, `section_ref` e.g. `"4.2"`, `heading`, `body`, `ord`). Mappings can cite a `policy_section_id` as their justification.

### business\_domains

Business domains of the taxonomy: `name` (unique per tenant), `description`, `inclusion`/`exclusion` guidance, `status` CHECK `draft` | `approved` | `retired`, `version`, `provenance` (default `'ModelInferred'`), optional `generation_run_id`.

### classifications

Sensitivity classifications: `name` (unique per tenant), `rank` (`INTEGER`, higher = more sensitive), `status` CHECK `draft` | `approved` | `retired`, `provenance`.

### information\_types

The core taxonomy unit, owned by a domain (`domain_id` FK `business_domains`). Columns: `definition`, `examples_json`, `counterexamples_json`, `sensitivity_rationale`, `expected_classification_id` FK `classifications`, `expected_location_note`, `status` CHECK `draft` | `approved` | `retired`, `version`, `provenance`, `generation_run_id`. Unique: `(tenant_id, name)`.

### sensitive\_categories / info\_type\_categories

Category vocabulary (`name` unique per tenant) and its many-to-many join to information types (composite PK `(information_type_id, category_id)`).

### data\_elements / info\_type\_elements

Atomic data elements (`name` unique per tenant, `aliases_json`, optional `detector_key` linking to a detector) and their many-to-many join to information types (composite PK `(information_type_id, data_element_id)`).

### regulatory\_obligations

Named obligations (`name` unique per tenant, `jurisdiction`, `source_ref`). Linked to taxonomy rows via `governance_mappings`.

### governance\_mappings

Versioned polymorphic many-to-many relationships with a review lifecycle: `subject_kind`/`subject_id` → `relation` → `object_kind`/`object_id`, plus `rationale`, optional `policy_section_id` citation, `confidence REAL`, `provenance`. `status` CHECK: `ProposedByAI` | `PolicyDerived` | `ValidatedByGRC` | `Rejected` | `Superseded` | `NeedsReview`; `reviewer_id` FK principals records who validated.

### ownership\_assignments

Who owns/stewards which domain (optionally narrowed to an information type). `kind` CHECK: `owner` | `delegate` | `steward`; `status` CHECK: `pending` | `active` | `expired` | `revoked`. Temporal validity via `valid_from`/`valid_to`; attestation via `attested_at`/`attestation_due_at`. FKs: `principal_id`, `domain_id`, `information_type_id`, `assigned_by` (all principal/taxonomy references).

### sensitivity\_labels

Purview sensitivity label catalog per Microsoft tenant: `ms_tenant_id`, `label_guid`, `name`, `priority`, `active`. `catalog_source` CHECK: `fixture` | `get_label_import` | `graph_beta` | `manual` — the schema records *how* the catalog was obtained. Unique: `(tenant_id, ms_tenant_id, label_guid)`.

### classification\_label\_mappings

Maps internal classifications to Purview labels: `direction` CHECK `classification_to_label` | `label_to_classification` | `bidirectional`; `status` CHECK `ProposedByAI` | `ValidatedByGRC` | `Rejected` | `Superseded`. Unique: `(tenant_id, classification_id, label_id)`.

## Migration 0002 — source topology and durable jobs

`0002_source_and_jobs.sql`. Connector configuration, the observed M365 file topology (scopes → containers → assets → versions/locations), deduplicated permissions, and the durable scan-job machinery ([ADR-0007](adr/ADR-0007-scan-pipeline-and-stage-caching.md), [ADR-0008](adr/ADR-0008-connector-contract-and-mock-m365.md)).

### connector\_instances

A configured connector. `connector_type` CHECK: `m365` (only value today); `mode` CHECK: `mock` | `live` — mock is the default/CI path; the live Graph connector was validated read-only against the CDX tenant ([ADR-0013](adr/ADR-0013-live-graph-connector.md), [CDX runbook](cdx-test-runbook.md)). `config_json` holds non-secret config only; `secret_ref` is an opaque reference into env/Key Vault and **never** a secret value. `status` CHECK: `configured` | `active` | `error` | `disabled`.

### source\_scopes

A scannable scope (a drive). `kind` CHECK: `sp_drive` | `onedrive`; `external_scope_id` is the provider drive id. `status` CHECK `active` | `paused` | `removed`; `coverage_state` CHECK `none` | `inventory_running` | `baseline` | `delta` | `stale` | `error` tracks scan freshness. `expected_domain_id` FK `business_domains` records the governed location expectation. Unique: `(tenant_id, connector_id, external_scope_id)`.

### source\_cursors

Provider pagination/delta tokens per scope. `cursor_kind` CHECK: `inventory_next` | `delta`; `value` is the opaque provider token stored verbatim, with `checksum` and `version`. `status` CHECK: `valid` | `invalid` | `resync_required`. Unique: `(tenant_id, scope_id, cursor_kind)`.

### source\_containers

Site/drive/folder hierarchy inside a scope: `kind` CHECK `site` | `drive` | `folder`, self-referencing `parent_id`, `path`, and an `expected_domain_id` governance expectation. Unique: `(tenant_id, scope_id, external_id)`.

### physical\_assets

The stable identity of a file across renames/moves. `kind` CHECK: `file`; `status` CHECK: `active` | `deleted` (soft delete with `deleted_at`). Unique: `(tenant_id, scope_id, external_id)`.

### asset\_versions

One row per observed content version of an asset.

| Column                    | Type         | Meaning                                                                                         |
| ------------------------- | ------------ | ----------------------------------------------------------------------------------------------- |
| `source_version`          | TEXT         | Provider **eTag** — changes on any item change                                                  |
| `content_version`         | TEXT         | Provider **cTag** — changes only when content changes                                           |
| `size_bytes`, `mime_type` | INTEGER/TEXT | Observed metadata                                                                               |
| `content_state`           | TEXT         | CHECK: `not_fetched` \| `available` \| `inaccessible` \| `unsupported` \| `partial` \| `failed` |
| `is_current`              | INTEGER      | Current-version flag; partial index `WHERE is_current = 1`                                      |

Unique: `(tenant_id, asset_id, source_version)`.

### asset\_locations

Where an asset lives over time: `container_id` FK, `name`, `path`, with temporal validity `valid_from`/`valid_to` (`valid_to IS NULL` = current; partial indexes on current rows).

### permission\_sets / permission\_grants

Deduplicated permission representation. `permission_sets` stores one row per distinct normalized grant set: `digest` (hash of normalized grants, unique per tenant), `broad_categories_json` (e.g. `["tenant_wide_group","anonymous_link"]`), `principal_count`, `has_external`, `completeness` CHECK `complete` | `partial` | `not_observable`. `permission_grants` holds the members of a set: nullable `principal_id` (or `broad_category` when the grant is a broad audience rather than a principal), `role` (read/write/owner), `inheritance` CHECK `direct` | `inherited`, `link_scope` (anonymous/organization/specific for sharing links).

### asset\_permission\_obs

Observation joining an asset(+version) to a permission set at a point in time: `acquisition_context` (default `'app_read_identity'`), `completeness` CHECK as above, `observed_at`, `is_current` flag with partial index.

### asset\_label\_obs

Observed sensitivity-label state per asset version: nullable `label_id` FK `sensitivity_labels`, `state` CHECK `labeled` | `unlabeled` | `unknown` | `unsupported` | `inaccessible`, `method` CHECK `metadata` | `extract_api` | `fixture`, `is_current` flag. Live label reads are feature-gated (`FMD_FEATURE_PURVIEW_LABEL_READ`); with the gate off the honest state is `unknown`.

### activity\_aggregates

Windowed activity per asset: `window_start`/`window_end`, `reads`/`edits`/`shares` counters, `last_activity_at`, `editor_principal_ids_json`. `source_state` CHECK: `observed` | `not_enabled` | `partial` — absence of an activity feed is recorded, not guessed.

### scan\_campaigns

A durable scan job. `kind` CHECK: `inventory` | `delta` | `rescore`; `scope_id` is NULL for rescore campaigns. `params_json` is the immutable request; `status` CHECK `pending` | `running` | `paused` | `completed` | `cancelled` | `failed`; `requested_by` FK principals. `progress_total_est` is NULL when unknown — never fabricated.

### work\_units

Leased queue items within a campaign.

| Column                                                 | Meaning                                                                          |
| ------------------------------------------------------ | -------------------------------------------------------------------------------- |
| `kind`                                                 | e.g. `inventory_page`, `process_asset`, `rescore_asset`, `execute_action`        |
| `logical_key`                                          | Idempotency key; unique `(tenant_id, campaign_id, logical_key)`                  |
| `status`                                               | CHECK: `pending` \| `leased` \| `completed` \| `failed` \| `dead` \| `cancelled` |
| `attempt` / `max_attempts`                             | Retry bookkeeping (default max 4)                                                |
| `lease_owner`, `lease_expires_at`, `last_heartbeat_at` | Lease protocol; `lease_owner` is a `scanners.id`                                 |
| `retry_class`, `last_error_category`                   | Safe error taxonomy                                                              |
| `run_after`                                            | Earliest execution time (backoff)                                                |

### stage\_attempts

Per-stage execution log: `work_unit_id` (a `work_units.id`, or a synthetic ref like `propagate:<assetId>` for decision-driven re-scores — deliberately not an FK), `asset_id`, `stage`, `cache_key`, `outcome` CHECK `computed` | `cache_hit` | `failed` | `skipped`, plus `impl_version`/`config_version` for reproducibility.

### stage\_cache

Stage result cache: presence of a row under `(tenant_id, cache_key)` (unique) means the stage output is reusable. `result_json` holds safe summaries/result references only.

### dead\_letters

Work units that exhausted retries: `work_unit_id` FK, `retry_class`, `safe_diagnostic` (redacted — no paths/names/content), `replayed_at` when re-queued.

### outbox\_events

Transactional outbox for projection/propagation events: `event_type`, `payload_json`, `partition_key`, `processed_at` (NULL = pending; partial index on unprocessed rows).

## Migration 0003 — intelligence and review

`0003_intelligence_review.sql`. Derived artifacts, fingerprints, features, detector/ensemble releases, the evidence → assertion chain ([ADR-0006](adr/ADR-0006-evidence-assertion-provenance.md)), clusters, risk, findings, and the owner review loop.

### artifacts

Derived blobs stored outside the DB. `kind` CHECK: `extracted_text_bounded` | `excerpt` | `feature_blob` | `dataset` | `report`; `path` is relative under the `FMD_ARTIFACT_DIR` object store; `content_hash`, `size_bytes`. `classification` defaults `'sensitive_derivative'`; `use_flags_json` is an allowlist — absence of a flag means the use is not permitted; `retention_class` defaults `'standard'` ([ADR-0011](adr/ADR-0011-retention-and-evidence-profile.md)).

### fingerprints / fingerprint\_bands

Content fingerprints per asset version: `representation` CHECK `raw_bytes` | `normalized_text`; `algorithm` CHECK `sha256` | `simhash64` with `algo_version`. Unique: `(tenant_id, asset_version_id, representation, algorithm, algo_version)`. `fingerprint_bands` stores SimHash band values (16 bands of 4 bits each; `band_no`, `band_value`) for near-duplicate candidate retrieval, indexed on `(tenant_id, band_no, band_value)`.

### feature\_artifacts

Extracted features per asset version: `kind` CHECK `keyphrases` | `detector_hits` | `language` | `excerpts` | `structure`; versioned by `algo_version` + `config_version` + `input_hash`. Small features inline in `value_json`; large ones referenced via `artifact_id`. `status` CHECK: `valid` | `incompatible`. Unique: `(tenant_id, asset_version_id, kind, algo_version, config_version)`.

### detector\_releases / ensemble\_releases

Versioned detector definitions (`detector_key` + `version` unique per tenant; `kind` CHECK `pattern` | `dictionary` | `keyphrase` | `context` | `near_dup` | `model_adapter`; `definition_json`; `status` `active` | `retired`) and versioned ensemble configurations (`config_json` with weights/thresholds/detector versions, `compat_feature_versions_json`, `status` `active` | `superseded`, `version` unique per tenant).

### evidence

Atomic observations that support or contradict a conclusion. Polymorphic subject: `subject_kind` CHECK `asset` | `asset_version` | `container` | `principal` + `subject_id`. `kind` examples (comment-documented): `phrase_hit`, `near_duplicate`, `label_state`, `exposure`, `editor_context`, `location_context`, `policy_citation`, `human_decision`, `keyphrase_overlap`. `direction` CHECK `supports` | `contradicts`; `weight REAL`; `value_json` bounded safe values only; optional `artifact_id`; `release_ref` names the detector/ensemble release. `access_classification` CHECK: `metadata` | `evidence_summary` | `evidence_excerpt` — the evidence-access gate uses this. Both `observed_at` and `recorded_at` are kept.

### assertions

Machine conclusions with full provenance and supersession chains.

| Column                                                           | Meaning                                                                                                                    |
| ---------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- |
| `subject_kind`/`subject_id`                                      | Polymorphic subject; `subject_kind` CHECK: `asset` \| `container` \| `principal`                                           |
| `predicate`                                                      | e.g. `is_information_type`, `belongs_to_domain`, `similar_to`, `is_overexposed`, `unexpected_location`, `is_rot_candidate` |
| `object_kind`/`object_id`/`object_value`                         | Polymorphic object                                                                                                         |
| `status`                                                         | CHECK: `candidate` \| `superseded` \| `retracted`                                                                          |
| `confidence REAL`, `provenance`, `release_ref`, `config_version` | Reproducibility                                                                                                            |
| `evidence_ids_json`, `explanation`                               | Why the assertion exists                                                                                                   |
| `confirms_/corrects_/rejects_assertion_id`                       | Self-FKs recording human-feedback lineage                                                                                  |
| `human_state`                                                    | CHECK: `none` \| `confirmed` \| `corrected` \| `rejected` \| `routed` \| `deferred`                                        |
| `valid_from`, `recorded_at`, `superseded_at`, `superseded_by`    | Temporal validity + supersession (self-FK)                                                                                 |

### clusters / cluster\_members

Asset groupings: `kind` CHECK `exact_duplicate` | `near_duplicate` | `context`, with `algorithm`/`algo_version`, optional `representative_asset_id`, `status` `active` | `superseded`. Members carry `score REAL` and `reason`; unique `(tenant_id, cluster_id, asset_id)`.

### risk\_assessments

Scored risk per subject (`subject_kind` CHECK `asset` | `container` | `domain`): `policy_version`, `components_json` (array of `{component, raw, normalized, evidenceRefs, note}`), `total_score REAL`, `severity` CHECK `low` | `moderate` | `elevated` | `high`, `coverage_note`, `is_current` flag with partial index.

### findings

Actionable issues surfaced to owners. `kind` examples: `overexposure`, `unexpected_location`, `label_gap`, `rot_candidate`, `duplicate_sprawl`. `dedup_key` unique per tenant prevents duplicate findings; `title` uses calm, precise language (no breach vocabulary). `severity` CHECK as above; `status` CHECK: `open` | `acknowledged` | `planned` | `remediating` | `resolved` | `reopened` | `suppressed`. References: `domain_id`, `subject_refs_json`, `assertion_ids_json`, `risk_id`; lifecycle times `first_seen_at`/`last_seen_at`/`resolved_at`.

### review\_candidates

The owner review queue: one row per assertion routed for review (`assertion_id` unique per tenant), scoped by `domain_id`, pointing at `asset_id`, ranked by `priority REAL` and `uncertainty REAL` (with `reprioritized_reason`). `status` CHECK: `pending` | `decided` | `deferred` | `escalated` | `stale`.

### review\_decisions

Owner decisions on candidates. `decision` CHECK: `mine` | `mine_different_type` | `another_domain` | `mine_not_sensitive` | `not_a_match` | `need_more_evidence`; optional `corrected_information_type_id` and `routed_domain_id`; `batch_id`; `training_eligible` flag; `resulting_assertion_id` links to the assertion the decision produced.

### dataset\_candidates

Decisions promoted toward training data: `decision_id` FK, `kind` CHECK `positive` | `counterexample`, `status` CHECK `pending` | `approved` | `rejected`. Consumed by migration 0008.

## Migration 0004 — analyst, actions, audit

`0004_analyst_actions_audit.sql`. Analyst query runs, the remediation action state machine ([ADR-0010](adr/ADR-0010-remediation-state-machine.md)), and the tamper-evident audit log.

### analyst\_queries

One row per analyst question ([ADR-0009](adr/ADR-0009-analyst-typed-plan.md)): `question`, `planner` CHECK `deterministic` | `ai`, `plan_json`, `validation_state` CHECK `valid` | `rejected` | `narrowed`, `scope_json` (server-side injected tenant/domain scope), `executed_desc_json`, `result_row_refs_json`, `coverage_json`, `model_ref`, `answer_text`, `status` CHECK `answered` | `rejected` | `error`.

### action\_requests

The remediation state machine. `kind` CHECK: `internal_task` | `purview_label_set` | `purview_label_remove`. `status` CHECK: `draft` | `validated` | `awaiting_approval` | `approved` | `executing` | `verifying` | `succeeded` | `partially_succeeded` | `failed` | `cancelled` | `drifted` | `blocked`. Targets: `finding_id`, `domain_id`, `target_asset_id`, and `target_snapshot_version_id` (the asset version at preview time — drift is detected against it). `current_state_json` / `desired_state_json` capture the planned mutation; `approval_required` defaults 1; `idempotency_key` unique per tenant. Live Purview label writes are feature-gated (`FMD_FEATURE_PURVIEW_LABEL_WRITE`) and run under a separate remediation identity; the mock path is the validated default.

### action\_approvals

Approval decisions: `approver_id` FK principals, `decision` CHECK `approve` | `reject` | `request_changes`, `reason`.

### action\_attempts

Execution attempts: `attempt_no` (unique `(tenant_id, action_id, attempt_no)`), `executor_identity` (which workload identity executed — read vs remediation), `idempotency_key`, `status` CHECK `running` | `succeeded` | `failed` | `skipped_duplicate` (duplicate delivery yields one mutation), `source_correlation`, `error_category`, `result_json`.

### action\_verifications

Post-execution source verification: `observed_state_json` and `outcome` CHECK `verified` | `mismatch` | `failed` | `unsupported`.

### audit\_events

Append-only audit with a per-tenant hash chain for tamper evidence: `seq` unique per tenant, `prev_hash` + `hash` chain each event to its predecessor. `actor_id` is NULL for system actions. `category` values (comment-documented): `auth`, `config`, `governance`, `scan`, `evidence_access`, `review`, `analyst`, `action`, `model`, `operations`. `outcome` CHECK: `success` | `denied` | `failure`; `policy_decision`; `details_safe_json` contains allowlisted fields only; `correlation_id` ties events to requests.

## Migration 0005 — rebuildable projections

`0005_projections.sql`. Derived read models for graph and search. **Everything in this migration is rebuildable from base tables**; treat it as a cache with indexes, not a source of truth.

### graph\_nodes / graph\_edges

Typed property graph. Nodes: `kind` (documented vocabulary: `asset`, `container`, `domain`, `information_type`, `principal`, `permission_set`, `cluster`, `finding`, `label`), `ref_id` (id of the underlying record; unique `(tenant_id, kind, ref_id)`), `label_safe` (display text safe for the owner evidence level), `props_json`. Edges: `from_node_id`/`to_node_id` FK `graph_nodes`, typed `kind` (no generic `RELATED_TO`), and a **required** human-readable `reason`. Unique: `(tenant_id, from_node_id, to_node_id, kind)`.

### search\_documents

Search projection metadata and the security facet: one row per asset (`(tenant_id, asset_id)` unique) carrying `domain_id` (**domain scoping for search authorization happens here**), `information_type_id`, `container_path`, `facets_json`, `freshness_at`.

### search\_fts (FTS5 virtual table)

```sql theme={null}
CREATE VIRTUAL TABLE search_fts USING fts5(
  doc_id UNINDEXED,
  title,
  keyphrases,
  excerpt,
  tokenize = 'porter unicode61'
);
```

Content index for search. It has no `tenant_id` column of its own — queries must join `doc_id` back to `search_documents` where tenant and domain scoping are enforced. Rebuildable like the rest of this migration.

### projection\_watermarks

Rebuild bookkeeping: composite PK `(tenant_id, projection)` where `projection` is `graph` or `search`, with `watermark_at` and `version`.

## Migration 0006 — scanner fleet

`0006_scanner_fleet.sql`. Distributed scanner workers.

### scanners

**Deployment-level infrastructure — the one table without `tenant_id`.** A registered worker that pulls work from the shared queue via outbound polling. `id` is the workload identity and doubles as `work_units.lease_owner`. Columns: `display_name`, `host` (pseudonymous host/process id), `capabilities_json` (connector types handled, e.g. `["m365"]`), `version`, `status` CHECK `active` | `draining` | `stale` | `offline`, `registered_at`, `last_heartbeat_at` (indexed; abandoned work is reclaimed on lease expiry), `processed_count`, `failed_count`.

## Migration 0007 — change notifications

`0007_change_notifications.sql`. Graph change-notification subscriptions and received notifications. Notifications are treated as *hints* to reconcile via the delta feed, never as authoritative content. Built and validated with the dev simulator (a simulated notification drove a real reconcile); live Graph subscriptions additionally require a public HTTPS webhook URL and the `FMD_FEATURE_CHANGE_NOTIFICATIONS` flag.

### change\_subscriptions

Subscription lifecycle: `scope_id`/`connector_id` FKs, `resource` (e.g. `/drives/{id}/root`), `change_type` (default `'updated'`), `external_subscription_id` (Graph subscription id, live mode only). `client_state_hash` stores only the sha256 of the per-subscription clientState — the raw value is never stored; the receiver recomputes the expected value with an HMAC. `created_by` FK principals records the standing intent (reconcile campaigns run as this principal). `mode` CHECK `mock` | `live`; `status` CHECK `pending` | `active` | `expiring` | `expired` | `removed` | `error`; `expires_at`, `last_renewed_at`, `last_error` (safe category only). Unique: `(tenant_id, resource, mode)`.

### change\_notifications

Received notifications: `subscription_id` FK, `resource`, `change_type`, freshness points `source_event_at` (provider-reported) and `received_at`, reconciliation linkage `reconcile_campaign_id` FK `scan_campaigns` + `reconciled_at`, `dedup_key` (resource+changeType within a short window). `outcome` CHECK: `received` | `deduplicated` | `reconcile_enqueued` | `reconcile_skipped` | `reconciled` | `rejected`; `reject_reason` is a safe category only. `source` CHECK: `live` | `simulated` — simulated events are labeled as such.

## Migration 0008 — model governance

`0008_model_governance.sql`. Turns owner review feedback into curated, split, consented datasets and gates ensemble/detector releases behind evaluation plus human approval.

### dataset\_snapshots

Curated dataset versions: `name` unique per tenant, `consent_scope` CHECK `tenant_internal` (default) | `vendor_shareable` — sharing beyond the tenant requires an explicit customer choice. `split_strategy` (default `'cluster_aware'`), counts (`positive_count`, `counterexample_count`, `train_count`, `eval_count`), `created_by` FK principals.

### dataset\_members

Snapshot membership: `candidate_id` FK `dataset_candidates` (unique per snapshot), denormalized `asset_id` and `information_type_id`, `label` CHECK `positive` | `counterexample`, `split` CHECK `train` | `eval`, `cluster_key` (cluster id or singleton asset id — makes the split leakage-safe), `model_confidence` at snapshot time.

### model\_releases

Proposed releases: `kind` CHECK `ensemble` | `detector` (with `target_key` when detector), proposed `version` + `config_json`, `base_version` + `base_config_json` (rollback snapshot), `dataset_snapshot_id` FK. `status` CHECK: `proposed` | `evaluated` | `approved` | `promoted` | `rejected` | `rolled_back`. Separation of duties: `decided_by` must differ from `proposed_by` (enforced in application code; both FK principals). `ensemble_release_id` FK `ensemble_releases` is set on promote.

### release\_evaluations

Evaluation results per release and variant (`variant` CHECK `candidate` | `current`; unique `(release_id, variant)`): confusion counts (`true_pos`, `false_pos`, `true_neg`, `false_neg`), `precision`, `recall`, `f1`, `threshold`.

## Migration 0009 — embeddings

`0009_embeddings.sql`. Semantic embeddings with an approximate (LSH-bucketed) candidate index. Embeddings are a retained feature: computed once per asset version and re-scored without any source I/O.

### asset\_embeddings

One embedding per asset version and model: `model` (e.g. `fmd-hash-256-v1`), `dim` (vectors stored inline as JSON for dim ≤ 256), `vector_json` (L2-normalized float array), `signature` (LSH signature, hex), `content_version`. Unique: `(tenant_id, asset_version_id, model)`.

### embedding\_buckets

One row per (embedding, band bucket) for candidate retrieval by shared band: `embedding_id` FK, denormalized `asset_id`, `model`, `band` (`"bandIndex:value"`), indexed on `(tenant_id, model, band)`.

## Migration 0010 — Teams connector

`0010_teams.sql`. Team/channel topology, channel messages, and file canonicalization. Built and mock-validated; the live path is feature-gated behind `FMD_FEATURE_TEAMS_CONNECTOR`. A channel's files live in the team's SharePoint site, so attachments are canonicalized to the backing driveItem (already inventoried by the SharePoint connector) rather than re-ingested as duplicate assets.

### teams\_teams

Observed teams per connector: `external_team_id` (unique per `(tenant_id, connector_id)`), `display_name`, `visibility` CHECK `private` | `public` | `unknown`.

### teams\_channels

Channels: `team_id` FK, `external_channel_id` (unique per team), `membership_type` CHECK `standard` | `private` | `shared` | `unknown`. Backing storage linkage: `backing_scope_external_id` (SharePoint drive id), `backing_folder_path`, and `backing_scope_id` FK `source_scopes` when the drive is known locally; `expected_domain_id` FK `business_domains`.

### teams\_messages

Channel messages — a distinct modality with its own table: `external_message_id` (unique per channel), `parent_message_id` (reply threading, external id), sender fields, `created_at_source`, `body_preview` (bounded, redaction-safe preview only), `body_hash`, `detector_hits_json` (detector keys only, no raw content), `has_attachments`.

### teams\_attachment\_refs

A message attachment pointing at a SharePoint driveItem: `external_drive_id`/`external_item_id`, `file_name`, `canonical_asset_id` FK `physical_assets` (the resolved SharePoint asset — a duplicate asset is never created), `resolution` CHECK `canonicalized` | `pending` | `external`. Unique: `(tenant_id, message_id, external_item_id)`.

## Migration 0011 — Exchange connector

`0011_exchange.sql`. Selected mailboxes, per-folder message delta, and attachment↔SharePoint matching. Built and mock-validated; the live path is feature-gated behind `FMD_FEATURE_EXCHANGE_CONNECTOR`. Key distinction from Teams: an email attachment is a *copy* of a file, not the SharePoint item itself, so a match is recorded as a **similarity** relationship — never merged into one identity.

### exchange\_mailboxes

Selected mailboxes per connector: `external_mailbox_id` (UPN/user id; unique per `(tenant_id, connector_id)`), `display_name`. `access_scope` CHECK: `application_access_policy` (default — least privilege via an application access policy, not tenant-wide Mail.Read) | `tenant_wide` | `fixture`.

### exchange\_folders

Mail folders with per-folder delta state: `mailbox_id` FK, `external_folder_id` (unique per mailbox), `delta_cursor` (opaque per-folder delta token).

### exchange\_messages

Messages: `folder_id` FK, `external_message_id` (unique per folder), `subject`, sender fields, `received_at_source`, `body_preview` (bounded, redaction-safe), `body_hash`, `detector_hits_json`, `has_attachments`, `importance` (default `'normal'`).

### exchange\_attachments

Attachments and their similarity match: `external_attachment_id` (unique per message), `file_name`, `size_bytes`, `content_type`, `content_hash` (sha256 of raw bytes when available), `normalized_text_hash` (sha256 of normalized extracted text, indexed for matching). `similar_asset_id` FK `physical_assets` records a similarity to a SharePoint asset (a distinct copy, not the same identity); `match_method` CHECK: `identical_content` | `identical_text` | `near_duplicate` | `filename_size` | `none`; `match_confidence REAL`.

## Migration 0012 — control plane

`0012_control_plane.sql`. SaaS control-plane state. The control plane handles licensing and fleet health only — never customer content or sensitive evidence.

### control\_plane\_state

One row per tenant (PK `tenant_id`): `telemetry_enabled` (default 1; customer-controlled, opt-outable at any time), `last_entitlement_json` (cached last-valid entitlement for the offline grace window; non-secret), `last_verified_at`, `updated_at`.

## Entity-relationship overview

The diagram shows the 17 most central tables. Every table also references `tenants` via `tenant_id` (edges omitted for readability), and `evidence`/`assertions` use polymorphic `subject_kind`/`subject_id` references rather than declared FKs — those are drawn as logical links labeled "subject".

```mermaid theme={null}
erDiagram
    tenants ||--o{ principals : "tenant_id"
    tenants ||--o{ business_domains : "tenant_id"
    tenants ||--o{ source_scopes : "tenant_id"

    business_domains ||--o{ information_types : "domain_id"

    source_scopes ||--o{ physical_assets : "scope_id"
    physical_assets ||--o{ asset_versions : "asset_id"
    physical_assets ||--o{ asset_permission_obs : "asset_id"
    permission_sets ||--o{ asset_permission_obs : "permission_set_id"

    physical_assets ||--o{ evidence : "subject (polymorphic)"
    physical_assets ||--o{ assertions : "subject (polymorphic)"

    assertions ||--o| review_candidates : "assertion_id"
    business_domains ||--o{ review_candidates : "domain_id"
    physical_assets ||--o{ review_candidates : "asset_id"
    review_candidates ||--o{ review_decisions : "candidate_id"
    principals ||--o{ review_decisions : "reviewer_id"

    business_domains ||--o{ findings : "domain_id"
    findings ||--o{ action_requests : "finding_id"
    physical_assets ||--o{ action_requests : "target_asset_id"
    principals ||--o{ action_requests : "requested_by"

    source_scopes ||--o{ scan_campaigns : "scope_id (NULL for rescore)"
    scan_campaigns ||--o{ work_units : "campaign_id"
```

Related reading: [architecture](architecture.md) (module layout over this schema), [threat model](threat-model.md) (what the safe-diagnostic/redaction columns defend against), [capacity model](capacity-model.md) (row-count expectations), [permissions manifest](permissions-manifest.md) (roles referenced by `role_assignments`), and [ADR-0003](adr/ADR-0003-persistence-single-sqlite-behind-role-interfaces.md) (why a single SQLite database behind role interfaces).