> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pensionsportal.ie/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Model Reference

> Complete database schema reference for PensionsPortal.ie

## Overview

PensionsPortal.ie 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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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](#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.

```typescript theme={null}
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](/security#pps-number-encryption-gdpr-priority-0) |
| `status`              | enum      | FSM — see [Member Status FSM](#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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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(),
});
```

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

***

### `ragCorpus`

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

```typescript theme={null}
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                                |

<Note>
  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.
</Note>

***

## 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

```bash theme={null}
# 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
```

<Warning>
  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.
</Warning>