PII Separation Architecture

Authrim separates sensitive identity values from protocol, authorization, and admin control-plane data. The current model is based on runtime storage profiles: a deployment can use shared D1 bindings, tenant-owned D1 slots, or external storage adapters while keeping the same logical source boundaries.

Storage Planes

The default builtin:storage:shared-d1 profile uses three deployment-level D1 bindings.

Binding	Storage plane	Typical data
`DB`	Core identity and protocol data	tenants, OAuth clients, roles, relationships, consent records, legacy `users_core`, canonical identity graph tables
`DB_PII`	Sensitive identity values and PII audit data	legacy `users_pii`, linked identities, subject identifiers, `identity_sensitive_values`, PII tombstones, PII audit logs
`DB_ADMIN`	Admin and control-plane data	admin users/RBAC, runtime profiles, tenant database registry, field catalogs, operational jobs

KV and Durable Objects remain separate from these D1 planes. KV is used for caches and signed runtime snapshots, while Durable Objects hold hot protocol state such as sessions, authorization codes, refresh-token rotation, PAR, DPoP JTI, device flow, CIBA, and rate limits.

Runtime Profiles

PII separation is enforced through logical sources and storage slices rather than hard-coded regional bindings.

Profile	Behavior
`builtin:storage:shared-d1`	Uses deployment-wide `DB`, `DB_PII`, and `DB_ADMIN` bindings
`builtin:storage:tenant-d1`	Keeps control data shared and routes tenant-owned core/PII/audit slices through the tenant database registry
`builtin:storage:external-durable`	Moves selected durable state to external adapters where configured
External SQL profiles	Use configured connection references instead of embedding credentials in tenant settings

In tenant-D1 mode, Authrim does not silently fall back to shared D1 when a tenant database route is missing. Unsupported routes fail without returning PII so storage-boundary bugs do not move sensitive data into the wrong plane.

Current Identity Model

New runtime user paths use the canonical identity graph. Protocol code reads and writes through CanonicalRuntimeUserStore, which keeps runtime code off direct users_core / users_pii row handling.

flowchart TB
    subgraph Core["Core DB: DB"]
        Subjects["identity_subjects"]
        Accounts["identity_accounts"]
        Links["subject_account_links"]
        Profiles["profiles<br/>display_name_ref"]
        Attributes["profile_attribute_values<br/>value_storage_ref, value_hash, purpose"]
        Contacts["contact_points<br/>normalized_hash, value_storage_ref, purpose"]
        Bindings["identity_bindings"]
        SearchIndexes["contact_point_search_indexes"]
    end

    subgraph PII["PII DB: DB_PII"]
        Sensitive["identity_sensitive_values<br/>value_json, value_hash"]
        LegacyPII["users_pii / linked_identities"]
        SubjectIds["subject_identifiers<br/>identifier_storage_ref"]
        Tombstones["users_pii_tombstone / pii_log"]
    end

    subgraph Control["Admin DB: DB_ADMIN"]
        RuntimeProfiles["storage_deployment_profiles"]
        TenantRegistry["tenant database registry"]
        FieldCatalogs["field_catalogs"]
    end

    Attributes -->|value_storage_ref| Sensitive
    Contacts -->|value_storage_ref| Sensitive
    Contacts --> SearchIndexes
    SubjectIds --> Sensitive
    RuntimeProfiles --> Core
    TenantRegistry --> Core
    TenantRegistry --> PII

Core graph rows may contain lifecycle state, metadata, hashes, and storage references. Sensitive values such as email, phone, profile name, address, and custom sensitive attributes are stored in DB_PII as identity_sensitive_values.

Key Tables

Core Identity Graph (`DB`)

CREATE TABLE IF NOT EXISTS identity_subjects (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  subject_type TEXT NOT NULL,
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  display_label TEXT,
  primary_account_id TEXT,
  risk_tier TEXT,
  assurance_level TEXT,
  metadata_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);

CREATE TABLE IF NOT EXISTS identity_accounts (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  account_type TEXT NOT NULL,
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  legacy_user_id TEXT,
  primary_subject_id TEXT,
  display_label TEXT,
  metadata_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);

CREATE TABLE IF NOT EXISTS profiles (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  subject_id TEXT NOT NULL,
  profile_type TEXT NOT NULL DEFAULT 'person',
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  locale TEXT,
  zoneinfo TEXT,
  display_name_ref TEXT,
  metadata_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);

CREATE TABLE IF NOT EXISTS profile_attribute_values (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  profile_id TEXT NOT NULL,
  catalog_entry_id TEXT NOT NULL,
  value_type TEXT NOT NULL,
  value_json TEXT,
  value_storage_ref TEXT,
  value_hash TEXT,
  classification TEXT NOT NULL DEFAULT 'internal',
  purpose TEXT,
  is_primary INTEGER NOT NULL DEFAULT 0,
  display_order INTEGER NOT NULL DEFAULT 0,
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);

CREATE TABLE IF NOT EXISTS contact_points (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  subject_id TEXT,
  account_id TEXT,
  contact_type TEXT NOT NULL,
  purpose TEXT NOT NULL DEFAULT 'primary',
  normalized_hash TEXT,
  value_storage_ref TEXT,
  display_label TEXT,
  is_primary INTEGER NOT NULL DEFAULT 0,
  verification_state TEXT NOT NULL DEFAULT 'unverified',
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);

Sensitive Values (`DB_PII`)

CREATE TABLE IF NOT EXISTS identity_sensitive_values (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  owner_type TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  value_key TEXT NOT NULL,
  value_json TEXT,
  value_hash TEXT,
  classification TEXT NOT NULL DEFAULT 'sensitive',
  lifecycle_state TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (tenant_id, owner_type, owner_id, value_key)
);

Legacy Runtime Tables

The migration baseline still includes legacy compatibility tables. They are important for upgrades and some management paths, but new runtime user writes are projected into the canonical identity graph.

CREATE TABLE users_core (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  email_verified INTEGER DEFAULT 0,
  phone_number_verified INTEGER DEFAULT 0,
  email_domain_hash TEXT,
  password_hash TEXT,
  is_active INTEGER DEFAULT 1,
  user_type TEXT NOT NULL DEFAULT 'end_user',
  pii_partition TEXT NOT NULL DEFAULT 'default',
  pii_status TEXT NOT NULL DEFAULT 'pending',
  lifecycle_state TEXT DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  last_login_at INTEGER
);

CREATE TABLE IF NOT EXISTS users_pii (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  pii_class TEXT NOT NULL DEFAULT 'PROFILE',
  email TEXT NOT NULL,
  email_blind_index TEXT,
  phone_number TEXT,
  name TEXT,
  given_name TEXT,
  family_name TEXT,
  address_country TEXT,
  declared_residence TEXT,
  custom_attributes_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

Search and Lookup

Core search surfaces should not need plaintext PII. The current schema supports:

Use case	Core-side field	PII-side field
Email/contact lookup	`contact_points.normalized_hash`, `contact_point_search_indexes.index_value`	`identity_sensitive_values.value_json`
Profile display	`profiles.display_name_ref`, `profile_attribute_values.value_storage_ref`	matching `identity_sensitive_values` row
Pairwise subject identifiers	`subject_identifiers.identifier_value_hash` / `identifier_storage_ref` after canonical migration	sensitive identifier value when needed
Field catalog metadata	`DB_ADMIN.field_catalog_entries`	actual sensitive field values stay in `DB_PII`

Deletion and Audit

PII deletion uses tombstones and audit records in the PII plane.

CREATE TABLE IF NOT EXISTS users_pii_tombstone (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  email_blind_index TEXT,
  deleted_at INTEGER NOT NULL,
  deleted_by TEXT,
  deletion_reason TEXT,
  retention_until INTEGER NOT NULL,
  deletion_metadata TEXT,
  created_at INTEGER,
  updated_at INTEGER
);

CREATE TABLE IF NOT EXISTS pii_log (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  anonymized_user_id TEXT NOT NULL,
  change_type TEXT NOT NULL,
  affected_fields TEXT NOT NULL,
  values_r2_key TEXT,
  values_encrypted TEXT,
  retention_until INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

Best Practices

Route storage through runtime profiles instead of hard-coding D1 binding names in feature code.
Store sensitive attribute values in identity_sensitive_values; keep core graph tables to hashes, references, state, and metadata.
Treat legacy users_core / users_pii tables as compatibility surfaces, not the primary model for new runtime identity features.
Do not join across storage planes. Fetch core rows and sensitive values separately through the repository/runtime store layer.
Include storage profile ID, source generation, and schema version in cache scope when tenant-D1 routing is involved.