Data Model

:::info Source Sourced from services/certification-service/DATA_MODEL.md in the documentation repo. :::

1. Database

Postgres 16 schema certification. Indefinite retention for certificates (verifiability).

2. Tables

2.1 certificates

CREATE TABLE certification.certificates (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  user_id ULID NOT NULL,
  course_id ULID NOT NULL,
  course_version_id ULID NOT NULL,
  enrollment_id ULID NOT NULL,
  template_id TEXT NOT NULL,
  issued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ,
  state TEXT NOT NULL DEFAULT 'pending_offline_verification'
    CHECK (state IN ('pending_offline_verification','issued','revoked')),
  evidence JSONB NOT NULL,
  proof TEXT NOT NULL,                 -- compact JWS
  proof_kid TEXT NOT NULL,
  artifacts JSONB NOT NULL,
  verification_token TEXT NOT NULL,
  verification_token_fingerprint TEXT NOT NULL,     -- HMAC(verification_token, tenant_secret)
  user_display_name_at_issuance TEXT NOT NULL,      -- snapshot for verification (subject to GDPR policy)
  UNIQUE (tenant_id, enrollment_id, course_version_id, state)   -- effectively idempotent
);
CREATE UNIQUE INDEX certs_by_token ON certification.certificates (verification_token);
CREATE INDEX certs_by_user ON certification.certificates (tenant_id, user_id, issued_at DESC);
CREATE INDEX certs_by_course ON certification.certificates (tenant_id, course_id, issued_at DESC);
CREATE INDEX certs_state ON certification.certificates (tenant_id, state);

2.2 revocation_records

CREATE TABLE certification.revocation_records (
  id ULID PRIMARY KEY,
  certificate_id ULID NOT NULL REFERENCES certification.certificates(id),
  tenant_id UUID NOT NULL,
  revoked_by ULID NOT NULL,
  revoked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  reason TEXT NOT NULL,
  public_reason JSONB
);
CREATE INDEX revocation_cert ON certification.revocation_records (certificate_id);

2.3 certificate_templates

CREATE TABLE certification.certificate_templates (
  id TEXT PRIMARY KEY,
  tenant_id UUID,                    -- NULL for system templates
  name JSONB NOT NULL,
  layout JSONB NOT NULL,
  branding JSONB NOT NULL,
  signatory_blocks JSONB NOT NULL,
  legal_footer JSONB,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived'))
);

2.4 offline_issuance_claims

CREATE TABLE certification.offline_issuance_claims (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  user_id ULID NOT NULL,
  enrollment_id ULID NOT NULL,
  attempt_id ULID NOT NULL,
  local_signature TEXT NOT NULL,
  local_completed_at TIMESTAMPTZ NOT NULL,
  claimed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','verified_issued','rejected')),
  rejection_reason TEXT,
  resulting_certificate_id ULID
);

2.5 verification_audit

CREATE TABLE certification.verification_audit (
  id BIGSERIAL PRIMARY KEY,
  certificate_id ULID NOT NULL,
  tenant_id UUID NOT NULL,
  verified_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  ip_hash TEXT NOT NULL,
  ua_hash TEXT NOT NULL,
  result TEXT NOT NULL           -- issued | revoked | not_found | invalid
);
CREATE INDEX verif_audit_cert ON certification.verification_audit (certificate_id, verified_at DESC);

2.6 outbox, inbox, sagas

Standard.

3. RLS

All tables except verification_audit (which stores hashed IPs, public endpoint has no authenticated tenant) and .well-known/certification-keys:

ALTER TABLE certification.certificates ENABLE ROW LEVEL SECURITY;
CREATE POLICY certs_tenant_iso ON certification.certificates
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Public /verify bypasses RLS via SECURITY DEFINER function that narrows reads to non-PII columns.

4. Indexing Strategy

• verification_token UNIQUE (primary lookup for public verify).
• (tenant_id, user_id, issued_at DESC) for learner portfolio.
• (tenant_id, course_id, issued_at DESC) for course certifications list.
• state filter (for admin "revoked" filter).

5. Partitioning

verification_audit partitioned monthly (high write). Retained 13 months.

6. Retention

• certificates: indefinite (verifiability); GDPR anonymization per tenant policy.
• verification_audit: 13 months then archive to S3.
• offline_issuance_claims: 7 years (compliance).

7. Migration

• Additive columns safe.
• Proof schema changes require dual-sign window + verification library update.

8. Performance

• Public verify: cached by CDN per certificate_id, invalidated on revoke.
• List endpoints: cursor on issued_at.
• Artifact URLs stored short-lived; re-signed on demand.