query-orchestration/docs/ai.generated/11-custom-metadata-guide.md

# Custom Metadata Guide (Mutable Metadata v4)

## Overview

The custom metadata system lets platform operators define client-scoped JSON Schema definitions and allows end users to attach structured, validated metadata blobs to individual documents. This is distinct from — and mutually exclusive with — the legacy field extraction system (`documentFieldExtractions`).

### When to Use Custom Metadata vs Legacy Field Extractions

| | Custom Metadata | Legacy Field Extractions |
|---|---|---|
| Schema definition | Per-client JSON Schema (flexible) | Fixed set of ~19 single-value + 112 array fields |
| Schema evolution | Via `POST .../versions` (preserved history) | Schema is static |
| Validation | JSON Schema draft 2020-12 | Fixed column types |
| Who configures | `super_admin` | Platform deployment |
| Who writes | `client_user`, `user_admin`, `super_admin` | Any authenticated user |

A document may use one system or the other, never both. The mutual exclusivity invariant is enforced at three layers: service-layer row locks, Trigger 1 (`trg_prevent_schema_reassignment`), and Trigger 2 (`trg_prevent_legacy_extraction_on_custom_document`). See the [Data Architecture doc](04-data-architecture.md) for trigger details.

---

## Authorization Matrix

| Role | `SuperAdminSchemaService` endpoints | `CustomMetadataService` endpoints |
|---|---|---|
| `super_admin` | Full (GET, POST, PATCH, DELETE) | Full (GET, POST) |
| `user_admin` | **No access (403 on all)** | Full (GET, POST) |
| `client_user` | **No access (403 on all)** | Full (GET, POST) |
| `auditor` | GET only (read-only) | GET only (no POST) |

**Important**: `user_admin` does NOT have access to any `/super-admin/custom-schemas` or `/super-admin/documents/*/schema` endpoints. The `/super-admin/` path prefix is intentionally restricted to `super_admin` only. This is a deliberate difference from the legacy `/admin/` prefix, which did grant some access to `user_admin`.

---

## Mutual Exclusivity

A document may have either legacy field extractions OR custom metadata — never both.

- Attempting to assign a custom schema to a document that already has legacy field extraction rows returns `409 Conflict`.
- Attempting to write legacy field extractions to a document that already has a custom schema assigned returns `409 Conflict`.
- The database enforces this via two triggers; the service layer reinforces it with explicit `FOR UPDATE` parent-row locks on the `documents` row before any DML.

---

## Platform Operator Workflow (super_admin)

This section describes how a platform operator sets up custom metadata for a client.

### Step 1 — Define the schema

```http
POST /super-admin/custom-schemas
Content-Type: application/json

{
  "clientId": "AAA",
  "name": "aircraft-engineering",
  "description": "Schema for aircraft inspection reports",
  "schemaDef": {
    "type": "object",
    "additionalProperties": false,
    "properties": {
      "aircraft_type": { "type": "string", "maxLength": 100 },
      "flight_hours": { "type": "number", "minimum": 0 },
      "inspection_date": { "type": "string", "format": "date" },
      "critical_findings": {
        "type": "array",
        "items": { "type": "string" }
      }
    },
    "required": ["aircraft_type", "inspection_date"]
  }
}
```

The response includes `id` (the schema UUID), `version: 1`, and `status: "active"`.

### Step 2 — Assign the schema to a document

```http
PATCH /super-admin/documents/{documentId}/schema
Content-Type: application/json

{
  "schemaId": "019580df-ef65-7676-8de9-94435a93337a"
}
```

The schema and document must belong to the same client. The document must not already have legacy field extractions or existing custom metadata.

### Step 3 — Users write metadata

Once a schema is assigned, users with `client_user`, `user_admin`, or `super_admin` roles can write metadata (see end-user workflow below).

---

## End-User Workflow (client_user)

### Write metadata

```http
POST /custom-metadata
Content-Type: application/json

{
  "documentId": "019580df-b3f8-7348-9ab1-1e55b3f18ed7",
  "metadata": {
    "aircraft_type": "Boeing 737",
    "inspection_date": "2026-04-01",
    "flight_hours": 12450.5,
    "critical_findings": ["hydraulic leak in bay 3"]
  }
}
```

Each call appends a new version. The metadata is validated against the schema currently bound to the document. Returns `400` with validation details if the payload does not conform.

### Read current metadata

```http
GET /custom-metadata?documentId=019580df-b3f8-7348-9ab1-1e55b3f18ed7
```

Returns the latest version of the metadata along with `schemaId`, `schemaName`, and `schemaVersion` from the bound schema.

### Read a specific version

```http
GET /custom-metadata/version?documentId=019580df-b3f8-7348-9ab1-1e55b3f18ed7&version=2
```

`version` must be >= 1. Returns `404` if that version does not exist.

### List version history

```http
GET /custom-metadata/history?documentId=019580df-b3f8-7348-9ab1-1e55b3f18ed7&limit=20&offset=0
```

Returns version summaries (`version`, `createdAt`, `createdBy`) in descending order. Does not return the full metadata payloads. `limit` must be 1–200.

---

## Schema Definition Requirements

Custom schema definitions must conform to the following rules. Submissions that violate any rule are rejected with `400`.

1. **Must be valid JSON** — malformed JSON is rejected immediately.
2. **Must be valid JSON Schema (draft 2020-12)** — the definition is meta-validated against the JSON Schema draft 2020-12 meta-schema.
3. **Root `type` must be `"object"`** — schemas with a different root type (e.g. `"array"`, `"string"`) are rejected.
4. **`additionalProperties` must be explicitly declared** — the absence of this key is rejected. The author must make a deliberate choice: `false` (strict, no extra keys) or `true` (open, any extra keys allowed).
5. **Maximum size is 64 KB** — definitions larger than 65,536 bytes are rejected.

### Sample Schema — Strict (no additional properties)

```json
{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "aircraft_type": { "type": "string", "maxLength": 100 },
    "flight_hours": { "type": "number", "minimum": 0 },
    "inspection_date": { "type": "string", "format": "date" },
    "critical_findings": {
      "type": "array",
      "items": { "type": "string" }
    }
  },
  "required": ["aircraft_type", "inspection_date"]
}
```

### Sample Schema — Open (additional properties allowed)

```json
{
  "type": "object",
  "additionalProperties": true,
  "properties": {
    "document_class": { "type": "string" },
    "reviewed_by": { "type": "string" }
  }
}
```

---

## Metadata Write Requirements

- Payload must be a JSON object (JSONB).
- Maximum size is 1 MB (1,048,576 bytes).
- Payload is validated against the schema currently bound to the document at write time.
- If the document has no schema assigned, the write returns `400`.
- `createdBy` is not accepted in the request body — actor identity comes from the JWT `sub` claim.

---

## Schema Versioning

A schema name within a client forms a lineage. Each version in the lineage is an independent row with a sequential `version` integer.

### Creating a new version

```http
POST /super-admin/custom-schemas/{currentActiveSchemaId}/versions
Content-Type: application/json

{
  "clientId": "AAA",
  "name": "aircraft-engineering",
  "description": "v2: added optional maintenance_notes field",
  "schemaDef": {
    "type": "object",
    "additionalProperties": false,
    "properties": {
      "aircraft_type": { "type": "string", "maxLength": 100 },
      "flight_hours": { "type": "number", "minimum": 0 },
      "inspection_date": { "type": "string", "format": "date" },
      "critical_findings": { "type": "array", "items": { "type": "string" } },
      "maintenance_notes": { "type": "string" }
    },
    "required": ["aircraft_type", "inspection_date"]
  }
}
```

On success:
- A new row is inserted with `version = 2` and `status = "active"`.
- The parent row (`schemaId` in the path) is updated to `status = "superseded"`.
- Both changes are applied atomically in a single transaction.

The HTTP method is `POST`, not `PUT`. Each call creates a new row with a new ID. The old version is preserved permanently and remains queryable.

### Status values

| Status | Meaning |
|---|---|
| `active` | The current version for this schema name. Documents can be assigned to it. Only one active version per `(client_id, name)` lineage at any time. |
| `superseded` | A previous version. Still queryable. Documents bound to it retain the binding. Cannot be assigned to new documents. |
| `retired` | Explicitly removed via DELETE. No documents are bound (DELETE returns 409 if any are). |

### Passing a non-active parent to POST .../versions

If the `schemaId` in the path points to a `superseded` or `retired` row, the request returns `409 Conflict`. The active version for the lineage must be used as the path parameter.

---

## Schema Upgrade Runbook (Reset-Metadata Flow)

Once a document has custom metadata written to it, its `custom_schema_id` is frozen by Trigger 1. To migrate a document to a newer schema version:

**Warning**: This process is irreversible. All metadata history for the document is permanently deleted.

### Step 1 — Create the new schema version (if not already done)

```http
POST /super-admin/custom-schemas/{currentActiveSchemaId}/versions
```

Note the new schema's `id`.

### Step 2 — Reset the document

```http
POST /super-admin/documents/{documentId}/reset-metadata
```

This single atomic operation:
- Deletes all rows in `document_custom_metadata` for the document.
- Sets `documents.custom_schema_id` to `NULL`.
- Returns a summary including `metadataVersionsDeleted` and the `previousSchemaId`.

The operation is idempotent. If the document has no schema and no metadata, it returns `200` with `metadataVersionsDeleted: 0`.

### Step 3 — Assign the new schema version

```http
PATCH /super-admin/documents/{documentId}/schema
Content-Type: application/json

{
  "schemaId": "<new-schema-version-id>"
}
```

### Step 4 — Write new metadata

```http
POST /custom-metadata
Content-Type: application/json

{
  "documentId": "{documentId}",
  "metadata": { ... }
}
```

---

## Cascade Deletion Behavior

Custom metadata and schema data are cleaned up automatically via database FK cascades. No application-level cleanup steps are required.

- `DELETE /document/{id}` (via `DeleteDocumentCascade`): deletes all `document_custom_metadata` rows for the document via `ON DELETE CASCADE` on the FK from `document_custom_metadata.document_id` to `documents.id`.
- `DELETE /client/{id}` (via `client.HardDelete`): deletes the client, which cascades to `client_metadata_schemas` (FK with `ON DELETE CASCADE`), then to `documents`, and then to `document_custom_metadata`.

No additional steps are needed in the delete code paths for this feature.

---

## Actor Fields

`createdBy` and `resetBy` fields in all request bodies are ignored — they are absent from the v4 request schemas. Actor identity is always derived from the JWT `sub` claim (an opaque Cognito subject UUID). Response fields that carry the actor identity are plain strings, not email-formatted values.

---

## Permit.io Deployment Note

Before deploying the `SuperAdminSchemaService` or `CustomMetadataService` controllers, the Permit.io resource and role configuration must be applied. Run the setup tool for each environment:

```bash
# Development
cmd/auth_related/permit.setup/run.tool.dev.sh

# UAT
cmd/auth_related/permit.setup/run.tool.uat.sh

# Production (requires explicit approval)
cmd/auth_related/permit.setup/run.tool.prod.sh
```

The setup tool applies the `super-admin` and `custom-metadata` resources and the role grants defined in `cmd/auth_related/permit.setup/permit_policies.yaml`. Without this step, all requests to the new endpoints will return `403` regardless of the user's Cognito role.