database-schema-new.md

March 19, 2026 · View on GitHub

Purpose

This document specifies a high‑performance PostgreSQL schema for storing Wallet Relying Parties (WRP) and their Intended Uses ("use cases") derived from the ts5 specification. It is optimized to query Intended Uses with filters:

  • filter by claim path
  • filter by country
  • search by relying party name

Filters are combinable and backed by indexes.

Key Differences from Previous Schema

This schema differs from database-schema.md in the following ways:

Structural Changes

  • Separated legal entity: Introduced legal_entity table with dual relationships:
    • wrp.id = legal_entity.id (shared PK: a WRP is a legal entity)
    • wrp.supervisory_authority_id → legal_entity.id (FK: a WRP has a supervisory authority legal entity)
  • String deduplication: Added strings lookup table for frequently repeated values (entitlement URIs, policy types, etc.)
  • Renamed tables: intended_uses → intended_use (singular), intended_use_credentials → credential, credential_claims → claim
  • No partitioning: Removed hash partitioning on intended_use and history tables (simpler design, partition later if needed)

Index Strategy

  • No denormalized arrays: Removed requested_credential_types, requested_formats, requested_claim_paths arrays and their maintenance triggers
  • Direct join queries: Queries join through credential and claim tables directly using btree and trigram indexes
  • Added trigram indexes: On claim.path (lowercase), wrp.trade_name, and credential.meta for fuzzy search

History Storage

  • Validity-based snapshots: History tables use valid_from and valid_until dates instead of version numbers
  • Compressed storage: Snapshots stored as bytea (compressed) instead of JSONB

Trade-offs

  • Simpler schema: Easier to maintain, no complex triggers for aggregate arrays
  • Slightly more joins: Queries join through more tables but indexes should keep performance good
  • String normalization: strings table adds small lookup overhead but reduces storage for repeated values

Entity overview

  • strings: lookup table for string deduplication
  • legal_entity: legal entity information (country, contact info, addresses) - used for both WRPs and supervisory authorities
  • legal_entity_identifiers: identifiers (e.g., EUID) linked to legal entities
  • wrp: Wallet Relying Party core entity
    • wrp.id = legal_entity.id (shared PK: WRP is a legal entity)
    • wrp.supervisory_authority_id → legal_entity.id (FK: WRP has a supervisory authority)
  • wrp_entitlements: entitlement URIs (references strings table)
  • wrp_support_uris: support/contact URIs
  • wrp_service_descriptions: multilingual service descriptions
  • wrp_uses_intermediary: self‑references to intermediary WRPs
  • wrp_provides_attestations, wrp_provides_claims: WRP-level attestation types
  • wrp_history: compressed snapshots of WRP changes with validity timestamps
  • intended_use: Intended Use (the "use case")
  • intended_use_purposes: multilingual Intended Use purposes
  • intended_use_policies: Intended Use policy entries
  • credential: credentials under an Intended Use (format + meta)
  • claim: claims under a credential (path + values)

Database Schema

-- Extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 1) Lookup table
CREATE TABLE strings (
    id      bigserial PRIMARY KEY,
    value   text NOT NULL
);

-- 2) Legal Entity
CREATE TABLE legal_entity (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legal_person      JSONB,
  natural_person    JSONB,
  postal_address    JSONB,
  country           char(2) NOT NULL,
  email             JSONB,
  phone             JSONB,
  info_uri          JSONB
);

CREATE INDEX IF NOT EXISTS legal_entity_country_idx ON legal_entity(country);

-- Identifiers (e.g., EUID); unique per (type, value)
CREATE TABLE legal_entity_identifiers (
	id               bigserial PRIMARY KEY,
  legal_entity_id  uuid NOT NULL REFERENCES legal_entity(id) ON DELETE CASCADE,
  type             bigserial NOT NULL REFERENCES strings(id),
  identifier       text NOT NULL,
  UNIQUE (type, identifier)
);

CREATE INDEX IF NOT EXISTS legal_entity_identifiers_value_idx ON legal_entity_identifiers (identifier);
CREATE INDEX IF NOT EXISTS legal_entity_identifiers_entity_fk_idx ON legal_entity_identifiers (legal_entity_id);

-- 3) Wallet Relying Party (WRP)
-- wrp.id = legal_entity.id (shared PK: a WRP IS a legal entity)
-- wrp.supervisory_authority_id references a different legal_entity (a WRP HAS a supervisory authority)
CREATE TABLE wrp (
  id                          uuid PRIMARY KEY REFERENCES legal_entity(id) ON DELETE CASCADE,
  trade_name                  text,
  is_psb                      boolean DEFAULT NULL,
  is_intermediary             boolean DEFAULT NULL,
  supervisory_authority_id    uuid REFERENCES legal_entity(id) ON DELETE SET NULL,
  provider_type               bigserial NOT NULL REFERENCES strings(id),
  x5c                         JSONB,
  policy                      JSONB NOT NULL,
  ipfs_cid                    text,                   -- IPFS CID of live snapshot (Storacha)
  ipfs_claimed_at             timestamptz,            -- Claim lock for multi-publisher coordination
  created_at                  timestamptz NOT NULL DEFAULT now(),
  updated_at                  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS wrp_supervisory_authority_fk_idx ON wrp(supervisory_authority_id);
CREATE INDEX IF NOT EXISTS wrp_trade_name_trgm_idx ON wrp USING gin (trade_name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS wrp_is_psb_idx ON wrp(is_psb);
CREATE INDEX IF NOT EXISTS wrp_is_intermediary_idx ON wrp(is_intermediary);

-- Entitlements (URIs)
CREATE TABLE wrp_entitlements (
  id              bigserial PRIMARY KEY,
  wrp_id          uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  entitlement     bigserial NOT NULL REFERENCES strings(id),
  UNIQUE (wrp_id, entitlement)
);

CREATE INDEX IF NOT EXISTS wrp_entitlement_idx ON wrp_entitlements (entitlement);
CREATE INDEX IF NOT EXISTS wrp_entitlement_wrp_fk_idx ON wrp_entitlements (wrp_id);

-- Support/contact URIs (email/phone/webpage flattened)
CREATE TABLE wrp_support_uris (
  id          bigserial PRIMARY KEY,
  wrp_id      uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  support_uri text NOT NULL,
  UNIQUE (wrp_id, support_uri)
);

CREATE INDEX IF NOT EXISTS wrp_support_uris_wrp_fk_idx ON wrp_support_uris (wrp_id);

-- Multilingual service descriptions (srvDescription)
-- service_idx groups translations of the same description
CREATE TABLE wrp_service_descriptions (
  id          bigserial PRIMARY KEY,
  wrp_id      uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  service_idx integer NOT NULL,
  lang        text NOT NULL,
  content     text NOT NULL,
  UNIQUE (wrp_id, service_idx, lang)
);

CREATE INDEX IF NOT EXISTS wrp_service_descriptions_wrp_fk_idx ON wrp_service_descriptions (wrp_id);

-- Intermediaries (usesIntermediary)
CREATE TABLE wrp_uses_intermediary (
  id                   bigserial PRIMARY KEY,
  wrp_id               uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  intermediary_wrp_id  uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  UNIQUE (wrp_id, intermediary_wrp_id)
);

CREATE INDEX IF NOT EXISTS wrp_uses_intermediary_wrp_fk_idx ON wrp_uses_intermediary (wrp_id);
CREATE INDEX IF NOT EXISTS wrp_uses_intermediary_intermediary_fk_idx ON wrp_uses_intermediary (intermediary_wrp_id);

-- WRP-level providesAttestations (set of sub-entitlements per spec)
CREATE TABLE wrp_provides_attestations (
  id         bigserial PRIMARY KEY,
  wrp_id     uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  format     text NOT NULL,
  meta       text NOT NULL,
  meta_text  text
);

CREATE INDEX IF NOT EXISTS wrp_provides_wrp_fk_idx ON wrp_provides_attestations (wrp_id);
CREATE INDEX IF NOT EXISTS wrp_provides_format_idx ON wrp_provides_attestations (format);
CREATE INDEX IF NOT EXISTS wrp_provides_meta_trgm_idx ON wrp_provides_attestations USING gin (meta gin_trgm_ops);

CREATE TABLE wrp_provides_claims (
  id              bigserial PRIMARY KEY,
  provides_id     bigserial NOT NULL REFERENCES wrp_provides_attestations(id) ON DELETE CASCADE,
  path            text NOT NULL,
  values          jsonb
);

CREATE INDEX IF NOT EXISTS wrp_provides_claim_provides_fk_idx ON wrp_provides_claims (provides_id);
CREATE INDEX IF NOT EXISTS wrp_provides_claim_path_idx ON wrp_provides_claims (path);

-- 4) Intended Use (the "use case")
CREATE TABLE intended_use (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  wrp_id         uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS intended_use_wrp_fk_idx ON intended_use (wrp_id);

-- Intended Use purposes (MultiLangString)
CREATE TABLE intended_use_purposes (
  id                bigserial PRIMARY KEY,
  intended_use_id   uuid NOT NULL REFERENCES intended_use(id) ON DELETE CASCADE,
  purpose_idx       integer NOT NULL,
  lang              text NOT NULL,
  content           text NOT NULL,
  UNIQUE (intended_use_id, purpose_idx, lang)
);

CREATE INDEX IF NOT EXISTS intended_use_purposes_iu_fk_idx ON intended_use_purposes (intended_use_id);

-- Intended Use privacy policies (array)
CREATE TABLE intended_use_policies (
  id              bigserial PRIMARY KEY,
  intended_use_id uuid NOT NULL REFERENCES intended_use(id) ON DELETE CASCADE,
  type            bigserial NOT NULL REFERENCES strings(id),
  policy_uri      text NOT NULL
);

CREATE INDEX IF NOT EXISTS intended_use_policies_iu_fk_idx ON intended_use_policies (intended_use_id);
CREATE INDEX IF NOT EXISTS intended_use_policies_uri_idx ON intended_use_policies (policy_uri);

-- 5) Credentials and Claims inside Intended Use
CREATE TABLE credential (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  intended_use_id uuid NOT NULL REFERENCES intended_use(id) ON DELETE CASCADE,
  format          text NOT NULL,           -- e.g. 'sd-jwt vc', 'mdoc'
  meta            text,                    -- aligns with ts5 OpenAPI meta:string
  meta_text       text                     -- optional denormalized text for search
);

CREATE INDEX IF NOT EXISTS credential_iu_fk_idx ON credential (intended_use_id);
CREATE INDEX IF NOT EXISTS credential_format_idx ON credential (format);
CREATE INDEX IF NOT EXISTS credential_meta_trgm_idx ON credential USING gin (meta gin_trgm_ops);

CREATE TABLE claim (
  id            bigserial PRIMARY KEY,
  credential_id uuid NOT NULL REFERENCES credential(id) ON DELETE CASCADE,
  path          text NOT NULL,             -- e.g. 'IBAN'
  values        jsonb                      -- array/scalar (strings/ints/bools)
);

CREATE INDEX IF NOT EXISTS claim_path_btree_idx ON claim (path);
CREATE INDEX IF NOT EXISTS claim_path_trgm_idx  ON claim USING gin (lower(path) gin_trgm_ops);
CREATE INDEX IF NOT EXISTS claim_credential_fk_idx ON claim (credential_id);

-- 6) History (JSONB snapshots; validity-based)
CREATE TABLE wrp_history (
  id          bigserial PRIMARY KEY,
  wrp_id      uuid NOT NULL REFERENCES wrp(id) ON DELETE CASCADE,
  valid_from  date NOT NULL,
  valid_until date NOT NULL,
  snapshot    bytea NOT NULL,
  ipfs_cid    text                    -- IPFS CID of published history snapshot
);

CREATE INDEX IF NOT EXISTS wrp_history_wrp_valid_idx ON wrp_history (wrp_id, valid_until);

-- Partial index: efficiently find unpublished history entries
CREATE INDEX IF NOT EXISTS idx_wrp_history_unpublished ON wrp_history(wrp_id, valid_from)
    WHERE ipfs_cid IS NULL;

Querying patterns

  • Combined filter: claim path + country + name search (fuzzy)
SELECT DISTINCT iu.*
FROM intended_use iu
JOIN wrp w            ON w.id = iu.wrp_id
JOIN legal_entity le  ON le.id = w.id              -- wrp.id = legal_entity.id
JOIN credential cr    ON cr.intended_use_id = iu.id
JOIN claim c          ON c.credential_id = cr.id
WHERE lower(c.path) = lower(\$1)           -- claim path (e.g., 'IBAN')
  AND le.country = \$2                     -- country code (e.g., 'DE')
  AND lower(w.trade_name) ILIKE lower(\$3) -- name search (e.g., '%acme%')
ORDER BY iu.created_at DESC;
  • Claim path only (exact or fuzzy using trigram)
-- exact/normalized
SELECT iu.*
FROM intended_use iu
WHERE EXISTS (
  SELECT 1
  FROM credential cr
  JOIN claim c ON c.credential_id = cr.id
  WHERE cr.intended_use_id = iu.id
    AND lower(c.path) = lower(\$1)
)
ORDER BY iu.created_at DESC;

-- fuzzy (trigram search)
SELECT DISTINCT iu.*
FROM intended_use iu
JOIN credential cr ON cr.intended_use_id = iu.id
JOIN claim c ON c.credential_id = cr.id
WHERE lower(c.path) ILIKE lower(\$1)
ORDER BY iu.created_at DESC;
-- \$1 example: '%iban%'
  • Country only
SELECT iu.*
FROM intended_use iu
JOIN wrp w ON w.id = iu.wrp_id
JOIN legal_entity le ON le.id = w.id    -- wrp.id = legal_entity.id
WHERE le.country = \$1
ORDER BY iu.created_at DESC;
-- \$1 example: 'DE'
  • Name search (trade name, trigram fuzzy search)
SELECT DISTINCT iu.*
FROM intended_use iu
JOIN wrp w ON w.id = iu.wrp_id
WHERE lower(w.trade_name) ILIKE lower(\$1)
ORDER BY iu.created_at DESC;
-- \$1 example: '%ministry%'
  • Filter by credential format and meta
SELECT iu.*
FROM intended_use iu
JOIN credential cr ON cr.intended_use_id = iu.id
WHERE cr.format = \$1
  AND (cr.meta_text ILIKE \$2 OR cr.meta ILIKE \$2)
ORDER BY iu.created_at DESC;
-- \$1 example: 'sd-jwt vc'
-- \$2 example: '%BankAccount%'
  • Filter by entitlement URI
SELECT DISTINCT iu.*
FROM intended_use iu
JOIN wrp w ON w.id = iu.wrp_id
JOIN wrp_entitlements we ON we.wrp_id = w.id
JOIN strings s ON s.id = we.entitlement
WHERE s.value = \$1
ORDER BY iu.created_at DESC;
-- \$1 example: 'urn:eudi:cir:role:psp'
  • Filter by PSB or intermediary status
SELECT iu.*
FROM intended_use iu
JOIN wrp w ON w.id = iu.wrp_id
WHERE w.is_psb = \$1
  AND w.is_intermediary = \$2
ORDER BY iu.created_at DESC;
  • Get WRP with its legal entity and supervisory authority
SELECT 
  w.*,
  le.country as wrp_country,
  le.email as wrp_email,
  le.phone as wrp_phone,
  sa.country as supervisory_authority_country,
  sa.email as supervisory_authority_email
FROM wrp w
JOIN legal_entity le ON le.id = w.id                        -- WRP's own legal entity
LEFT JOIN legal_entity sa ON sa.id = w.supervisory_authority_id  -- Supervisory authority
WHERE w.id = \$1;
  • Combined filter with all common use cases
SELECT DISTINCT iu.*
FROM intended_use iu
JOIN wrp w ON w.id = iu.wrp_id
JOIN legal_entity le ON le.id = w.id              -- wrp.id = legal_entity.id
LEFT JOIN credential cr ON cr.intended_use_id = iu.id
LEFT JOIN claim c ON c.credential_id = cr.id
WHERE 1=1
  AND (\$1::text IS NULL OR lower(c.path) = lower(\$1))           -- claim path filter
  AND (\$2::char(2) IS NULL OR le.country = \$2)                  -- country filter
  AND (\$3::text IS NULL OR lower(w.trade_name) ILIKE lower(\$3)) -- name search
  AND (\$4::boolean IS NULL OR w.is_psb = \$4)                    -- PSB filter
  AND (\$5::boolean IS NULL OR w.is_intermediary = \$5)           -- intermediary filter
ORDER BY iu.created_at DESC
LIMIT \$6 OFFSET \$7;

Notes

Performance Considerations

  • Indexes for core filters: The schema provides indexes to support the three main query patterns:

    • Claim path filtering: claim_path_btree_idx and claim_path_trgm_idx for exact and fuzzy searches
    • Country filtering: legal_entity_country_idx for fast country lookups
    • Name search: wrp_trade_name_trgm_idx for fuzzy trade name searches

  • Foreign key indexes: All foreign keys have corresponding indexes for efficient joins:

    • wrp_supervisory_authority_fk_idx for joining WRP to supervisory authority
    • intended_use_wrp_fk_idx, credential_iu_fk_idx, claim_credential_fk_idx for the main query path
    • Note: wrp.id references legal_entity.id as a shared primary key (no separate index needed)

  • String deduplication: The strings table reduces storage for frequently repeated values but adds one join. For highest performance queries, consider denormalizing frequently accessed strings.

  • Query performance: The combined filter query will perform well with proper index usage:

    1. Start with claim path filter using claim_path_btree_idx
    2. Join up to credential → intended_use → wrp → legal_entity using FK indexes
    3. Apply country and name filters using their respective indexes
    4. PostgreSQL query planner should use hash joins for efficiency

History Storage

  • WRP-level only: History is tracked at the WRP level only via wrp_history. Each snapshot contains the full WRP state including all its intended uses at that point in time.
  • Validity-based snapshots: Each snapshot is stored with valid_from and valid_until dates
  • Compressed format: Snapshots stored as bytea (recommend using zstd compression before storage)
  • Minimal indexes: Only (wrp_id, valid_until) indexed, as history queries are less frequent
  • Query pattern: To get state at time T, find the snapshot where valid_from <= T AND valid_until > T

IPFS Publishing

  • wrp.ipfs_cid: CID of the current live WRP snapshot on IPFS. Set by the IPFS publisher after successful upload.
  • wrp.ipfs_claimed_at: Timestamp used as a claim lock for multi-publisher coordination. Set when a publisher claims a WRP, cleared on completion or failure. Claims older than 10 minutes are considered stale and can be reclaimed.
  • wrp_history.ipfs_cid: CID of the published history snapshot. Each history entry is published sequentially to maintain chain linking via previous_cid.
  • idx_wrp_history_unpublished: Partial index on (wrp_id, valid_from) WHERE ipfs_cid IS NULL for efficiently finding unpublished history entries.
  • Migrations: wrp.ipfs_cid exists since 000001. Additional IPFS support added by 000007 (ipfs_cid on wrp_history + partial index), 000008 (ipfs_claimed_at on wrp), 000009 (drop unused ipfs_url columns).

Future Optimizations

If query performance becomes critical at scale, consider:

  • Denormalized claim paths: Add TEXT[] array of claim paths to intended_use table with GIN index, maintained by trigger (similar to old schema)
  • Partitioning: Partition intended_use by hash on wrp_id if table grows beyond 10M rows
  • Materialized views: For complex reporting queries, create materialized views with pre-joined data
  • Full-text search: Add tsvector columns to intended_use_purposes for full-text search on purpose descriptions