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

# Data Model Conventions: CL & PM Cores

> This document establishes consistent data model conventions for all CL (Clinical) and PM (Practice Management) tables, addressing gaps identified in the spec r…

**Version:** 1.0.0
**Last Updated:** 2026-02-17
**Constitution Reference:** Section 5.2.1 (Table Standards)
**Source:** CL-PM-SPEC-REVIEW Findings 3.3, 8.8

***

## Overview

This document establishes consistent data model conventions for all CL (Clinical) and PM (Practice Management) tables, addressing gaps identified in the spec review.

***

## 1. Required Columns for All Business Entity Tables

Per constitution Section 5.2.1, every business entity table MUST include:

```sql theme={null}
-- Standard columns (required on ALL business entity tables)
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES pf_organizations(id),
custom_fields JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_by UUID REFERENCES pf_profiles(id),
updated_by UUID REFERENCES pf_profiles(id)
```

### Tables Currently Missing Required Columns

| Table                      | Spec  | Missing Columns                             |
| -------------------------- | ----- | ------------------------------------------- |
| `cl_cds_rules`             | CL-08 | `created_by`, `updated_by`                  |
| `cl_cds_alerts`            | CL-08 | `created_by`, `updated_by`                  |
| `cl_outcome_measures`      | CL-10 | `created_by`, `updated_by`                  |
| `cl_program_outcomes`      | CL-10 | `created_by`, `updated_by`                  |
| `cl_report_definitions`    | CL-15 | `created_by`                                |
| `cl_pdmp_queries`          | CL-17 | `created_by`, `updated_by`, `custom_fields` |
| `cl_pdmp_configuration`    | CL-17 | `created_by`, `updated_by`                  |
| `pm_claim_lines`           | PM-08 | `created_by`, `updated_by`                  |
| `pm_eligibility_checks`    | PM-02 | `updated_by`                                |
| `pm_appointment_reminders` | PM-03 | `created_by`, `updated_by`                  |

### Exception: Immutable Log Tables

Tables that are INSERT-only by design (audit logs, disclosure logs) may omit `updated_by` and `updated_at`:

* `cl_disclosure_log` (CL-11) — INSERT-only, no updates allowed
* `cl_data_exchange_log` (CL-16) — INSERT-only
* `pf_audit_logs` — INSERT-only

***

## 2. Soft-Delete Policy

### Policy

| Table Category                                                                | Soft-Delete Required | Rationale                                                 |
| ----------------------------------------------------------------------------- | -------------------- | --------------------------------------------------------- |
| Clinical records (diagnoses, notes, assessments, plans, medications)          | **YES**              | HIPAA 6-year retention, regulatory audit trail            |
| Financial records (claims, charges, payments, adjustments)                    | **YES**              | Billing audit trail, payer disputes, regulatory retention |
| Patient demographics                                                          | **YES**              | Cannot hard-delete patient records with clinical history  |
| Consent records                                                               | **YES**              | 42 CFR Part 2 audit requirements                          |
| Safety-critical records (risk screenings, safety plans, crisis episodes)      | **YES**              | Patient safety, regulatory documentation                  |
| Reference/configuration tables (templates, instruments, rules, fee schedules) | **NO**               | Use `is_active` flag instead                              |
| Immutable log tables (audit logs, disclosure logs)                            | **NO**               | Never deleted; INSERT-only                                |

### Implementation

```sql theme={null}
-- Add to all tables requiring soft-delete
deleted_at TIMESTAMPTZ  -- NULL = active; set = soft-deleted

-- Add partial index for active records
CREATE INDEX idx_{table}_active ON {table}(organization_id)
  WHERE deleted_at IS NULL;
```

### Tables Currently Missing `deleted_at`

| Table                        | Spec  | Action           |
| ---------------------------- | ----- | ---------------- |
| `cl_consents`                | CL-11 | Add `deleted_at` |
| `cl_assessments`             | CL-02 | Add `deleted_at` |
| `cl_treatment_plans`         | CL-03 | Add `deleted_at` |
| `cl_treatment_goals`         | CL-03 | Add `deleted_at` |
| `cl_treatment_interventions` | CL-03 | Add `deleted_at` |
| `cl_medications`             | CL-05 | Add `deleted_at` |
| `cl_prescriptions`           | CL-06 | Add `deleted_at` |
| `cl_risk_screenings`         | CL-07 | Add `deleted_at` |
| `cl_safety_plans`            | CL-07 | Add `deleted_at` |
| `cl_crisis_episodes`         | CL-13 | Add `deleted_at` |
| `cl_group_sessions`          | CL-14 | Add `deleted_at` |
| `cl_group_attendance`        | CL-14 | Add `deleted_at` |
| `pm_claims`                  | PM-08 | Add `deleted_at` |
| `pm_appointments`            | PM-03 | Add `deleted_at` |
| `pm_charges`                 | PM-07 | Add `deleted_at` |

***

## 3. RLS Helper Function Naming Convention

### Standard Pattern

```
{core_prefix}_has_org_access()     -- Tenant isolation check
{core_prefix}_has_permission(p)    -- Role-based permission check
```

### Current Inconsistencies to Resolve

| Current Name                        | Spec  | Standard Name         |
| ----------------------------------- | ----- | --------------------- |
| `pm_can_access_patient()`           | PM-01 | `pm_has_org_access()` |
| `pm_claims_policy_check()`          | PM-08 | `pm_has_org_access()` |
| `can_access_consents()`             | CL-11 | `cl_has_org_access()` |
| `get_current_organization_id()`     | PM-04 | `pm_has_org_access()` |
| `cl_security.get_org_for_profile()` | CL-07 | `cl_has_org_access()` |
| `cl_security.is_profile_in_org()`   | CL-07 | `cl_has_org_access()` |

### Org Context Pattern

All RLS helpers MUST use the same pattern for obtaining the current organization:

```sql theme={null}
-- STANDARD PATTERN: Use auth.uid() to get org from pf_profiles
CREATE OR REPLACE FUNCTION {core}_has_org_access(target_org_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_profiles
    WHERE id = auth.uid()
    AND organization_id = target_org_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;
```

**Do NOT use:**

* `current_setting('app.current_org')` (CL-08 pattern — fragile, requires app-level setting)
* `current_setting('jwt.claims.organization_id')` (CL-12 pattern — JWT claim may not exist)

***

## 4. PHI Classification

### Classification Levels

| Level        | Description                                    | Requirements                                                |
| ------------ | ---------------------------------------------- | ----------------------------------------------------------- |
| **PHI**      | Protected Health Information (HIPAA)           | Access logging, encryption at rest, breach notification     |
| **SUD-PHI**  | Substance Use Disorder records (42 CFR Part 2) | All PHI requirements + consent-gated access + Part 2 notice |
| **PII**      | Personally Identifiable Information            | Standard data protection                                    |
| **Business** | Non-sensitive business data                    | Standard access controls                                    |

### Table Classification

| Table                   | Spec  | Classification                       |
| ----------------------- | ----- | ------------------------------------ |
| `cl_patient_charts`     | CL-01 | PHI                                  |
| `cl_problems`           | CL-01 | PHI                                  |
| `cl_allergies`          | CL-01 | PHI                                  |
| `cl_progress_notes`     | CL-04 | PHI (SUD-PHI if SUD-related)         |
| `cl_assessments`        | CL-02 | PHI (SUD-PHI if substance\_use type) |
| `cl_medications`        | CL-05 | PHI                                  |
| `cl_prescriptions`      | CL-06 | PHI                                  |
| `cl_risk_screenings`    | CL-07 | PHI                                  |
| `cl_safety_plans`       | CL-07 | PHI                                  |
| `cl_consents`           | CL-11 | PHI                                  |
| `cl_disclosure_log`     | CL-11 | PHI                                  |
| `cl_crisis_episodes`    | CL-13 | PHI                                  |
| `pm_patients`           | PM-01 | PII + PHI                            |
| `pm_insurance_policies` | PM-02 | PII                                  |
| `pm_claims`             | PM-08 | PHI (contains diagnosis codes)       |
| `pm_appointments`       | PM-03 | PHI                                  |
| `pm_charges`            | PM-07 | PHI (contains diagnosis codes)       |