Database Schema

PostgreSQL 15 + pgvector. DDL in src/db/schema.ts and migrations/*.sql. Applied via Schema Migrations.

Source: docs/SYSTEM_OVERVIEW.md § Database schema.

Runtime tables

companies

CREATE TABLE companies (
  id            SERIAL PRIMARY KEY,
  "orgNr"       TEXT UNIQUE NOT NULL,
  name          TEXT,
  enriched_data JSONB,       -- full EnrichmentResult stored here
  processed_at  TIMESTAMPTZ
);
CREATE INDEX idx_companies_enriched_gin ON companies USING GIN (enriched_data);

bolagsverket_companies

Bulk import target. See Bolagsverket Import.

scb_foundations

Bulk import target. See SCB Import.

GDPR tables

OptOut_Hashes

SHA-256 hashes only, no plaintext. See Opt-Out Hashes.

RoPA_Log

Append-only audit log. See RoPA Log.

Article14_Notifications

Tracks notification state per collected contact. See Article 14.

CREATE TABLE "Article14_Notifications" (
  id                   SERIAL PRIMARY KEY,
  org_nr               TEXT,
  email                TEXT,
  collected_at         TIMESTAMPTZ,
  notification_sent_at TIMESTAMPTZ,
  status               TEXT,
  retry_count          INTEGER DEFAULT 0
);

Migration tracking

schema_migrations

CREATE TABLE schema_migrations (
  version    TEXT PRIMARY KEY,
  name       TEXT,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

name is nullable for compatibility with legacy migrations (e.g. 006_domain_registry.sql) that insert without supplying it. Source: migrations/000_schema_migrations.sql.

See also

Schema Migrations, Bolagsverket Import, SCB Import, RoPA Log, Opt-Out Hashes, Article 14.