Encore OS Platform Data Dictionary

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

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

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

-- Always use NUMERIC for currency
amount NUMERIC(12, 2) NOT NULL DEFAULT 0.00

-- ✅ CORRECT: Static enum check
CHECK (status IN ('pending', 'approved', 'rejected'))

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

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

-- Organization-scoped uniqueness
UNIQUE(organization_id, employee_number)

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

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

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