Data Model Conventions: CL & PM Cores

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

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

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

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