Database Development Guide - Encore Health OS

Version: 1.2.0
Last Updated: 2026-04-24
Status: Stable
Target Audience: Developers and AI agents (GitHub Copilot, Cursor, general AI assistants) Comprehensive guide for database development in the Encore Health OS Platform, covering RLS patterns, migrations, custom fields, multi-tenant queries, and debugging.

AI Agent Context

Key Patterns for AI:

Always include organization_id and custom_fields JSONB DEFAULT '{}' NOT NULL in business entity tables
Always use SECURITY DEFINER functions for RLS policies to avoid recursion
Always follow naming conventions: snake_case for tables/columns, {core}_{action}_{entity}() for functions
Always ship schema changes as versioned supabase/migrations/*.sql — never manual SQL in production Dashboard
Prefer declarative DDL under supabase/schemas/ plus supabase db diff (see DECLARATIVE_SCHEMA_GUIDE.md); use hand-written migrations for documented diff caveats (DML, some policy/view/grant cases)
Always test RLS policies with multi-tenant isolation scenarios

Common Mistakes to Avoid:

Creating recursive RLS policies (querying RLS-protected tables in RLS policies)
Forgetting organization_id in business tables (breaks multi-tenancy)
Using CHECK constraints for time-based validations (use triggers or application logic)
Creating schema changes outside migrations / declarative workflow (no orphan Dashboard DDL)
Not including custom_fields JSONB column in business entities

Quick Reference

Task	Pattern	Example
Create table	Include `organization_id`, `custom_fields`, RLS	Creating Tables
RLS policy	Use `SECURITY DEFINER` functions	RLS Patterns
Migration	`{timestamp}_{snake_case_description}.sql` for new files (UUID-suffix grandfathered)	Migrations
Custom fields	`JSONB DEFAULT '{}'` with comment	Custom Fields
Multi-tenant query	Always filter by `organization_id`	Multi-Tenant Queries

Database Development Workflow

1. Plan the Schema

Before writing SQL, document:

Table purpose and ownership (which core/module)
Required columns (standard + module-specific)
Relationships (foreign keys)
RLS requirements
Custom fields use cases

2. Author DDL (declarative) and generate a migration

Preferred (DDL):

Edit or add .sql under supabase/schemas/ so it reflects the desired final state (see DECLARATIVE_SCHEMA_GUIDE.md).
Optional: npm run db:schemas:lint (@supabase/pg-topo ordering / parse diagnostics; skips if no .sql files yet).
Stop local Supabase, then generate a migration from drift:
```
npx supabase stop
npx supabase db diff -f {core}_{entity}_{change}
```
Shortcut: npm run db:schemas:diff -- -f {core}_{entity}_{change}
Review the new file under supabase/migrations/ (watch for unintended drops).

Legacy / caveat-only (DML, certain policy/view/grant cases):

npx supabase migration new {core}_{entity}_table
# Example
npx supabase migration new hr_employees_table

Follow MIGRATION_LANES.md for schema vs system-defaults lanes.

3. Write or verify SQL

Follow naming conventions and include:

Table creation with standard columns
RLS enablement
RLS policies using SECURITY DEFINER functions
Indexes for performance
Comments for documentation

4. Test Locally

# Reset local database
supabase db reset

# Run migrations
supabase migration up

# Verify in Supabase Studio
# Check RLS policies work correctly

5. Generate TypeScript Types

# Generate types from schema
npx supabase gen types typescript --project-id {project-ref} > src/integrations/supabase/types.ts

6. Update Code

Create TypeScript types/interfaces
Implement queries with proper RLS
Add tests for RLS policies
Document in implementation log

Creating Tables with RLS

Standard Table Structure

All business entity tables MUST include:

CREATE TABLE {core}_{entity} (
  -- Primary key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Multi-tenant context (REQUIRED)
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  
  -- Site context (where applicable)
  site_id UUID REFERENCES pf_sites(id),
  
  -- Custom fields (REQUIRED for business entities)
  custom_fields JSONB DEFAULT '{}' NOT NULL,
  
  -- Audit columns
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  
  -- Module-specific columns
  -- ... your columns here ...
);

-- Enable RLS (REQUIRED)
ALTER TABLE {core}_{entity} ENABLE ROW LEVEL SECURITY;

-- Add comment for custom_fields
COMMENT ON COLUMN {core}_{entity}.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001"})';

-- Updated_at trigger (REQUIRED - use shared function)
CREATE TRIGGER set_{core}_{entity}_updated_at
    BEFORE UPDATE ON {core}_{entity}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

When to Include custom_fields

✅ Include for:

Business entities (employees, residents, forms, transactions)
User-facing data that may need org-specific metadata

❌ Skip for:

Junction/mapping tables
Audit/log tables
System/configuration tables

Example: Employee Table

CREATE TABLE hr_employees (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  site_id UUID REFERENCES pf_sites(id),
  custom_fields JSONB DEFAULT '{}' NOT NULL,
  
  -- Employee-specific columns
  full_name TEXT NOT NULL,
  email TEXT,
  employee_number TEXT,
  hire_date DATE,
  department_id UUID REFERENCES pf_departments(id),
  level_id UUID REFERENCES pf_levels(id),
  
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id)
);

ALTER TABLE hr_employees ENABLE ROW LEVEL SECURITY;

COMMENT ON COLUMN hr_employees.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001", "union_member": true})';

-- Updated_at trigger (REQUIRED)
CREATE TRIGGER set_hr_employees_updated_at
    BEFORE UPDATE ON hr_employees
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

RLS Policy Patterns

Critical Rule: Use SECURITY DEFINER Functions

❌ NEVER query RLS-protected tables directly in policies (causes infinite recursion):

-- BAD: pf_user_role_assignments has RLS too! (Note: pf_user_roles is DROPPED — use pf_user_role_assignments)
CREATE POLICY "access" ON my_table FOR SELECT
USING (organization_id IN (
  SELECT organization_id FROM pf_user_role_assignments WHERE user_id = auth.uid()
));

✅ ALWAYS use SECURITY DEFINER helper functions:

-- Create helper function first
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
  );
END;
$$;

-- Then use in policy
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));

Standard RLS Policies

SELECT Policy (View)

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

INSERT Policy (Create)

CREATE POLICY "{core}_{entity}_insert" ON {core}_{entity}
FOR INSERT
WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

UPDATE Policy (CRITICAL: Must Include WITH CHECK)

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()));

Why WITH CHECK is Critical: Without WITH CHECK, a user can change organization_id to move data between tenants, breaking multi-tenant isolation.

DELETE Policy

CREATE POLICY "{core}_{entity}_delete" ON {core}_{entity}
FOR DELETE
USING ({core}_has_org_access(organization_id, auth.uid()));

Creator-Only Policies

For entities where only the creator can modify:

-- Creator can update
CREATE POLICY "{core}_{entity}_update_creator" ON {core}_{entity}
FOR UPDATE
USING (created_by = auth.uid())
WITH CHECK (
  created_by = auth.uid()
  AND {core}_has_org_access(organization_id, auth.uid())
);

-- Creator can delete
CREATE POLICY "{core}_{entity}_delete_creator" ON {core}_{entity}
FOR DELETE
USING (
  created_by = auth.uid()
  AND {core}_has_org_access(organization_id, auth.uid())
);

Role-Based Policies

For policies that require specific roles:

-- ⚠️ DEPRECATED PATTERN — pf_user_roles is DROPPED. Use SECURITY DEFINER helpers instead.
-- Org admins can manage (correct pattern):
CREATE POLICY "{core}_{entity}_admin" ON {core}_{entity}
FOR ALL
USING ({core}_is_org_admin(organization_id, auth.uid()))
WITH CHECK ({core}_is_org_admin(organization_id, auth.uid()));

Migration Creation and Testing

Migration Naming

Format (new migrations): {timestamp}_{snake_case_description}.sql — e.g. 20260428000143_pf15_picklist_defaults_data_driven.sql. The Lovable-generated {timestamp}_{uuid}.sql pattern is grandfathered for existing files but blocked for new ones by scripts/database/lint-migration-filename.ts in pre-commit.

# Generated automatically by Supabase CLI
supabase migration new {core}_{entity}_table

# Example output:
# supabase/migrations/20251123140051_8ae69786-af17-4237-9157-b98f05f45ad7.sql

Migration Requirements

Monotonic and Ordered: Migrations run in timestamp order
Re-runnable: Can run multiple times safely (use IF NOT EXISTS)
Backward-Compatible: When possible, avoid breaking changes

Migration Template

-- Migration: {core}_{entity}_table
-- Description: Create {entity} table for {core} module
-- Author: {your-name}
-- Date: {date}

-- Create table
CREATE TABLE IF NOT EXISTS {core}_{entity} (
  -- ... table definition ...
);

-- Enable RLS
ALTER TABLE {core}_{entity} ENABLE ROW LEVEL SECURITY;

-- Create helper function (if needed)
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
  );
END;
$$;

-- Create RLS policies
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));

-- ... other policies ...

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_{core}_{entity}_org_id 
  ON {core}_{entity}(organization_id);

-- Add comments
COMMENT ON TABLE {core}_{entity} IS '{description}';
COMMENT ON COLUMN {core}_{entity}.custom_fields IS '{examples}';

Testing Migrations

# Reset local database
supabase db reset

# This will:
# 1. Drop all tables
# 2. Run all migrations in order
# 3. Apply seed data (if any)

# Verify in Supabase Studio
# - Check table exists
# - Check RLS is enabled
# - Test policies with different users

Migration Best Practices

One logical change per migration - Easier to review and rollback
Use IF NOT EXISTS - Makes migrations re-runnable
Add comments - Document purpose and changes
Test locally first - Always test before committing
Review RLS policies - Ensure tenant isolation

Custom Fields Implementation

When to Use Custom Fields

Use custom_fields JSONB for:

Organization-specific metadata on records
Fields that vary by organization
Extensibility without schema changes

Examples:

Employee badge numbers
External system IDs
Custom flags or preferences
Organization-specific classifications

Custom Fields Pattern

-- Add to table
custom_fields JSONB DEFAULT '{}' NOT NULL,

-- Add comment with examples
COMMENT ON COLUMN {table_name}.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001", "union_member": true})';

Querying Custom Fields

// Query with custom field filter
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .eq('organization_id', orgId)
  .filter('custom_fields->>badge_number', 'eq', '12345');

// Access custom field in code
const badgeNumber = employee.custom_fields?.badge_number;
const isUnionMember = employee.custom_fields?.union_member === true;

Custom Fields Indexing

For frequently queried custom fields, add GIN index:

CREATE INDEX idx_{table}_custom_fields 
ON {table} USING GIN (custom_fields);

When to add index:

Custom field is queried frequently
Organization has many records
Query performance is slow

Multi-Tenant Query Patterns

Always Filter by organization_id

✅ CORRECT: Always include organization_id filter

// Query with organization context
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .eq('organization_id', currentOrganization.id);  // ✅ Required

❌ WRONG: Missing organization_id filter

// This will fail RLS or return wrong data
const { data } = await supabase
  .from('hr_employees')
  .select('*');  // ❌ Missing organization_id!

Getting Current Organization

import { useCurrentUser } from '@/platform/auth';
import { useOrganization } from '@/platform/organizations/OrganizationContext';

function MyComponent() {
  const { user } = useCurrentUser();
  const { currentOrganization } = useOrganization();
  
  // Use currentOrganization.id for queries
  const { data } = await supabase
    .from('hr_employees')
    .select('*')
    .eq('organization_id', currentOrganization.id);
}

Multi-Organization Queries

For users with access to multiple organizations:

// Get user's organizations (Note: pf_user_roles is DROPPED — use pf_user_role_assignments)
const { data: userRoles } = await supabase
  .from('pf_user_role_assignments')
  .select('organization_id')
  .eq('user_id', user.id);

const orgIds = userRoles?.map(r => r.organization_id) || [];

// Query across organizations (if needed)
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .in('organization_id', orgIds);

Note: RLS policies will automatically filter to organizations the user has access to.

Database Debugging

Check RLS Policies

-- List all policies on a table
SELECT * FROM pg_policies 
WHERE tablename = '{core}_{entity}';

-- Check if RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE tablename = '{core}_{entity}';

Test RLS Policies

-- Test as specific user
SET ROLE authenticated;
SET request.jwt.claim.sub = '{user-id}';

-- Try query
SELECT * FROM {core}_{entity};

-- Reset
RESET ROLE;

Debug Query Performance

-- Enable query logging
SET log_statement = 'all';
SET log_duration = on;

-- Run query and check logs
SELECT * FROM {core}_{entity} WHERE organization_id = '{org-id}';

Common RLS Issues

Issue: “Infinite recursion” error

Cause: Querying RLS-protected table in policy Solution: Use SECURITY DEFINER function

Issue: “No rows returned” when rows exist

Cause: RLS policy too restrictive or missing Solution: Check policy conditions, verify user has org access

Issue: “Cross-organization data leakage”

Cause: Missing WITH CHECK in UPDATE policy Solution: Add WITH CHECK clause to UPDATE policies

Constitution playbook: PF DDL templates

Canonical SQL and JSON examples for module settings, picklists (PF-15), and custom field definitions (PF-16). The constitution §5.2 states the MUST rules; this section is the extended implementation reference. UI patterns for settings pages: settings-pattern-guide.md.

Module settings table (`{core}_module_settings`)

CREATE TABLE {core}_module_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL UNIQUE REFERENCES pf_organizations(id) ON DELETE CASCADE,

  -- Configuration columns (module-specific)
  -- Examples:
  -- default_status TEXT DEFAULT 'draft',
  -- retention_days INTEGER DEFAULT 365,
  -- max_upload_size_mb INTEGER DEFAULT 25,
  -- custom_categories TEXT[] DEFAULT ARRAY[]::TEXT[],

  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id)
);

ALTER TABLE {core}_module_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their org settings"
  ON {core}_module_settings FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()));

CREATE POLICY "Org admins can insert settings"
  ON {core}_module_settings FOR INSERT
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "Org admins can update settings"
  ON {core}_module_settings FOR UPDATE
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE TRIGGER set_updated_at BEFORE UPDATE ON {core}_module_settings
  FOR EACH ROW EXECUTE FUNCTION pf_set_updated_at();

Picklists: `pf_picklists` and `pf_picklist_items`

CREATE TABLE pf_picklists (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES pf_organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT,
  is_system BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  version INTEGER DEFAULT 1,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  UNIQUE(organization_id, name)
);

ALTER TABLE pf_picklists ENABLE ROW LEVEL SECURITY;

CREATE TABLE pf_picklist_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  picklist_id UUID NOT NULL REFERENCES pf_picklists(id) ON DELETE CASCADE,
  value TEXT NOT NULL,
  label TEXT NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  UNIQUE(picklist_id, value)
);

ALTER TABLE pf_picklist_items ENABLE ROW LEVEL SECURITY;

CREATE INDEX idx_picklists_org_category ON pf_picklists(organization_id, category);
CREATE INDEX idx_picklist_items_picklist ON pf_picklist_items(picklist_id, display_order);

Example RLS (use current pf_has_org_access / pf_is_org_admin from your schema):

CREATE POLICY "Users can view org picklists"
  ON pf_picklists FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()) OR is_system = true);

CREATE POLICY "Admins can manage picklists"
  ON pf_picklists FOR ALL
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "Users can view org picklist items"
  ON pf_picklist_items FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_has_org_access(pf_picklists.organization_id, auth.uid())
    )
  );

CREATE POLICY "Admins can manage picklist items"
  ON pf_picklist_items FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_is_org_admin(pf_picklists.organization_id, auth.uid())
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_is_org_admin(pf_picklists.organization_id, auth.uid())
    )
  );

Custom field definitions: `pf_custom_field_definitions`

CREATE TABLE pf_custom_field_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL,
  field_key TEXT NOT NULL,
  field_label TEXT NOT NULL,
  field_type TEXT NOT NULL,
  is_required BOOLEAN DEFAULT false,
  default_value JSONB,
  validation_rules JSONB DEFAULT '{}',
  display_order INTEGER DEFAULT 0,
  field_group TEXT,
  is_active BOOLEAN DEFAULT true,
  picklist_id UUID REFERENCES pf_picklists(id),
  help_text TEXT,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  UNIQUE(organization_id, entity_type, field_key)
);

ALTER TABLE pf_custom_field_definitions ENABLE ROW LEVEL SECURITY;

CREATE INDEX idx_custom_field_defs_entity ON pf_custom_field_definitions(organization_id, entity_type, is_active);

CREATE POLICY "Users can view org field definitions"
  ON pf_custom_field_definitions FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()));

CREATE POLICY "Admins can manage field definitions"
  ON pf_custom_field_definitions FOR ALL
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

Example `validation_rules` JSON

{
  "text": {
    "minLength": 2,
    "maxLength": 100,
    "pattern": "^[A-Z0-9-]+$"
  },
  "number": {
    "min": 0,
    "max": 999999,
    "decimals": 2
  },
  "date": {
    "minDate": "2020-01-01",
    "maxDate": "2030-12-31"
  },
  "select": {
    "allowOther": true
  }
}

Core Standards

Constitution §5 - Database rules and RLS requirements
AGENTS.md - Database naming conventions and patterns

Development Guides

Settings pattern guide - Module settings pages (UI; pairs with Constitution playbook DDL)
Troubleshooting Guide - Database connection and query issues
Testing Setup and Run Guide - RLS testing patterns and execution

Architecture

Custom Fields Guide - Detailed custom fields patterns
Constitution - Database development guardrails and migration patterns

Maintained By: Platform Foundation Team

Architecture

Development

Database

Testing

Migration

Operations

Governance & Security

Platform Internals

Documentation Index

​AI Agent Context

​Quick Reference

​Database Development Workflow

​1. Plan the Schema

​2. Author DDL (declarative) and generate a migration

​3. Write or verify SQL

​4. Test Locally

​5. Generate TypeScript Types

​6. Update Code

​Creating Tables with RLS

​Standard Table Structure

​When to Include custom_fields

​Example: Employee Table

​RLS Policy Patterns

​Critical Rule: Use SECURITY DEFINER Functions

​Standard RLS Policies

​SELECT Policy (View)

​INSERT Policy (Create)

​UPDATE Policy (CRITICAL: Must Include WITH CHECK)

​DELETE Policy

​Creator-Only Policies

​Role-Based Policies

​Migration Creation and Testing

​Migration Naming

​Migration Requirements

​Migration Template

​Testing Migrations

​Migration Best Practices

​Custom Fields Implementation

​When to Use Custom Fields

​Custom Fields Pattern

​Querying Custom Fields

​Custom Fields Indexing

​Multi-Tenant Query Patterns

​Always Filter by organization_id

​✅ CORRECT: Always include organization_id filter

​❌ WRONG: Missing organization_id filter

​Getting Current Organization

​Multi-Organization Queries

​Database Debugging

​Check RLS Policies

​Test RLS Policies

​Debug Query Performance

​Common RLS Issues

​Issue: “Infinite recursion” error

​Issue: “No rows returned” when rows exist

​Issue: “Cross-organization data leakage”

​Constitution playbook: PF DDL templates

​Module settings table ({core}_module_settings)

​Picklists: pf_picklists and pf_picklist_items

​Custom field definitions: pf_custom_field_definitions

​Example validation_rules JSON

​Related Documentation

​Core Standards

​Development Guides

​Architecture

AI Agent Context

Quick Reference

Database Development Workflow

1. Plan the Schema

2. Author DDL (declarative) and generate a migration

3. Write or verify SQL

4. Test Locally

5. Generate TypeScript Types

6. Update Code

Creating Tables with RLS

Standard Table Structure

When to Include custom_fields

Example: Employee Table

RLS Policy Patterns

Critical Rule: Use SECURITY DEFINER Functions

Standard RLS Policies

SELECT Policy (View)

INSERT Policy (Create)

UPDATE Policy (CRITICAL: Must Include WITH CHECK)

DELETE Policy

Creator-Only Policies

Role-Based Policies

Migration Creation and Testing

Migration Naming

Migration Requirements

Migration Template

Testing Migrations

Migration Best Practices

Custom Fields Implementation

When to Use Custom Fields

Custom Fields Pattern

Querying Custom Fields

Custom Fields Indexing

Multi-Tenant Query Patterns

Always Filter by organization_id

✅ CORRECT: Always include organization_id filter

❌ WRONG: Missing organization_id filter

Getting Current Organization

Multi-Organization Queries

Database Debugging

Check RLS Policies

Test RLS Policies

Debug Query Performance

Common RLS Issues

Issue: “Infinite recursion” error

Issue: “No rows returned” when rows exist

Issue: “Cross-organization data leakage”

Constitution playbook: PF DDL templates

Module settings table (`{core}_module_settings`)

Picklists: `pf_picklists` and `pf_picklist_items`

Custom field definitions: `pf_custom_field_definitions`

Example `validation_rules` JSON

Related Documentation

Core Standards

Development Guides

Architecture