Data Model Reference

Overview

PensionPortal.ai uses PostgreSQL (hosted on Neon serverless) with Drizzle ORM for all data access. This document is the authoritative reference for the database schema, entity relationships, data classifications, and retention policies.

Property	Value
Database	PostgreSQL 16 (Neon serverless)
ORM	Drizzle ORM (TypeScript)
Primary key strategy	CUID2 (collision-resistant, URL-safe, sortable)
Soft delete strategy	Status enums (FSM-based) — no `deletedAt` columns
Temporal tracking	`createdAt` and `updatedAt` on all tables
Vector extension	pgvector (1536-dimensional embeddings for RAG)
Tenant isolation	Application-layer via `ActorContext` (no RLS)

Entity Relationship Summary

brokers
  └── users (brokerId FK)
  └── employers (brokerId FK)
        └── users (employerId FK) — EmployerAdmin / EmployerUser
        └── schemes (employerId FK)
              ├── members (schemeId + employerId FK)
              │     └── contributionRecords (memberId FK)
              │     └── fundChoices (memberId FK)
              ├── keyFunctionHolders (schemeId FK)
              ├── writtenPolicies (schemeId FK)
              ├── oraReports (schemeId FK)
              ├── healthCheckAssessments (schemeId FK)
              ├── acsWizard (schemeId FK)
              └── documents (schemeId FK)

providers
  └── schemes (providerId FK)

ragCorpus (standalone — no FK to tenant data)
audit_logs (standalone — references actorId + entityId by convention, no FK)

Table Descriptions

`users`

Stores all authenticated principals. The brokerId column is the tenant anchor for broker-level users.

const users = pgTable("users", {
  id:           text("id").primaryKey().$defaultFn(() => createId()),
  email:        text("email").notNull().unique(),
  passwordHash: text("password_hash").notNull(),
  role:         roleEnum("role").notNull(),
  brokerId:     text("broker_id").references(() => brokers.id),
  employerId:   text("employer_id").references(() => employers.id),
  fullName:     text("full_name"),
  createdAt:    timestamp("created_at").defaultNow().notNull(),
  updatedAt:    timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`id`	text (CUID2)	Primary key
`email`	text	Unique, used as login identifier
`passwordHash`	text	bcryptjs hash (work factor 12) — never plaintext
`role`	enum	`SuperAdmin \| BrokerAdmin \| BrokerUser \| EmployerAdmin \| EmployerUser \| Member`
`brokerId`	text (FK)	Tenant anchor — set at creation, immutable
`employerId`	text (FK)	Populated for EmployerAdmin / EmployerUser / Member
`fullName`	text	Display name
`createdAt`	timestamp	UTC, server-assigned
`updatedAt`	timestamp	UTC, updated on every write

`password_reset_tokens`

Stores hashed password reset tokens for the forgot-password flow. Tokens are SHA-256 hashed before storage — plaintext is never persisted.

const passwordResetTokens = pgTable("password_reset_tokens", {
  id:        text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId:    text("user_id").notNull(),
  tokenHash: text("token_hash").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  usedAt:    timestamp("used_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
});

Column	Type	Notes
`id`	text (UUID)	Primary key
`userId`	text (FK)	References `users.id`
`tokenHash`	text	SHA-256 hash of the plaintext token
`expiresAt`	timestamp	Token expiry (60 minutes from creation)
`usedAt`	timestamp	Set when token is consumed (one-time use)
`createdAt`	timestamp	UTC, server-assigned

`employers`

The primary tenant-scoped entity. Every employer belongs to exactly one broker tenant.

const employers = pgTable("employers", {
  id:                 text("id").primaryKey().$defaultFn(() => createId()),
  brokerId:           text("broker_id").notNull().references(() => brokers.id),
  name:               text("name").notNull(),
  registrationNumber: text("registration_number"),
  sector:             text("sector"),
  payrollCycle:       payrollCycleEnum("payroll_cycle"),
  brand:              jsonb("brand"),   // { primaryColor, logoUrl, ... }
  stats:              jsonb("stats"),   // cached aggregate counts
  createdAt:          timestamp("created_at").defaultNow().notNull(),
  updatedAt:          timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`id`	text (CUID2)	Primary key
`brokerId`	text (FK)	Non-nullable tenant anchor
`name`	text	Employer legal name
`registrationNumber`	text	CRO / Companies Registration Office number
`sector`	text	Industry sector (NACE code or free text)
`payrollCycle`	enum	`weekly \| fortnightly \| monthly \| quarterly`
`brand`	JSONB	Whitelabel branding: `{ primaryColor, logoUrl, tradingName }`
`stats`	JSONB	Cached counts: `{ memberCount, schemeCount, activeKfhCount }`

`providers`

Pension product providers (insurers, fund managers). Not tenant-scoped — shared reference data.

const providers = pgTable("providers", {
  id:               text("id").primaryKey().$defaultFn(() => createId()),
  slug:             text("slug").notNull().unique(),
  displayName:      text("display_name").notNull(),
  submissionFormat: text("submission_format"), // e.g. "xml_v2", "csv_iol"
  contactName:      text("contact_name"),
  contactEmail:     text("contact_email"),
  contactPhone:     text("contact_phone"),
  createdAt:        timestamp("created_at").defaultNow().notNull(),
  updatedAt:        timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`slug`	text	URL-safe identifier (e.g. `irish-life`, `zurich-ireland`)
`submissionFormat`	text	Format for contribution file uploads

`schemes`

A pension scheme belongs to one employer and uses one provider. The status column implements a finite state machine.

const schemes = pgTable("schemes", {
  id:                          text("id").primaryKey().$defaultFn(() => createId()),
  employerId:                  text("employer_id").notNull().references(() => employers.id),
  providerId:                  text("provider_id").references(() => providers.id),
  name:                        text("name").notNull(),
  type:                        schemeTypeEnum("type").notNull(),
  status:                      schemeStatusEnum("status").notNull().default("Draft"),
  revenueApprovalNumber:       text("revenue_approval_number"),
  trustDeedDate:               date("trust_deed_date"),
  establishmentDate:           date("establishment_date"),
  normalRetirementAge:         integer("normal_retirement_age"),
  payrollCycle:                payrollCycleEnum("payroll_cycle"),
  salaryDefinition:            text("salary_definition"),
  omaFlag:                     boolean("oma_flag").default(false),
  establishedBeforeDerogation: boolean("established_before_derogation").default(false),
  transitionDecision:          text("transition_decision"),
  createdAt:                   timestamp("created_at").defaultNow().notNull(),
  updatedAt:                   timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`type`	enum	`DB \| DC \| PRSA \| RAC \| SmallSelf \| Executive`
`status`	enum	FSM — see Scheme Status FSM
`revenueApprovalNumber`	text	Issued by Irish Revenue Commissioners on approval
`omaFlag`	boolean	One-Member Arrangement — triggers different IORP II obligations
`establishedBeforeDerogation`	boolean	Affects IORP II transitional provisions
`transitionDecision`	text	OMA transition decision text

`members`

Individual scheme members. PPS numbers are stored encrypted (AES-256-GCM). Status implements a FSM.

const members = pgTable("members", {
  id:                   text("id").primaryKey().$defaultFn(() => createId()),
  employerId:           text("employer_id").notNull().references(() => employers.id),
  schemeId:             text("scheme_id").notNull().references(() => schemes.id),
  firstName:            text("first_name").notNull(),
  lastName:             text("last_name").notNull(),
  dateOfBirth:          date("date_of_birth"),
  ppsNumberEncrypted:   text("pps_number_encrypted"), // AES-256-GCM ciphertext
  email:                text("email"),
  phone:                text("phone"),
  status:               memberStatusEnum("status").notNull().default("PendingEnrolment"),
  currentFundChoiceId:  text("current_fund_choice_id"),
  employeeContribRate:  numeric("employee_contrib_rate"),  // percentage
  employerContribRate:  numeric("employer_contrib_rate"),  // percentage
  createdAt:            timestamp("created_at").defaultNow().notNull(),
  updatedAt:            timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`ppsNumberEncrypted`	text	AES-256-GCM ciphertext — never plaintext. See Security Architecture
`status`	enum	FSM — see Member Status FSM
`currentFundChoiceId`	text (FK)	Active fund allocation
`employeeContribRate`	numeric	Percentage of salary — e.g. `5.00` for 5%
`employerContribRate`	numeric	Percentage of salary

`keyFunctionHolders`

Records the IORP II Key Function Holders (KFHs) appointed to a scheme. Pensions Authority notification status is tracked.

const keyFunctionHolders = pgTable("key_function_holders", {
  id:                      text("id").primaryKey().$defaultFn(() => createId()),
  schemeId:                text("scheme_id").notNull().references(() => schemes.id),
  kfhType:                 kfhTypeEnum("kfh_type").notNull(),
  holderName:              text("holder_name").notNull(),
  holderEmail:             text("holder_email"),
  appointmentDate:         date("appointment_date"),
  paNotificationStatus:    text("pa_notification_status"), // "pending" | "submitted" | "acknowledged"
  fitAndProperAssessedAt:  timestamp("fit_and_proper_assessed_at"),
  status:                  text("status").notNull().default("active"), // "active" | "resigned" | "removed"
  createdAt:               timestamp("created_at").defaultNow().notNull(),
  updatedAt:               timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Notes
`kfhType`	enum	`risk_management \| internal_audit \| actuarial`
`paNotificationStatus`	text	Pensions Authority notification workflow state
`fitAndProperAssessedAt`	timestamp	Date of Fit & Proper assessment under IORP II

`writtenPolicies`

Tracks the 11 written policies required by IORP II. Each policy record stores its review status and RAG rating.

const writtenPolicies = pgTable("written_policies", {
  id:             text("id").primaryKey().$defaultFn(() => createId()),
  schemeId:       text("scheme_id").notNull().references(() => schemes.id),
  type:           policyTypeEnum("type").notNull(),
  status:         policyStatusEnum("status").notNull().default("Draft"),
  effectiveDate:  date("effective_date"),
  lastReviewedAt: date("last_reviewed_at"),
  nextReviewDue:  date("next_review_due"),
  ragStatus:      ragStatusEnum("rag_status").default("Red"),
  createdAt:      timestamp("created_at").defaultNow().notNull(),
  updatedAt:      timestamp("updated_at").defaultNow().notNull(),
});

Policy types (policyTypeEnum):

investment_policy | risk_management_policy | internal_control_policy |
internal_audit_policy | actuarial_policy | outsourcing_policy |
remuneration_policy | business_continuity_policy | communications_policy |
conflict_of_interest_policy | esc_policy

Column	Type	Notes
`ragStatus`	enum	`Red \| Amber \| Green` — overall compliance status
`nextReviewDue`	date	Triggers dashboard alerts when overdue

`oraReports`

Own Risk Assessment (ORA) reports. Each ORA covers 7 risk categories, each with likelihood, impact, rating, and mitigation fields.

const oraReports = pgTable("ora_reports", {
  id:               text("id").primaryKey().$defaultFn(() => createId()),
  schemeId:         text("scheme_id").notNull().references(() => schemes.id),
  assessmentYear:   integer("assessment_year").notNull(),
  status:           oraStatusEnum("status").notNull().default("Draft"),

  // 7 risk categories — each with likelihood/impact/rating/mitigation
  investmentLikelihood:    text("investment_likelihood"),
  investmentImpact:        text("investment_impact"),
  investmentRating:        text("investment_rating"),
  investmentMitigation:    text("investment_mitigation"),

  operationalLikelihood:   text("operational_likelihood"),
  operationalImpact:       text("operational_impact"),
  operationalRating:       text("operational_rating"),
  operationalMitigation:   text("operational_mitigation"),

  // ... (repeated for: compliance, governance, counterparty, liquidity, esg)

  nextOraDate:  date("next_ora_date"),
  signedOffBy:  text("signed_off_by"), // users.id of the trustee who signed off
  createdAt:    timestamp("created_at").defaultNow().notNull(),
  updatedAt:    timestamp("updated_at").defaultNow().notNull(),
});

The 7 ORA risk categories are: investment, operational, compliance, governance, counterparty, liquidity, ESG.

`healthCheckAssessments`

Scheme health check assessments with 7 RAG ratings and an AI-generated executive summary.

const healthCheckAssessments = pgTable("health_check_assessments", {
  id:                 text("id").primaryKey().$defaultFn(() => createId()),
  schemeId:           text("scheme_id").notNull().references(() => schemes.id),
  assessmentDate:     date("assessment_date").notNull(),
  status:             assessmentStatusEnum("status").notNull().default("Draft"),

  // 7 RAG ratings
  governanceRag:      ragStatusEnum("governance_rag"),
  investmentRag:      ragStatusEnum("investment_rag"),
  complianceRag:      ragStatusEnum("compliance_rag"),
  communicationsRag:  ragStatusEnum("communications_rag"),
  administrationRag:  ragStatusEnum("administration_rag"),
  riskManagementRag:  ragStatusEnum("risk_management_rag"),
  financialRag:       ragStatusEnum("financial_rag"),

  executiveSummary:   text("executive_summary"),    // AI-generated draft
  remediationActions: jsonb("remediation_actions"), // [{ action, owner, dueDate, status }]
  createdAt:          timestamp("created_at").defaultNow().notNull(),
  updatedAt:          timestamp("updated_at").defaultNow().notNull(),
});

`acsWizard`

Annual Compliance Statement (ACS) wizard completion tracking. IORP II requires trustees to submit an ACS annually. Dual trustee sign-off fields enforce the two-signatory requirement.

const acsWizard = pgTable("acs_wizard", {
  id:                  text("id").primaryKey().$defaultFn(() => createId()),
  schemeId:            text("scheme_id").notNull().references(() => schemes.id),
  complianceYear:      integer("compliance_year").notNull(),
  status:              acsStatusEnum("status").notNull().default("NotStarted"),
  answers:             jsonb("answers"),             // wizard step answers keyed by question ID
  trustee1Id:          text("trustee_1_id"),         // users.id
  trustee1SignedAt:    timestamp("trustee_1_signed_at"),
  trustee2Id:          text("trustee_2_id"),         // users.id
  trustee2SignedAt:    timestamp("trustee_2_signed_at"),
  submittedToPA:       boolean("submitted_to_pa").default(false),
  paSubmittedAt:       timestamp("pa_submitted_at"),
  createdAt:           timestamp("created_at").defaultNow().notNull(),
  updatedAt:           timestamp("updated_at").defaultNow().notNull(),
});

`auditLogs`

Append-only audit trail. No application-layer DELETE or UPDATE operations. Required for IORP II regulatory examination.

const auditLogs = pgTable("audit_logs", {
  id:            text("id").primaryKey().$defaultFn(() => createId()),
  actorId:       text("actor_id").notNull(),   // users.id (no FK — preserved after user deletion)
  actorType:     text("actor_type").notNull(), // role at time of action
  action:        text("action").notNull(),     // e.g. "scheme.created", "member.pps_decrypted"
  entityType:    text("entity_type"),          // e.g. "scheme", "member"
  entityId:      text("entity_id"),            // PK of affected entity
  previousState: jsonb("previous_state"),      // state before change (null for creates)
  newState:      jsonb("new_state"),           // state after change (null for deletes)
  ipAddress:     text("ip_address"),
  userAgent:     text("user_agent"),
  timestamp:     timestamp("timestamp").defaultNow().notNull(),
});

actorId is stored as plain text rather than a foreign key. This ensures audit records survive user deletion — a regulatory requirement.

`ragCorpus`

Regulatory document chunks for pgvector RAG. Contains only public regulatory text — no member or scheme data.

const ragCorpus = pgTable("rag_corpus", {
  id:           text("id").primaryKey().$defaultFn(() => createId()),
  sourceTitle:  text("source_title").notNull(),   // e.g. "S.I. 128/2021 — IORP II"
  chunkText:    text("chunk_text").notNull(),      // the actual regulatory text chunk
  embedding:    vector("embedding", { dimensions: 1536 }), // OpenAI text-embedding-3-small
  createdAt:    timestamp("created_at").defaultNow().notNull(),
});

Column	Type	Notes
`embedding`	vector(1536)	Requires `pgvector` extension. HNSW index on this column.
`sourceTitle`	text	Displayed in AI citations

Data Classification Table

Field	Table	Classification	Protection Measures
`ppsNumberEncrypted`	members	PII — Highly Sensitive	AES-256-GCM encryption at rest; access audit logged; never sent to AI
`dateOfBirth`	members	PII — Personal	HTTPS in transit; access role-scoped; audit logged
`email`	users, members	PII — Personal	HTTPS in transit; access role-scoped
`firstName`, `lastName`	members	PII — Personal	HTTPS in transit; access role-scoped
`phone`	members	PII — Personal	HTTPS in transit; access role-scoped
`passwordHash`	users	Credential	bcryptjs hashed; never logged or returned via API
`previousState`, `newState`	audit_logs	Operational	Append-only; SuperAdmin / BrokerAdmin read only
Scheme compliance data	schemes, writtenPolicies, oraReports	Operational	Role-scoped; tenant-isolated
RAG corpus	ragCorpus	Regulatory (public)	No classification required — public regulatory text
`embedding`	ragCorpus	Operational	No PII content — derived from public text

FSM Status Enums

Scheme Status FSM

Draft
  └─► PendingRevenueApproval   (awaiting Revenue Commissioners approval)
        └─► Active              (approved and live)
              ├─► UnderReview   (regulatory review in progress)
              │     └─► Active  (review completed — returns to Active)
              └─► WindingUp     (scheme closure initiated)
                    ├─► WoundUp (closure complete)
                    └─► Closed  (administrative close — no wind-up process)

Member Status FSM

PendingEnrolment
  └─► Active                 (enrolled and contributing)
        ├─► Deferred          (left employer, preserved benefits)
        ├─► Retired           (in drawdown)
        ├─► TransferredOut    (benefits transferred to another scheme)
        └─► Deceased          (notified of death — benefits to estate/dependants)

Data Retention

Data Type	Retention Period	Basis
Member records	7 years post-scheme closure	Pensions Act 1990 (as amended)
Contribution records	7 years	Revenue Commissioners requirement
Audit logs	Indefinite	IORP II regulatory examination requirement
Written policies	Duration of scheme + 7 years	IORP II
ORA reports	Duration of scheme + 7 years	IORP II
ACS submissions	Duration of scheme + 7 years	IORP II
AI-generated drafts	Retained with document record	Deletable by BrokerAdmin
Password reset tokens	60 minutes (auto-expire)	Security (GDPR Art. 32)
Session JWTs	30 days (Auth.js default)	Operational

Soft deletes (via status enums) are used throughout the application. Hard deletion of member records is only permitted after the 7-year retention period expires. A scheduled job (future work) will flag eligible records for review.

Migration Files

Database migrations are managed by Drizzle Kit. Migration files are located in drizzle/migrations/.

Migration	File	Description
`0000`	`0000_initial_schema.sql`	Initial schema: brokers, employers, providers, schemes, members, users, documents
`0001`	`0001_pgvector_rag.sql`	Adds `pgvector` extension, `rag_corpus` table, HNSW vector index
`0002`	`0002_auth_compliance.sql`	Adds Auth.js session/account tables, compliance tables (keyFunctionHolders, writtenPolicies, oraReports, healthCheckAssessments, acsWizard), `audit_logs`
`0003`	`0003_add_password_reset_tokens.sql`	Adds `password_reset_tokens` table for forgot-password flow (GDPR Art. 32)

Running Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Apply pending migrations to the database
npx drizzle-kit migrate

# Inspect current schema state
npx drizzle-kit studio

Never run drizzle-kit push against the production database. Always use drizzle-kit migrate with reviewed migration files. The push command bypasses the migration history and can cause schema drift.

Getting Started

Annual Compliance Statement

Own-Risk Assessment

Key Function Holders

Written Policies

Supervisory Readiness

OMA Transition

AI Assistant

Compliance Calendar

Overview

Entity Relationship Summary

Table Descriptions

`users`

`password_reset_tokens`

`employers`

`providers`

`schemes`

`members`

`keyFunctionHolders`

`writtenPolicies`

`oraReports`

`healthCheckAssessments`

`acsWizard`

`auditLogs`

`ragCorpus`

Data Classification Table

FSM Status Enums

Scheme Status FSM

Member Status FSM

Data Retention

Migration Files

Running Migrations

Getting Started

Annual Compliance Statement

Own-Risk Assessment

Key Function Holders

Written Policies

Supervisory Readiness

OMA Transition

AI Assistant

Compliance Calendar

​Overview

​Entity Relationship Summary

​Table Descriptions

​users

​password_reset_tokens

​employers

​providers

​schemes

​members

​keyFunctionHolders

​writtenPolicies

​oraReports

​healthCheckAssessments

​acsWizard

​auditLogs

​ragCorpus

​Data Classification Table

​FSM Status Enums

​Scheme Status FSM

​Member Status FSM

​Data Retention

​Migration Files

​Running Migrations

Overview

Entity Relationship Summary

Table Descriptions

`users`

`password_reset_tokens`

`employers`

`providers`

`schemes`

`members`

`keyFunctionHolders`

`writtenPolicies`

`oraReports`

`healthCheckAssessments`

`acsWizard`

`auditLogs`

`ragCorpus`

Data Classification Table

FSM Status Enums

Scheme Status FSM

Member Status FSM

Data Retention

Migration Files

Running Migrations