IdentityMatch Record

The identity_match table is the fast-lookup index at the heart of the matching system. Given an HMAC-hashed external identifier and its type, it returns the internal identity ID. This single lookup is what makes returning-wallet-user authentication sub-second: one database query to resolve the identity, then decrypt the cached attributes from the associated binding.

Every time a user presents a credential -- whether a wallet public key, an OIDC subject identifier, or any other supported identifier type -- the system computes the HMAC hash of that identifier, queries identity_match with the hash, and either finds an existing identity or determines that this is a new, unrecognized user. The table is indexed for this exact access pattern, making the lookup operation efficient even as the number of registered identities grows.

Record Structure

The identity_match table contains the following columns. Each record represents a single mapping from one hashed external identifier to one internal identity.

Column	Type	Description
id	TEXT (UUID)	Primary key. A unique identifier for this match record, generated as a UUID v4 at creation time.
tenant_id	TEXT	Multi-tenant isolation key. Every query includes a tenant filter to ensure strict data separation between tenants.
identifier_hash	TEXT	The HMAC-SHA256 hash of the external identifier. Computed using Key A (for holder identifiers) or Key B (for institutional identifiers). This is the primary lookup value.
identifier_type	TEXT	The type of identifier that was hashed. One of: KEY, SUBJECT_ID, EMAIL, DID, CLAIM_TUPLE. Combined with the hash, this forms the lookup key.
internal_identity_id	TEXT (UUID)	The resolved internal identity that this match points to. Multiple match records can share the same internal_identity_id, representing different ways to look up the same person.
hash_key_version	INTEGER	The version of the HMAC key that was used to compute identifier_hash. This field is critical for key rotation: it tells the system whether this hash was computed with the current key or a previous key.
metadata_json	TEXT	Optional JSON metadata about the match. This can store additional context such as the source of the match, the authentication method used, or diagnostic information. The schema of this JSON is not enforced at the database level.
created_at	TEXT (ISO 8601)	Timestamp of when this match record was first created. Set once at insertion time and never modified.
updated_at	TEXT (ISO 8601)	Timestamp of the most recent modification to this record. Updated whenever any field changes, including during key rotation migrations.
last_used_at	TEXT (ISO 8601)	Timestamp of the most recent lookup that accessed this record. Updated each time the record is used for identity resolution. This field drives inactivity-based cleanup: records not accessed within the configured threshold (default 2 years) are candidates for deletion.
deleted_at	TEXT (ISO 8601)	Soft-delete timestamp. When set, this record is considered logically deleted and is excluded from normal lookups. The record is retained during the configurable retention period (default 30 days) before permanent removal.
deletion_reason	TEXT	The reason for deletion. Possible values: gdpr_request (user-initiated erasure under GDPR Art. 17), inactivity (exceeded the 2-year inactivity threshold), key_rotation (old key version cleanup during migration).

Unique Constraint

The table enforces a unique constraint on the combination of (tenant_id, identifier_hash, identifier_type). This constraint guarantees three important properties.

One identifier maps to exactly one identity within a tenant. A given wallet key fingerprint (hashed) can only resolve to one internal identity within the same tenant. If the system encounters a conflict -- for example, if a wallet key that was previously matched to identity A is now being matched to identity B -- the upsert operation updates the existing record rather than creating a duplicate.

Cross-tenant isolation. The same identifier hash appearing in two different tenants is treated as two completely independent matches. Tenant A's identity resolution is invisible to tenant B, even if the underlying user happens to be the same person. This is fundamental to the multi-tenant architecture.

Multiple identifier types per identity. The same internal_identity_id can appear in multiple rows, each with a different identifier_type. This is the mechanism that enables multi-match identity resolution. The unique constraint allows this because the identifier_type is part of the uniqueness tuple: a KEY match and a SUBJECT_ID match are distinct records even if they point to the same identity.

Multiple Matches Per Identity

After a first-time wallet login with successful reconciliation, two match records are created pointing to the same internal identity. This is the normal and expected outcome.

Match 1:
  identifier_hash = HMAC(wallet_key_thumbprint, Key_A)
  identifier_type = KEY
  internal_identity_id = abc-123

Match 2:
  identifier_hash = HMAC(surfconext_sub, Key_B)
  identifier_type = SUBJECT_ID
  internal_identity_id = abc-123

Both records resolve to abc-123. Match 1 is used when the user returns with their wallet (the common fast-path). Match 2 is used when an external system queries the REST API with the institutional identifier (the reverse-lookup path).

This design also supports future expansion. If the system later supports DID-based authentication, a third match of type DID can be added to the same identity without modifying the existing records. The identity accumulates lookup paths over time.

Soft Delete and GDPR Art. 17

The identity_match table supports soft deletion to comply with GDPR Article 17 (Right to Erasure). When a deletion is requested, the system does not immediately remove the record from the database. Instead, it sets the deleted_at timestamp and the deletion_reason field.

During the retention period (configurable, default 30 days), the record remains in the database but is excluded from normal lookups. All queries that resolve identities include a WHERE deleted_at IS NULL filter, so soft-deleted records are invisible to the matching logic. The retention period exists to support audit trails, incident investigation, and the possibility of undoing an accidental deletion.

After the retention period expires, the hardDeleteMatch operation permanently removes the record from the database. This is an irreversible operation -- once hard-deleted, the match cannot be recovered.

Three deletion reasons are supported:

• gdpr_request: The user has exercised their right to erasure under GDPR Article 17. This triggers deletion of all matches associated with the user's internal_identity_id, along with all associated identity_link_binding records.
• inactivity: The record has not been accessed within the configured inactivity threshold (default 2 years). The last_used_at timestamp is compared against the threshold, and records that exceed it are marked for deletion.
• key_rotation: During a key rotation migration, records using an old key version that cannot be migrated (for example, because the identity is inactive and scheduled for cleanup) are soft-deleted rather than migrated to the new key.

Indexes

The table uses carefully chosen indexes to support the two primary access patterns.

Primary Lookup Index

The unique constraint on (tenant_id, identifier_hash, identifier_type) serves as the primary lookup index. This is the index used by the findMatchByIdentifierHash query, which is the hottest query in the system -- executed on every wallet authentication request. The index enables the database to resolve an identity in a single B-tree traversal, regardless of how many total matches exist.

Identity Reverse-Lookup Index

The index idx_match_identity(tenant_id, internal_identity_id) supports the reverse direction: given an internal identity ID, find all matches associated with it. This access pattern is used in two critical scenarios:

• GDPR erasure: When a user requests deletion, the system must find and soft-delete every match record associated with their identity. Without this index, the operation would require a full table scan.
• External API lookup: When a third-party system queries the REST API with an identity ID, the system uses this index to find all associated matches and their linked bindings.

Key Queries

The following queries from the SqlDelight schema define the primary operations on the identity_match table.

findMatchByIdentifierHash

The primary lookup query. Given a tenant, an identifier hash, and an identifier type, returns the match record if it exists and has not been soft-deleted. This is the query executed on every authentication request.

SELECT * FROM identity_match
WHERE tenant_id = :tenant_id
  AND identifier_hash = :identifier_hash
  AND identifier_type = :identifier_type
  AND deleted_at IS NULL;

findMatchesByIdentityId

The reverse-lookup query. Given a tenant and an internal identity ID, returns all match records associated with that identity. Used for GDPR erasure and external API responses.

SELECT * FROM identity_match
WHERE tenant_id = :tenant_id
  AND internal_identity_id = :internal_identity_id;

upsertMatch

Insert a new match record, or update the existing record if a conflict occurs on the unique constraint (tenant_id, identifier_hash, identifier_type). On conflict, the internal_identity_id, hash_key_version, metadata_json, and updated_at fields are updated. This operation is used during reconciliation to create or update matches atomically.

softDeleteMatch

Set the deleted_at and deletion_reason fields on an existing match record. The record remains in the database but is excluded from normal lookups.

hardDeleteMatch

Permanently remove a match record from the database. This operation is irreversible and is executed only after the retention period has expired. The system checks that deleted_at is older than the retention threshold before proceeding.