Encore OS — Documentation

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

Supabase best practices - Encore-mapped synthesis of current Supabase guidance (RLS init-plan, security_invoker, SECURITY DEFINER, advisors, declarative schema, edge/Deno, types/parity) with the gates that enforce each
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

​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