Database Schema Complete

30+ tables, 50+ indexes, 9 migrations

This is the complete catalog of every table, index, constraint, and relationship in the EnrichNode database.

Core Pipeline Tables

companies

Primary enriched data store.

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
orgNr	TEXT	UNIQUE NOT NULL
name	TEXT
address	TEXT
sni	TEXT[]
enriched_data	JSONB	Stores full EnrichmentResult including contacts (PII)
processed_at	TIMESTAMPTZ
source	TEXT	DEFAULT ‘pipeline’
domain	TEXT	(added migration 002)
lead_score	NUMERIC	(added migration 002)
contacts_count	INTEGER	(added migration 002)
has_vd_contact	BOOLEAN	(added migration 002)
last_enriched_at	TIMESTAMPTZ	(added migration 002)
enrichment_tier	TEXT	CHECK: active/stable/dormant (migration 003)
next_enrichment_after	TIMESTAMPTZ	(migration 003)
data_fingerprint	TEXT	(migration 003)
enrichment_run_id	TEXT	(migration 003)
tier_assigned_at	TIMESTAMPTZ	(migration 003)
turnover_sek	BIGINT	(migration 005)
employee_count	INTEGER	(migration 005)
equity_sek	BIGINT	(migration 005)
bv_financial_year	INTEGER	(migration 005)
bv_financial_fetched_at	TIMESTAMPTZ	(migration 005)
embedding	VECTOR(1536)	(migration 007)

Indexes:

• idx_companies_enriched_gin — GIN on enriched_data
• idx_companies_needs_refresh — B-tree on processed_at NULLS FIRST
• idx_companies_lead_score — B-tree on lead_score DESC NULLS LAST
• idx_companies_has_vd — B-tree on has_vd_contact
• idx_companies_domain — B-tree on domain
• idx_companies_last_enriched — B-tree on last_enriched_at DESC NULLS LAST
• idx_companies_tier — B-tree on (enrichment_tier, next_enrichment_after NULLS FIRST)
• idx_companies_next_enrich — B-tree on next_enrichment_after NULLS FIRST WHERE NOT NULL
• idx_companies_fingerprint — B-tree on data_fingerprint
• idx_companies_embedding — IVFFlat on embedding vector_cosine_ops (lists=100)

leads

Sales-ready leads with validation layers.

Column	Type	Constraints
id	UUID	PRIMARY KEY
org_nr	TEXT	UNIQUE
name	TEXT
legal_form	TEXT
address	TEXT
sni_code	TEXT
sni_secondary	TEXT[]
business_desc	TEXT
employees	INTEGER
turnover	BIGINT
is_active	BOOLEAN
is_validated	BOOLEAN
confidence_score	NUMERIC(3,2)
validated_at	TIMESTAMPTZ
layer_registry	BOOLEAN
layer_website	BOOLEAN
layer_profiles	BOOLEAN
layer_news	BOOLEAN
website	TEXT
linkedin_url	TEXT
email	TEXT
phone	TEXT
source	TEXT
imported_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Bulk Import Tables

bolagsverket_companies

~2.9M records (651K active AB post-archive).

Column	Type
id	SERIAL PK
org_nr	TEXT UNIQUE
name	TEXT
legal_form	TEXT
registration_date	DATE
deregistration_date	DATE
deregistration_reason	TEXT
business_description	TEXT
address	TEXT
postal_code	TEXT
postal_city	TEXT
country	TEXT
is_active	BOOLEAN DEFAULT true
data_source	TEXT
imported_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

scb_foundations

~1.8M records (post-archive: only AB).

Column	Type
id	SERIAL PK
org_nr	TEXT UNIQUE
name	TEXT
address	TEXT
postal_code	TEXT
postal_city	TEXT
co_address	TEXT
legal_form	INTEGER
status	INTEGER
entity_status	INTEGER
registration_date	DATE
sni_codes	TEXT[]
sni_primary	TEXT
advertising_block	BOOLEAN
data_source	TEXT
imported_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Compliance Tables

OptOut_Hashes

SHA-256 hashed contacts for opt-out.

CREATE TABLE "OptOut_Hashes" (
    hashed_contact TEXT PRIMARY KEY
);

RoPA_Log

Immutable audit trail (Article 30).

CREATE TABLE "RoPA_Log" (
    id          SERIAL PRIMARY KEY,
    timestamp   TIMESTAMPTZ DEFAULT NOW(),
    "tableName" TEXT NOT NULL,
    operation   TEXT NOT NULL,
    "recordId"  TEXT,
    description TEXT
);

Migration 002 adds: month_bucket column for efficient querying.

Article14_Notifications

GDPR Art. 14 notification log.

CREATE TABLE "Article14_Notifications" (
    id              SERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    company_org_nr  TEXT,
    sent_at         TIMESTAMPTZ DEFAULT NOW(),
    method          TEXT DEFAULT 'email',
    status          TEXT DEFAULT 'sent',  -- sent | failed | skipped
    error_message   TEXT,
    UNIQUE (email, company_org_nr)
);

Quality & Error Tracking

enrichment_errors

Column	Type
id	SERIAL PK
org_nr	TEXT
timestamp	TIMESTAMPTZ
error_type	TEXT
error_message	TEXT
stack_trace	TEXT
retry_count	INTEGER
resolved	BOOLEAN
metadata	JSONB

enrichment_issues

Column	Type	Constraints
id	SERIAL PK
org_nr	TEXT
issue_type	TEXT
severity	TEXT	CHECK: low/medium/high/critical
description	TEXT
detected_at	TIMESTAMPTZ
status	TEXT	CHECK: open/in_progress/resolved/flagged_manual
resolution	TEXT
resolved_at	TIMESTAMPTZ
auto_remediation_attempts	INTEGER
last_auto_attempt_at	TIMESTAMPTZ

remediation_actions

Column	Type
id	SERIAL PK
org_nr	TEXT
action_type	TEXT
issue_id	INTEGER FK → enrichment_issues (ON DELETE SET NULL)
success	BOOLEAN
details	JSONB
executed_at	TIMESTAMPTZ

Scale Infrastructure

enrichment_queue_state

Column	Type
id	SERIAL PK
batch_id	TEXT UNIQUE
dispatched_at	TIMESTAMPTZ
batch_size	INTEGER
status	TEXT
completed_at	TIMESTAMPTZ
orgs_enriched	INTEGER
orgs_failed	INTEGER

enrichment_run_log

Column	Type	Constraints
id	SERIAL PK
run_id	TEXT UNIQUE
run_type	TEXT	CHECK: canary/quarterly/manual/delta
quarter	TEXT
started_at	TIMESTAMPTZ
completed_at	TIMESTAMPTZ
status	TEXT	CHECK: running/completed/failed/aborted
companies_targeted	INTEGER
companies_dispatched	INTEGER
companies_completed	INTEGER
companies_error	INTEGER
companies_blocked	INTEGER
companies_skipped	INTEGER
p50_latency_ms	INTEGER
p95_latency_ms	INTEGER
cost_estimate_usd	NUMERIC
serper_calls	INTEGER
hunter_calls	INTEGER
playwright_calls	INTEGER
error_rate_pct	NUMERIC
notes	TEXT

enrichment_history

Column	Type
id	SERIAL PK
org_nr	TEXT
run_id	TEXT FK → enrichment_run_log
enriched_at	TIMESTAMPTZ
duration_ms	INTEGER
status	TEXT
lead_score_then	NUMERIC
contacts_then	INTEGER
fingerprint	TEXT
api_calls_json	JSONB

api_circuit_state

Column	Type	Constraints
api_name	TEXT PK
state	TEXT	CHECK: closed/open/half-open
failure_count	INTEGER
last_failure_at	TIMESTAMPTZ
opened_at	TIMESTAMPTZ
reset_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Archive Tables (Migration 009)

Table	Purpose
bolagsverket_companies_archive	Archived inactive/non-AB BV companies
scb_foundations_archive	Archived inactive/non-AB SCB companies
archive_audit_log	Archive operation audit trail

Multi-Tenancy

organizations

Column	Type
id	UUID PK
name	TEXT
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

users

Column	Type
id	UUID PK
organization_id	UUID FK → organizations
email	TEXT UNIQUE
password_hash	TEXT
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

projects

Column	Type
id	UUID PK
organization_id	UUID FK → organizations
name	TEXT
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

documents

Column	Type
id	UUID PK
project_id	UUID FK → projects
name	TEXT
content	TEXT
embedding	VECTOR(1536)
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Contact Mapping (contact-mapping-schema.sql)

swedish_company_officials

Column	Type	Constraints
id	SERIAL PK
org_nr	TEXT
person_name	TEXT
role_type	TEXT
role_title	TEXT
source	TEXT
valid_from	DATE
valid_to	DATE
UNIQUE	(org_nr, person_name, role_type)

contact_discovery_log

Column	Type	Constraints
id	SERIAL PK
org_nr	TEXT
person_name	TEXT
discovery_source	TEXT
contact_type	TEXT
contact_value	TEXT
confidence_score	INTEGER	CHECK: 0-100
verified	BOOLEAN
discovered_at	TIMESTAMPTZ
UNIQUE	(org_nr, person_name, discovery_source, contact_type)

domain_email_patterns

Column	Type	Constraints
domain	TEXT PK
detected_pattern	TEXT
sample_size	INTEGER
confidence_score	INTEGER	CHECK: 0-100
first_seen_at	TIMESTAMPTZ
last_verified_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

person_phone_mappings

Column	Type	Constraints
id	SERIAL PK
person_name	TEXT
org_nr	TEXT
phone_number	TEXT
phone_type	TEXT	CHECK: mobile/landline/company_main/unknown
source	TEXT
confidence_score	INTEGER
is_primary	BOOLEAN
UNIQUE	(org_nr, person_name, phone_number)

mapped_contacts

Column	Type	Constraints
id	SERIAL PK
org_nr	TEXT FK → companies(“orgNr”)
person_name	TEXT
first_name	TEXT
last_name	TEXT
position_title	TEXT
position_role_type	TEXT	CHECK: VD/CFO/CMO/CTO/Försäljningschef/Marknadschef/Styrelseordförande/Styrelseledamot/Medarbetare/Övrig
position_seniority	TEXT	CHECK: C-Level/VP/Director/Manager/Staff
email	TEXT
phone	TEXT
linkedin_url	TEXT
data_sources	TEXT[]
enrichment_confidence	NUMERIC
UNIQUE	(org_nr, person_name)

eniro_cache

Column	Type	Constraints
id	SERIAL PK
search_query	TEXT
search_type	TEXT	CHECK: company/person
results	JSONB
result_count	INTEGER
cached_at	TIMESTAMPTZ
expires_at	TIMESTAMPTZ
UNIQUE	(search_query, search_type)

Webhooks

webhooks

Column	Type
id	UUID PK
url	TEXT
events	webhook_event[]
secret	TEXT
active	BOOLEAN
created_at	TIMESTAMPTZ
last_triggered	TIMESTAMPTZ
failure_count	INTEGER

webhook_deliveries

Column	Type	Constraints
id	UUID PK
webhook_id	UUID FK → webhooks (ON DELETE CASCADE)
event	TEXT
payload	JSONB
status	TEXT	CHECK: pending/delivered/failed
response_status	INTEGER
response_body	TEXT
error_message	TEXT
retry_count	INTEGER
created_at	TIMESTAMPTZ
delivered_at	TIMESTAMPTZ

Merge & Migration

merged_companies

Smart merge of SCB + Bolagsverket data.

schema_migrations

Column	Type
version	TEXT PK
name	TEXT
applied_at	TIMESTAMPTZ DEFAULT NOW()

domain_registry

Column	Type
domain	TEXT PK
normalized_name	TEXT
loaded_at	TIMESTAMPTZ

prv_trademarks

Column	Type
id	SERIAL PK
org_nr	TEXT
trademark_name	TEXT
status	TEXT
registration_no	TEXT
registration_date	DATE
nice_classes	INTEGER[]
imported_at	TIMESTAMPTZ

Migration History

Version	File	What Was Added
000	000_schema_migrations.sql	Migration tracking table
001	001_add_quality_tracking.sql	enrichment_issues, remediation_actions, quality fields
002	002_enrichment_scale.sql	companies.domain, lead_score, enrichment_queue_state, 6 indexes
003	003_quarterly_scale.sql	enrichment_tier, enrichment_run_log, enrichment_history, 3 indexes
004	004_circuit_state.sql	api_circuit_state table
005	005_financial_fields.sql	turnover_sek, employee_count, equity_sek
006	006_domain_registry.sql	pg_trgm, domain_registry table
007	007_pgvector_company_embeddings.sql	embedding VECTOR(1536), IVFFlat index
008	008_prv_trademarks.sql	prv_trademarks table
009	009_add_archive_tables.sql	Archive tables for inactive companies