Schema Reference

This page provides the complete DDL (Data Definition Language) for all seven tables in the Auth Bridge database. Each table definition is accompanied by detailed explanations of its columns, constraints, indexes, and the role of encryption or hashing on specific columns. The SQL shown here mirrors the SqlDelight .sq schema files that serve as the canonical source of truth for the database.

All tables use TEXT for primary keys, storing UUIDs as strings. Timestamps are stored as ISO-8601 formatted TEXT values rather than native PostgreSQL timestamp types, ensuring consistent serialization across all layers of the application. Columns that hold cryptographic material are annotated with comments indicating which key is used for the HMAC or encryption operation.

identity_match

The identity_match table is the primary lookup index for the identity matching system. When a wallet holder presents a verifiable credential, the system computes an HMAC-SHA256 hash of the relevant identifier and queries this table to find whether a matching internal identity already exists.

CREATE TABLE IF NOT EXISTS identity_match (
    id              TEXT NOT NULL PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    identifier_hash TEXT NOT NULL,       -- HMAC-SHA256 (Key A or B)
    identifier_type TEXT NOT NULL,       -- KEY, SUBJECT_ID, EMAIL, DID, CLAIM_TUPLE
    internal_identity_id TEXT NOT NULL,
    hash_key_version INTEGER NOT NULL DEFAULT 1,
    metadata_json   TEXT,
    created_at      TEXT NOT NULL,
    updated_at      TEXT NOT NULL,
    last_used_at    TEXT NOT NULL,
    deleted_at      TEXT,
    deletion_reason TEXT,
    UNIQUE(tenant_id, identifier_hash, identifier_type)
);

CREATE INDEX IF NOT EXISTS idx_match_identity
    ON identity_match(tenant_id, internal_identity_id);

Column Details

Column	Type	Description
`id`	TEXT, PK	UUID primary key for the match record.
`tenant_id`	TEXT, NOT NULL	Tenant identifier for multi-tenant isolation. Every query filters on this column.
`identifier_hash`	TEXT, NOT NULL	HMAC-SHA256 hash of the external identifier. Uses Key A for wallet/holder identifiers or Key B for institution identifiers. The original identifier value is never stored.
`identifier_type`	TEXT, NOT NULL	Discriminator indicating the type of identifier that was hashed. Valid values include `KEY` (wallet key), `SUBJECT_ID` (OIDC subject), `EMAIL`, `DID` (decentralized identifier), and `CLAIM_TUPLE` (composite claim hash).
`internal_identity_id`	TEXT, NOT NULL	The system's internal UUID for the identity. Multiple match records can point to the same internal identity, representing different identifiers for the same person.
`hash_key_version`	INTEGER, NOT NULL	Version of the HMAC key used to compute `identifier_hash`. Incremented during key rotation so the system knows which key version to use for verification. Defaults to 1.
`metadata_json`	TEXT, nullable	Optional JSON metadata associated with the match, such as the credential type or presentation context.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of when the match record was first created.
`updated_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent update to this record.
`last_used_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent successful lookup against this record. Updated on every access. Used for inactivity detection and GDPR retention.
`deleted_at`	TEXT, nullable	ISO-8601 timestamp of soft deletion. When set, the record is considered logically deleted but remains in the database for the retention period.
`deletion_reason`	TEXT, nullable	Human-readable reason for the soft deletion (e.g., `INACTIVE`, `GDPR_ERASURE`, `ADMIN_REQUEST`).

Unique Constraint

The composite unique constraint on (tenant_id, identifier_hash, identifier_type) ensures that within a single tenant, the same hashed identifier of the same type can only map to one match record. This prevents duplicate entries and enforces the one-to-one relationship between an external identifier and its internal identity mapping.

Index: idx_match_identity

The index on (tenant_id, internal_identity_id) supports efficient reverse lookups: given an internal identity ID, find all match records (and thus all known identifiers) associated with that identity. This is essential for GDPR erasure operations, which must locate and delete all records for a given identity.

Soft Delete

The deleted_at and deletion_reason columns implement a soft delete pattern. When a record is soft-deleted, it is no longer returned by standard lookup queries, but it remains in the database for a configurable retention period (default 30 days). After the retention period expires, a background job performs a hard delete, permanently removing the record. This two-phase approach provides a safety window during which accidental deletions can be recovered.

identity_link_binding

The identity_link_binding table records the binding between a wallet holder and an institutional identity established during the reconciliation process. Each binding represents a verified link between a holder's wallet credential and their identity at a specific institution.

CREATE TABLE IF NOT EXISTS identity_link_binding (
    id                                    TEXT NOT NULL PRIMARY KEY,
    tenant_id                             TEXT NOT NULL,
    match_id                              TEXT NOT NULL REFERENCES identity_match(id),
    holder_identifier_hash                TEXT NOT NULL,       -- HMAC (Key A)
    holder_hash_key_version               INTEGER NOT NULL DEFAULT 1,
    institution_identifier_hash           TEXT,                -- HMAC (Key B)
    institution_hash_key_version          INTEGER,
    encrypted_institution_id              TEXT,                -- AES-256-GCM (Key C)
    encrypted_institution_id_key_version  INTEGER,
    persisted_attributes_envelope         TEXT,                -- AES-256-GCM (Key C)
    provider_id                           TEXT,
    institution_id_label                  TEXT,
    assurance_summary                     TEXT,                -- JSON
    canonical_schema_version              TEXT,
    material_profile_version              TEXT,
    selector_rule_version                 TEXT,
    material_fingerprints                 TEXT,
    created_at                            TEXT NOT NULL,
    updated_at                            TEXT NOT NULL,
    last_used_at                          TEXT NOT NULL,
    reconcile_time                        TEXT
);

CREATE INDEX IF NOT EXISTS idx_binding_match
    ON identity_link_binding(tenant_id, match_id);
CREATE INDEX IF NOT EXISTS idx_binding_holder
    ON identity_link_binding(tenant_id, holder_identifier_hash);

Column Details

Column	Type	Description
`id`	TEXT, PK	UUID primary key for the binding record.
`tenant_id`	TEXT, NOT NULL	Tenant identifier for multi-tenant isolation.
`match_id`	TEXT, FK	Foreign key referencing `identity_match(id)`. Links this binding to the parent match record.
`holder_identifier_hash`	TEXT, NOT NULL	HMAC-SHA256 hash of the wallet holder's identifier, computed using Key A. Used for looking up all bindings belonging to a specific holder.
`holder_hash_key_version`	INTEGER, NOT NULL	Version of Key A used for the holder hash. Defaults to 1.
`institution_identifier_hash`	TEXT, nullable	HMAC-SHA256 hash of the institution-side identifier (e.g., SURF sub), computed using Key B. Nullable because the binding may exist before reconciliation completes.
`institution_hash_key_version`	INTEGER, nullable	Version of Key B used for the institution hash.
`encrypted_institution_id`	TEXT, nullable	The institution identifier encrypted with AES-256-GCM using Key C. This allows the system to recover the plaintext institution ID when needed for token issuance, without storing it in the clear.
`encrypted_institution_id_key_version`	INTEGER, nullable	Version of Key C used for the encryption.
`persisted_attributes_envelope`	TEXT, nullable	AES-256-GCM encrypted envelope (Key C) containing persisted identity attributes. Only attributes with `persist: true` in the canonical attribute rules are included.
`provider_id`	TEXT, nullable	Identifier of the reconciliation provider (e.g., SURF) that was used to establish this binding.
`institution_id_label`	TEXT, nullable	Human-readable label for the institution identifier, such as `eduperson_principal_name` or `sub`.
`assurance_summary`	TEXT, nullable	JSON object summarizing the assurance level of the binding, including ACR and AMR values from the reconciliation provider.
`canonical_schema_version`	TEXT, nullable	Version of the canonical attribute schema that was in effect when this binding was created or last updated.
`material_profile_version`	TEXT, nullable	Version of the material profile configuration used during reconciliation.
`selector_rule_version`	TEXT, nullable	Version of the selector rule configuration used during reconciliation.
`material_fingerprints`	TEXT, nullable	Fingerprints of the material (attributes) used to establish the binding. Enables detection of whether the binding needs to be refreshed.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of binding creation.
`updated_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent update.
`last_used_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent access. Used for inactivity detection.
`reconcile_time`	TEXT, nullable	ISO-8601 timestamp of when the reconciliation that established this binding was completed.

Index: idx_binding_match

The index on (tenant_id, match_id) supports efficient lookups of all bindings associated with a particular identity match. This is the primary access pattern when the system needs to retrieve a holder's institutional bindings after an identity match has been found.

Index: idx_binding_holder

The index on (tenant_id, holder_identifier_hash) enables direct lookup of all bindings for a specific wallet holder without first going through the identity_match table. This is used when the holder hash is known directly from a wallet presentation.

Domain-Separated Keys

This table is the clearest illustration of the domain-separated key architecture. The holder_identifier_hash is computed with Key A, the institution_identifier_hash with Key B, and the encrypted_institution_id and persisted_attributes_envelope are encrypted with Key C. Compromising any single key does not expose the data protected by the other keys.

reconciliation_session

The reconciliation_session table tracks the lifecycle of each identity verification (IDV) reconciliation flow. A reconciliation session is created when a wallet holder initiates the process of linking their wallet identity to an institutional identity through an upstream OIDC provider such as SURF.

CREATE TABLE IF NOT EXISTS reconciliation_session (
    id                              TEXT NOT NULL PRIMARY KEY,
    tenant_id                       TEXT NOT NULL,
    status                          TEXT NOT NULL,
    identifier_hash                 TEXT,
    identifier_type                 TEXT,
    provider_id                     TEXT NOT NULL,
    authorization_url               TEXT,
    state                           TEXT,
    nonce                           TEXT,
    code_verifier                   TEXT,
    redirect_uri                    TEXT,
    token_endpoint                  TEXT,
    encrypted_identity              TEXT,
    encrypted_identity_key_version  INTEGER,
    error_message                   TEXT,
    created_at                      TEXT NOT NULL,
    expires_at                      TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_recon_session_state
    ON reconciliation_session(state) WHERE state IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_recon_session_status
    ON reconciliation_session(tenant_id, status);

Column Details

Column	Type	Description
`id`	TEXT, PK	UUID primary key for the session.
`tenant_id`	TEXT, NOT NULL	Tenant identifier for multi-tenant isolation.
`status`	TEXT, NOT NULL	Current state of the session. Values include `PENDING`, `AUTHORIZED`, `COMPLETED`, `FAILED`, and `EXPIRED`.
`identifier_hash`	TEXT, nullable	HMAC hash of the identifier that initiated the reconciliation. Set after the initial identity match is found.
`identifier_type`	TEXT, nullable	Type of the identifier (e.g., `KEY`, `SUBJECT_ID`).
`provider_id`	TEXT, NOT NULL	Identifier of the reconciliation provider being used for this session.
`authorization_url`	TEXT, nullable	The full OIDC authorization URL that the user is redirected to. Stored so it can be logged or debugged.
`state`	TEXT, nullable	OIDC state parameter, a random value used to prevent CSRF attacks. Must be unique across all active sessions.
`nonce`	TEXT, nullable	OIDC nonce parameter, bound to the ID token to prevent replay attacks.
`code_verifier`	TEXT, nullable	PKCE code verifier for the authorization code exchange. Stored temporarily until the token exchange completes.
`redirect_uri`	TEXT, nullable	The redirect URI registered for this session's OIDC flow.
`token_endpoint`	TEXT, nullable	The token endpoint URL for the provider, cached at session creation to avoid repeated discovery lookups.
`encrypted_identity`	TEXT, nullable	AES-256-GCM encrypted identity data received from the provider after successful token exchange.
`encrypted_identity_key_version`	INTEGER, nullable	Version of the encryption key used for `encrypted_identity`.
`error_message`	TEXT, nullable	Error description if the session ended in a `FAILED` state.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of session creation.
`expires_at`	TEXT, NOT NULL	ISO-8601 timestamp after which the session is considered expired and eligible for cleanup.

Unique Index: idx_recon_session_state

The partial unique index on state (where state IS NOT NULL) ensures that no two active sessions share the same OIDC state parameter. This is critical for security: when the OIDC callback arrives with a state value, the system must be able to unambiguously identify which session it belongs to.

Index: idx_recon_session_status

The index on (tenant_id, status) supports efficient queries for sessions in a particular state, such as finding all expired sessions for cleanup or all pending sessions for monitoring.

Session Lifecycle

A reconciliation session progresses through the following states:

PENDING -- Session created, authorization URL generated, waiting for the user to authenticate with the provider.
AUTHORIZED -- The OIDC callback has been received with an authorization code, token exchange is in progress.
COMPLETED -- Token exchange succeeded, identity data received and encrypted, binding created or updated.
FAILED -- An error occurred at any stage. The error_message column contains details.
EXPIRED -- The session exceeded its TTL without completing. Cleaned up by the background session cleanup job.

reconciliation_provider

The reconciliation_provider table stores configuration for each upstream OIDC identity provider that can be used during the reconciliation process. This is pure configuration data with no sensitive content.

CREATE TABLE IF NOT EXISTS reconciliation_provider (
    id                    TEXT NOT NULL PRIMARY KEY,
    name                  TEXT NOT NULL,
    oidc_client_id        TEXT NOT NULL,
    identifier_attr_name  TEXT NOT NULL DEFAULT 'sub',
    enabled               INTEGER NOT NULL DEFAULT 1,
    attribute_mappings    TEXT,   -- JSON
    assurance_acr         TEXT,
    assurance_amr         TEXT,
    created_at            TEXT NOT NULL,
    updated_at            TEXT NOT NULL
);

Column Details

Column	Type	Description
`id`	TEXT, PK	Unique identifier for the provider (e.g., `surf`, `keycloak`).
`name`	TEXT, NOT NULL	Human-readable display name for the provider (e.g., `SURF eduID`).
`oidc_client_id`	TEXT, NOT NULL	The OIDC client ID registered with this provider.
`identifier_attr_name`	TEXT, NOT NULL	The name of the claim in the ID token or userinfo response that serves as the primary institution identifier. Defaults to `sub`.
`enabled`	INTEGER, NOT NULL	Whether this provider is currently enabled (1) or disabled (0). Disabled providers are not offered during reconciliation. Defaults to 1.
`attribute_mappings`	TEXT, nullable	JSON object mapping provider-specific claim names to canonical attribute names. Enables normalization of attributes across different providers.
`assurance_acr`	TEXT, nullable	Expected Authentication Context Class Reference value for tokens from this provider. Used to assess the assurance level of reconciliation.
`assurance_amr`	TEXT, nullable	Expected Authentication Methods Reference values for tokens from this provider.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of provider creation.
`updated_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent update.

Configuration vs. Runtime

This table is typically populated during deployment or initial setup and rarely changes during normal operation. Changes to provider configuration (such as updating an OIDC client ID or modifying attribute mappings) take effect immediately for new reconciliation sessions but do not retroactively affect existing bindings.

auxiliary_data

The auxiliary_data table stores supplementary encrypted data associated with an identity. This is a flexible key-value store where the "key" is the combination of identity_id and category, and the "value" is an encrypted payload. Common use cases include storing institution-specific metadata such as enrollment status or organizational unit.

CREATE TABLE IF NOT EXISTS auxiliary_data (
    id                    TEXT NOT NULL PRIMARY KEY,
    tenant_id             TEXT NOT NULL,
    identity_id           TEXT NOT NULL,
    category              TEXT NOT NULL,
    encrypted_payload     TEXT NOT NULL,    -- AES-256-GCM (Key C)
    schema_version        TEXT NOT NULL DEFAULT '1',
    created_at            TEXT NOT NULL,
    updated_at            TEXT NOT NULL,
    expires_at            TEXT,
    UNIQUE(tenant_id, identity_id, category)
);

CREATE INDEX IF NOT EXISTS idx_aux_identity
    ON auxiliary_data(tenant_id, identity_id);
CREATE INDEX IF NOT EXISTS idx_aux_expires
    ON auxiliary_data(expires_at) WHERE expires_at IS NOT NULL;

Column Details

Column	Type	Description
`id`	TEXT, PK	UUID primary key.
`tenant_id`	TEXT, NOT NULL	Tenant identifier for multi-tenant isolation.
`identity_id`	TEXT, NOT NULL	The internal identity ID that this auxiliary data is associated with.
`category`	TEXT, NOT NULL	Category label for the data (e.g., `enrollment`, `org_unit`, `student_status`). Together with `identity_id`, forms the logical key.
`encrypted_payload`	TEXT, NOT NULL	The data payload encrypted with AES-256-GCM using Key C. The plaintext structure depends on the category and schema version.
`schema_version`	TEXT, NOT NULL	Version of the payload schema, allowing the system to handle schema evolution gracefully. Defaults to `'1'`.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of record creation.
`updated_at`	TEXT, NOT NULL	ISO-8601 timestamp of the most recent update.
`expires_at`	TEXT, nullable	Optional ISO-8601 timestamp after which this data is considered expired and eligible for automatic cleanup. Useful for time-limited data such as semester-specific enrollment.

Unique Constraint

The unique constraint on (tenant_id, identity_id, category) ensures that each identity has at most one auxiliary data record per category within a tenant. Writing to the same identity-category pair performs an upsert, replacing the previous encrypted payload.

Index: idx_aux_identity

The index on (tenant_id, identity_id) supports efficient retrieval of all auxiliary data records for a given identity, which is needed during token issuance and identity lookup operations.

Index: idx_aux_expires

The partial index on expires_at (where expires_at IS NOT NULL) supports efficient identification of expired records during background cleanup. Only records that actually have an expiration date are included in this index, keeping it compact.

key_migration_history

The key_migration_history table tracks cryptographic key rotation operations. When a key is rotated, the system must re-hash or re-encrypt all affected records. This table records the progress and outcome of each migration, enabling operators to monitor the process and verify that it completed successfully.

CREATE TABLE IF NOT EXISTS key_migration_history (
    version           INTEGER NOT NULL PRIMARY KEY,
    status            TEXT NOT NULL,
    config_snapshot   TEXT,
    records_processed INTEGER NOT NULL DEFAULT 0,
    records_failed    INTEGER NOT NULL DEFAULT 0,
    records_skipped   INTEGER NOT NULL DEFAULT 0,
    records_purged    INTEGER NOT NULL DEFAULT 0,
    created_at        TEXT NOT NULL,
    completed_at      TEXT
);

Column Details

Column	Type	Description
`version`	INTEGER, PK	The target key version that this migration is transitioning to. Serves as both the primary key and a monotonically increasing version counter.
`status`	TEXT, NOT NULL	Current status of the migration. Values include `IN_PROGRESS`, `COMPLETED`, `FAILED`, and `ROLLED_BACK`.
`config_snapshot`	TEXT, nullable	JSON snapshot of the key configuration at the time the migration started. Useful for debugging and auditing.
`records_processed`	INTEGER, NOT NULL	Number of records successfully re-hashed or re-encrypted during the migration. Defaults to 0.
`records_failed`	INTEGER, NOT NULL	Number of records that could not be migrated due to errors. Defaults to 0.
`records_skipped`	INTEGER, NOT NULL	Number of records skipped because they were already at the target version. Defaults to 0.
`records_purged`	INTEGER, NOT NULL	Number of records purged (deleted) during the migration, typically soft-deleted records past their retention period. Defaults to 0.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of when the migration was initiated.
`completed_at`	TEXT, nullable	ISO-8601 timestamp of when the migration finished (whether successfully, with failures, or rolled back).

Migration Workflow

A key migration progresses through the following steps:

A new migration record is inserted with status = 'IN_PROGRESS' and the target version as the primary key.
The migration process iterates through all affected records, re-computing hashes or re-encrypting ciphertext with the new key version.
As records are processed, the counters (records_processed, records_failed, records_skipped, records_purged) are updated.
Upon completion, the status is updated to COMPLETED and completed_at is set.
If the migration fails partway through, the status is set to FAILED and operators can investigate using the counter values and config snapshot.

audit_event

The audit_event table is an append-only log of all significant operations performed by the system. No UPDATE or DELETE operations are ever executed against this table in normal operation, ensuring the integrity of the audit trail for regulatory compliance.

CREATE TABLE IF NOT EXISTS audit_event (
    id              TEXT NOT NULL PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    event_type      TEXT NOT NULL,
    correlation_id  TEXT,
    subject_hash    TEXT,
    client_id       TEXT,
    detail          TEXT,    -- JSON
    created_at      TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_audit_type
    ON audit_event(tenant_id, event_type);
CREATE INDEX IF NOT EXISTS idx_audit_time
    ON audit_event(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_correlation
    ON audit_event(correlation_id);

Column Details

Column	Type	Description
`id`	TEXT, PK	UUID primary key for the event.
`tenant_id`	TEXT, NOT NULL	Tenant identifier for multi-tenant isolation.
`event_type`	TEXT, NOT NULL	Type of event (e.g., `session_created`, `binding_created`, `gdpr_erasure`). See the Audit Trail page for the complete list.
`correlation_id`	TEXT, nullable	Links related events across a single user flow. For example, all events during a single reconciliation session share the same correlation ID.
`subject_hash`	TEXT, nullable	HMAC-hashed identifier of the subject of the event. Never stored in plaintext.
`client_id`	TEXT, nullable	Identifier of the client (application) that triggered the event, when applicable.
`detail`	TEXT, nullable	JSON object containing additional operational context. The contents vary by event type but never include plaintext PII.
`created_at`	TEXT, NOT NULL	ISO-8601 timestamp of when the event occurred.

Indexes

Three indexes support the most common audit query patterns:

idx_audit_type on (tenant_id, event_type) -- Find all events of a specific type within a tenant, such as retrieving all GDPR erasure events.
idx_audit_time on (created_at) -- Time-range queries across all tenants, useful for operational dashboards and monitoring.
idx_audit_correlation on (correlation_id) -- Retrieve all events belonging to a single user flow, enabling end-to-end tracing of a reconciliation session.

Key Queries

The following table lists the most important named SqlDelight queries defined in the application, providing a quick reference for what each query does and which table it primarily operates on.

Query Name	Table	Description
`findMatchByHash`	identity_match	Look up a match record by tenant, identifier hash, and identifier type. The primary lookup path for identity matching.
`findMatchesByIdentity`	identity_match	Find all match records for a given internal identity ID. Used during GDPR erasure and identity inspection.
`insertMatch`	identity_match	Create a new match record with all required fields.
`softDeleteMatch`	identity_match	Set `deleted_at` and `deletion_reason` on a match record.
`hardDeleteMatch`	identity_match	Permanently remove a soft-deleted match record from the database.
`findBindingsByMatch`	identity_link_binding	Retrieve all bindings associated with a match record.
`findBindingByHolder`	identity_link_binding	Look up bindings by holder identifier hash.
`findInactiveBindings`	identity_link_binding	Find bindings where `last_used_at` is older than the inactivity threshold.
`insertBinding`	identity_link_binding	Create a new binding record.
`updateBinding`	identity_link_binding	Update an existing binding after re-reconciliation.
`hardDeleteBinding`	identity_link_binding	Permanently remove a binding record.
`findSoftDeletedMatchesPastRetention`	identity_match	Find soft-deleted matches where the retention period has elapsed.
`findSoftDeletedBindingsPastRetention`	identity_link_binding	Find soft-deleted bindings where the retention period has elapsed.
`insertSession`	reconciliation_session	Create a new reconciliation session.
`findSessionByState`	reconciliation_session	Look up a session by its OIDC state parameter. Used during the callback.
`updateSessionStatus`	reconciliation_session	Transition a session to a new status.
`deleteExpiredSessions`	reconciliation_session	Remove sessions past their `expires_at` time.
`findAuxByIdentity`	auxiliary_data	Retrieve all auxiliary data for an identity.
`upsertAux`	auxiliary_data	Insert or update auxiliary data for an identity-category pair.
`findExpiredAux`	auxiliary_data	Find auxiliary data records past their `expires_at` time.
`deleteAuxByIdentity`	auxiliary_data	Delete all auxiliary data for an identity. Used during GDPR erasure.
`insertAuditEvent`	audit_event	Append a new audit event.
`findAuditEventsByType`	audit_event	Paginated query for audit events of a specific type within a tenant.
`findAuditEventsByCorrelation`	audit_event	Retrieve all audit events sharing a correlation ID.
`countMatches`	identity_match	Count total match records per tenant. Used for monitoring.
`countAllBindings`	identity_link_binding	Count total binding records per tenant.
`countInactiveBindings`	identity_link_binding	Count bindings past the inactivity threshold.
`countAllAux`	auxiliary_data	Count total auxiliary data records per tenant.
`insertMigration`	key_migration_history	Record the start of a key migration.
`updateMigrationProgress`	key_migration_history	Update counters during a running migration.
`completeMigration`	key_migration_history	Mark a migration as completed.

identity_match​

Column Details​

Unique Constraint​

Index: idx_match_identity​

Soft Delete​

identity_link_binding​

Column Details​

Index: idx_binding_match​

Index: idx_binding_holder​

Domain-Separated Keys​

reconciliation_session​

Column Details​

Unique Index: idx_recon_session_state​

Index: idx_recon_session_status​

Session Lifecycle​

reconciliation_provider​

Column Details​

Configuration vs. Runtime​

auxiliary_data​

Column Details​

Unique Constraint​

Index: idx_aux_identity​

Index: idx_aux_expires​

key_migration_history​

Column Details​

Migration Workflow​

audit_event​

Column Details​

Indexes​

Key Queries​

identity_match

Column Details

Unique Constraint

Index: idx_match_identity

Soft Delete

identity_link_binding

Column Details

Index: idx_binding_match

Index: idx_binding_holder

Domain-Separated Keys

reconciliation_session

Column Details

Unique Index: idx_recon_session_state

Index: idx_recon_session_status

Session Lifecycle

reconciliation_provider

Column Details

Configuration vs. Runtime

auxiliary_data

Column Details

Unique Constraint

Index: idx_aux_identity

Index: idx_aux_expires

key_migration_history

Column Details

Migration Workflow

audit_event

Column Details

Indexes

Key Queries