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

# Encore Health OS Platform Data Dictionary

> Version: 1.0.0 Last Updated: 2026-01-15 Constitution Reference: Section 5 (Database Naming & Environment Guardrails)

**Version:** 1.0.0\
**Last Updated:** 2026-01-15\
**Constitution Reference:** Section 5 (Database Naming & Environment Guardrails)

***

## Overview

This document defines standard column definitions, naming conventions, and data patterns used across the Encore Health OS platform. All database schema designs MUST follow these standards to ensure consistency, maintainability, and proper multi-tenant isolation.

**Related Documents:**

* `constitution.md §5` - Database naming and guardrails
* `AGENTS.md` - Database rules quick reference
* `specs/_templates/SPEC_TEMPLATE.md` - Data Model section
* `specs/_templates/db-preflight.md` - Database pre-flight checklist

***

## Standard Column Definitions

### Identity Columns

These columns are REQUIRED on all business entity tables:

| Column            | Type | Required    | Default             | Description                                 | Example                                |
| ----------------- | ---- | ----------- | ------------------- | ------------------------------------------- | -------------------------------------- |
| `id`              | UUID | Yes         | `gen_random_uuid()` | Primary key, auto-generated                 | `550e8400-e29b-41d4-a716-446655440000` |
| `organization_id` | UUID | Yes         | -                   | Tenant identifier, FK to `pf_organizations` | Required for multi-tenant isolation    |
| `site_id`         | UUID | Conditional | -                   | Site identifier, FK to `pf_sites`           | Required for site-scoped entities      |

**Usage Notes:**

* `organization_id` is ALWAYS required for multi-tenant isolation
* `site_id` is required when data is site-specific (e.g., beds, rooms)
* Both enable RLS policies for data isolation

### Audit Columns

These columns track record lifecycle and are RECOMMENDED on all business entities:

| Column       | Type        | Required    | Default | Description                                 | Auto-Updated          |
| ------------ | ----------- | ----------- | ------- | ------------------------------------------- | --------------------- |
| `created_at` | TIMESTAMPTZ | Yes         | `now()` | Record creation timestamp                   | On INSERT only        |
| `updated_at` | TIMESTAMPTZ | Yes         | `now()` | Last modification timestamp                 | Via trigger on UPDATE |
| `created_by` | UUID        | Recommended | -       | User who created, FK to `pf_profiles`       | Set by application    |
| `updated_by` | UUID        | Recommended | -       | User who last modified, FK to `pf_profiles` | Set by application    |
| `deleted_at` | TIMESTAMPTZ | Conditional | -       | Soft delete timestamp (null = not deleted)  | Set by application    |

**Update Trigger Pattern:**

```sql theme={null}
-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_timestamp
BEFORE UPDATE ON {table_name}
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

### Extensibility Columns

| Column          | Type  | Required    | Default | Description                    | When to Include        |
| --------------- | ----- | ----------- | ------- | ------------------------------ | ---------------------- |
| `custom_fields` | JSONB | Conditional | `'{}'`  | Organization-specific metadata | Business entities only |

**When to Include `custom_fields`:**

* ✅ Business entity tables (employees, residents, invoices, forms)
* ✅ Configuration tables that organizations might extend
* ❌ Junction/mapping tables (e.g., `hr_employee_skills`)
* ❌ Audit/log tables (e.g., `pf_audit_logs`)
* ❌ Line item tables (use parent's `custom_fields`)
* ❌ System configuration tables

**Required Comment Pattern:**

```sql theme={null}
COMMENT ON COLUMN {table_name}.custom_fields IS 
  'Organization-specific metadata (e.g., {"external_id": "EXT-123", "badge_number": "B-456"})';
```

### Status Columns

Common patterns for tracking entity state:

| Pattern      | Type        | Description                 | Example Values                       |
| ------------ | ----------- | --------------------------- | ------------------------------------ |
| `status`     | TEXT        | Current lifecycle state     | `'draft'`, `'active'`, `'archived'`  |
| `is_active`  | BOOLEAN     | Simple active/inactive flag | `true`, `false`                      |
| `deleted_at` | TIMESTAMPTZ | Soft delete indicator       | `null` (active), timestamp (deleted) |

**Prefer `status` TEXT over `is_active` BOOLEAN** when entity has more than 2 states.

**Prefer `deleted_at` TIMESTAMPTZ over `is_deleted` BOOLEAN** for soft deletes (enables audit trail).

***

## Standard Enum Values

### Common Status Values

| Domain            | Values                                                 | Description               |
| ----------------- | ------------------------------------------------------ | ------------------------- |
| General Lifecycle | `'draft'`, `'active'`, `'inactive'`, `'archived'`      | Standard entity lifecycle |
| Approval Workflow | `'pending'`, `'approved'`, `'rejected'`, `'cancelled'` | Approval state machine    |
| Processing State  | `'queued'`, `'processing'`, `'completed'`, `'failed'`  | Async processing          |
| Publication State | `'draft'`, `'review'`, `'published'`, `'deprecated'`   | Content publishing        |

### Employment Status (HR)

| Value          | Description                        |
| -------------- | ---------------------------------- |
| `'active'`     | Currently employed                 |
| `'inactive'`   | Temporarily inactive (leave, etc.) |
| `'terminated'` | Employment ended                   |
| `'pending'`    | Pending onboarding                 |

### Episode Status (RH)

| Value           | Description                     |
| --------------- | ------------------------------- |
| `'pending'`     | Awaiting admission              |
| `'active'`      | Currently in residence          |
| `'discharged'`  | Completed stay                  |
| `'transferred'` | Transferred to another facility |

### Transaction Status (FA)

| Value       | Description         |
| ----------- | ------------------- |
| `'draft'`   | Not yet finalized   |
| `'pending'` | Awaiting approval   |
| `'posted'`  | Finalized to ledger |
| `'voided'`  | Cancelled/reversed  |

***

## Column Naming Conventions

### Standard Patterns

| Pattern           | Example                             | Description                                     |
| ----------------- | ----------------------------------- | ----------------------------------------------- |
| `{entity}_id`     | `employee_id`, `resident_id`        | Foreign key reference to entity                 |
| `{entity}_count`  | `bed_count`, `task_count`           | Denormalized count (use sparingly)              |
| `{action}_at`     | `submitted_at`, `approved_at`       | Timestamp when action occurred                  |
| `{action}_by`     | `submitted_by`, `approved_by`       | User who performed action (FK to `pf_profiles`) |
| `is_{state}`      | `is_active`, `is_primary`           | Boolean state flag                              |
| `has_{feature}`   | `has_attachments`, `has_comments`   | Boolean existence flag                          |
| `total_{entity}`  | `total_amount`, `total_hours`       | Calculated/aggregated total                     |
| `{entity}_number` | `invoice_number`, `employee_number` | Human-readable identifier                       |
| `{entity}_date`   | `hire_date`, `admission_date`       | Date (no time) field                            |

### Foreign Key Naming

```sql theme={null}
-- Standard FK pattern
{referenced_entity}_id UUID REFERENCES {core}_{referenced_table}(id)

-- Examples
employee_id UUID REFERENCES hr_employees(id)
organization_id UUID REFERENCES pf_organizations(id) ON DELETE CASCADE
site_id UUID REFERENCES pf_sites(id)
```

### Index Naming

| Pattern                        | Example                       | Purpose             |
| ------------------------------ | ----------------------------- | ------------------- |
| `idx_{table}_{column}`         | `idx_hr_employees_org`        | Single column index |
| `idx_{table}_{col1}_{col2}`    | `idx_hr_employees_org_status` | Composite index     |
| `idx_{table}_{column}_partial` | `idx_hr_employees_active`     | Partial index       |
| `uniq_{table}_{columns}`       | `uniq_hr_employees_number`    | Unique index        |

***

## Reserved Column Names

These columns have platform-wide meaning and MUST NOT be repurposed:

| Column            | Reserved For               | Core |
| ----------------- | -------------------------- | ---- |
| `id`              | Primary key                | All  |
| `organization_id` | Multi-tenant isolation     | All  |
| `site_id`         | Site-level scoping         | All  |
| `custom_fields`   | Organization extensibility | All  |
| `created_at`      | Audit: creation time       | All  |
| `updated_at`      | Audit: last modified time  | All  |
| `created_by`      | Audit: creator reference   | All  |
| `updated_by`      | Audit: modifier reference  | All  |
| `deleted_at`      | Soft delete marker         | All  |

***

## Data Type Guidelines

### Identifiers

| Type               | Use For                          | Example                             |
| ------------------ | -------------------------------- | ----------------------------------- |
| `UUID`             | Primary keys, foreign keys       | `id`, `organization_id`             |
| `TEXT`             | Human-readable identifiers       | `employee_number`, `invoice_number` |
| `SERIAL/BIGSERIAL` | Sequence numbers (internal only) | Avoid for business keys             |

### Text Fields

| Type         | Use For                  | Notes                       |
| ------------ | ------------------------ | --------------------------- |
| `TEXT`       | Variable-length strings  | Preferred over `VARCHAR`    |
| `VARCHAR(n)` | Fixed-max-length strings | Only when constraint needed |
| `TEXT[]`     | String arrays            | For tags, keywords          |

### Numeric Fields

| Type           | Use For                 | Notes                        |
| -------------- | ----------------------- | ---------------------------- |
| `INTEGER`      | Counts, small numbers   | -2B to +2B                   |
| `BIGINT`       | Large counts            | When INTEGER may overflow    |
| `NUMERIC(p,s)` | Money, precise decimals | `NUMERIC(12,2)` for currency |
| `DECIMAL`      | Alias for NUMERIC       | Use NUMERIC for consistency  |

**Money Pattern:**

```sql theme={null}
-- Always use NUMERIC for currency
amount NUMERIC(12, 2) NOT NULL DEFAULT 0.00
```

### Date/Time Fields

| Type          | Use For                  | Notes                           |
| ------------- | ------------------------ | ------------------------------- |
| `TIMESTAMPTZ` | Timestamps with timezone | **Always use for timestamps**   |
| `DATE`        | Date only (no time)      | Use for birth dates, hire dates |
| `TIME`        | Time only (no date)      | Rare; use for schedules         |
| `INTERVAL`    | Time durations           | For calculated durations        |

**Always Use `TIMESTAMPTZ`** (not `TIMESTAMP`) for timestamps to handle timezone correctly.

### JSON Fields

| Type    | Use For             | Notes                             |
| ------- | ------------------- | --------------------------------- |
| `JSONB` | Structured metadata | Preferred over `JSON` (indexable) |
| `JSON`  | Avoid               | Use `JSONB` instead               |

***

## Constraint Guidelines

### CHECK Constraints

**Use CHECK constraints for:**

* ✅ Enum-like values: `CHECK (status IN ('active', 'inactive'))`
* ✅ Numeric ranges: `CHECK (age >= 0 AND age <= 150)`
* ✅ String patterns: `CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')`

**NEVER use CHECK constraints for:**

* ❌ Time-based logic: `CHECK (expiry_date >= CURRENT_DATE)` - Will fail as time passes!
* ❌ Complex business rules - Use application-level validation
* ❌ Cross-table relationships - Use foreign keys or application logic

```sql theme={null}
-- ✅ CORRECT: Static enum check
CHECK (status IN ('pending', 'approved', 'rejected'))

-- ❌ WRONG: Time-based check (NEVER DO THIS)
CHECK (expiry_date >= CURRENT_DATE)
```

### Foreign Key Constraints

```sql theme={null}
-- Standard FK with CASCADE delete (for child records)
organization_id UUID NOT NULL 
  REFERENCES pf_organizations(id) 
  ON DELETE CASCADE

-- FK with RESTRICT (prevent orphans)
employee_id UUID NOT NULL 
  REFERENCES hr_employees(id) 
  ON DELETE RESTRICT

-- Optional FK (nullable)
supervisor_id UUID 
  REFERENCES hr_employees(id) 
  ON DELETE SET NULL
```

### Unique Constraints

```sql theme={null}
-- Organization-scoped uniqueness
UNIQUE(organization_id, employee_number)

-- Composite uniqueness
UNIQUE(organization_id, site_id, bed_number)
```

***

## RLS Policy Patterns

### Standard Policy Set

Every business table needs these RLS policies:

```sql theme={null}
-- Enable RLS
ALTER TABLE {core}_{entity} ENABLE ROW LEVEL SECURITY;

-- SELECT: Organization access
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));

-- INSERT: Organization access with check
CREATE POLICY "{core}_{entity}_insert" ON {core}_{entity}
FOR INSERT
WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- UPDATE: Organization access with BOTH clauses (CRITICAL!)
CREATE POLICY "{core}_{entity}_update" ON {core}_{entity}
FOR UPDATE
USING ({core}_has_org_access(organization_id, auth.uid()))
WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- DELETE: Admin only
CREATE POLICY "{core}_{entity}_delete" ON {core}_{entity}
FOR DELETE
USING ({core}_is_org_admin(organization_id, auth.uid()));
```

**⚠️ CRITICAL:** UPDATE policies MUST include `WITH CHECK` to prevent organization\_id changes that could breach tenant isolation.

### Security Definer Functions

```sql theme={null}
-- Standard org access check function
CREATE OR REPLACE FUNCTION {core}_has_org_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_role_assignments
    WHERE pf_user_role_assignments.organization_id = org_id
      AND pf_user_role_assignments.user_id = user_id
      AND (pf_user_role_assignments.expires_at IS NULL 
           OR pf_user_role_assignments.expires_at > now())
  );
END;
$$;
```

***

## Table Categories

### Business Entity Tables

Primary data tables that represent core business objects.

**Requirements:**

* ✅ `organization_id` (required)
* ✅ `site_id` (where applicable)
* ✅ `custom_fields JSONB` (required)
* ✅ All audit columns
* ✅ RLS enabled with full policy set

**Examples:** `hr_employees`, `rh_residents`, `fa_invoices`, `fw_forms`

### Junction/Mapping Tables

Tables that create many-to-many relationships.

**Requirements:**

* ✅ `organization_id` (required)
* ✅ Composite primary key or unique constraint
* ❌ `custom_fields` (not needed)
* ✅ RLS enabled

**Examples:** `hr_employee_skills`, `fw_form_assignments`

### Audit/Log Tables

Tables that record historical events for compliance.

**Requirements:**

* ✅ `organization_id` (required)
* ✅ `created_at` (required)
* ❌ `updated_at`, `deleted_at` (logs are immutable)
* ❌ `custom_fields` (not needed)
* ✅ Append-only (no UPDATE/DELETE policies)

**Examples:** `pf_audit_logs`, `fa_journal_entry_lines`

### Configuration Tables

Tables that store module or organization settings.

**Requirements:**

* ✅ `organization_id` (required)
* ✅ All audit columns
* ❌ `custom_fields` (settings ARE the custom fields)

**Examples:** `hr_module_settings`, `fa_module_settings`

***

## Versioning

| Version | Date       | Changes         |
| ------- | ---------- | --------------- |
| 1.0.0   | 2026-01-15 | Initial version |

***

## See Also

* `constitution.md §5` - Database naming and guardrails
* `AGENTS.md` - Database rules quick reference
* `specs/_templates/SPEC_TEMPLATE.md` - Data Model section
* `specs/_templates/db-preflight.md` - Database pre-flight checklist
* `specs/_templates/DATA_MODEL_TEMPLATE.md` - Data model documentation template

***

**Document Owner:** Platform Foundation\
**Review Cadence:** Quarterly\
**Next Review:** 2026-04-15