Payroll Multi-Tenancy and UUID Identity Design

Decision Boundary

This design changes payroll persistence, payroll HTTP/JWT auth context, employee service identity resolution, repository tenant scoping, and payroll event identity fields. It does not add a service, datastore, Kafka topic, or response envelope. It does not wait for INFT-69 because payroll no longer calls Core to validate employee target users.

Recommended option: payroll adopts IAM UUID identity directly. payroll_principals is a payroll-local projection keyed by (tenant_id, iam_principal_id) and employees reference its local UUID id. Rejected options:

Keep calling Core by int64 user ID. Rejected because IAM and gateway now provide canonical UUID principal identity.
Accept both X-User-ID and X-Principal-Id. Rejected because IAM ADR-0002 selected a clean contract with no header negotiation.
Store IAM principal UUID directly on every payroll table without a payroll principal table. Rejected because payroll needs a domain principal row for status, display metadata, local foreign keys, and audit joins.

Canonical Auth Contract

Gateway mode:

Required request headers: X-Principal-Id, X-IAM-Tenant-Id.
Required UUID parsing: both headers must parse as UUID.
Optional metadata: X-Principal-Type, X-Tenant-Status, X-User-Roles, X-User-Scopes, X-User-Email, X-User-Name.
Rejected identity boundary: X-User-ID.

JWT mode:

Required claims: principal_id, tenant_id.
Workload subject may be service:{client_id}, but principal_id is always the UUID used for payroll indexing.
Numeric user_id claims are ignored for payroll identity.

Implementation references:

Gateway headers are parsed in internal/middleware/auth.go:263-306.
Direct JWT UUID validation is in internal/middleware/auth.go:367-390.
Typed auth context is in internal/models/auth.go:173-216.

Schema Design

New Table

CREATE TABLE IF NOT EXISTS payroll_principals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    iam_principal_id UUID NOT NULL,
    email TEXT NULL,
    display_name TEXT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT payroll_principals_tenant_id_id_unique UNIQUE (tenant_id, id),
    CONSTRAINT payroll_principals_tenant_iam_unique UNIQUE (tenant_id, iam_principal_id)
);

CREATE INDEX IF NOT EXISTS idx_payroll_principals_tenant_status
    ON payroll_principals(tenant_id, status);

Model and repository references:

internal/models/principal.go:14-23
internal/repositories/postgres/payroll_principal.go:29-70

Tenant Columns

The current migration ground-truths the payroll table inventory and adds tenant_id UUID NOT NULL to tables that exist in the deployed schema. The verified list is:

aca_employee_enrollments, aca_form_1094c, aca_form_1095c, aca_health_plans, aca_monthly_coverage, ach_entries, ach_files, address_geocoding_cache, benefit_enrollment_periods, benefit_plans, break_entries, certified_payroll_reports, compliance_audit_logs, contractor_1099_info, contractor_1099_payments, davis_bacon_projects, deduction_types, electronic_filing_submissions, employee_addresses, employee_benefit_elections, employee_deductions, employee_equity_grants, employee_payroll_records, employee_project_assignments, employee_tax_settings, employees, equity_compensation_plans, equity_transactions, form_1099, garnishments, geofences, on_demand_advances, pay_periods, payment_reconciliations, payment_transactions, payroll_gl_reconciliations, payroll_runs, prevailing_wage_classifications, prevailing_wage_determinations, qualifying_life_events, quarterly_tax_filings, report_executions, report_templates, tasks, tax_brackets, tax_calculation_audit, tax_calculations, tax_deductions, tax_elections, tax_exemptions, tax_jurisdictions, tax_rates, tax_rules, tax_tables, tax_treaties, time_approvals, time_audit_logs, time_entries, time_tracking_sessions, w2_forms, worker_classification_assessments, worker_classification_rules.

The migration uses this safe pattern for each table:

ALTER TABLE <table> ADD COLUMN IF NOT EXISTS tenant_id UUID;
UPDATE <table> SET tenant_id = payroll_default_tenant_id() WHERE tenant_id IS NULL;
ALTER TABLE <table> ALTER COLUMN tenant_id SET DEFAULT payroll_default_tenant_id();
ALTER TABLE <table> ALTER COLUMN tenant_id SET NOT NULL;
CREATE INDEX IF NOT EXISTS idx_<table>_tenant_id ON <table>(tenant_id);

Backfill source:

CREATE OR REPLACE FUNCTION payroll_default_tenant_id()
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
    configured_tenant TEXT := NULLIF(current_setting('app.default_tenant_id', true), '');
BEGIN
    IF configured_tenant IS NULL THEN
        RETURN '00000000-0000-4000-8000-000000000001'::UUID;
    END IF;
    RETURN configured_tenant::UUID;
END;
$$;

Production operators must set app.default_tenant_id to the canonical IAM tenant UUID for the single-tenant payroll database before applying the migration. If it is not set, the migration uses the deterministic bootstrap UUID above; that value is acceptable only for local and ephemeral staging data.

Employee Identity Columns

ALTER TABLE IF EXISTS employees ALTER COLUMN user_id DROP NOT NULL;
ALTER TABLE IF EXISTS employees ADD COLUMN IF NOT EXISTS principal_id UUID NULL;
ALTER TABLE IF EXISTS employees ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employees ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;

CREATE INDEX IF NOT EXISTS idx_employees_tenant_principal
    ON employees(tenant_id, principal_id)
    WHERE principal_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_employees_tenant_user_id
    ON employees(tenant_id, user_id)
    WHERE user_id IS NOT NULL;

ALTER TABLE employees
    ADD CONSTRAINT employees_tenant_principal_fk
    FOREIGN KEY (tenant_id, principal_id)
    REFERENCES payroll_principals(tenant_id, id);

user_id is retained only so old rows and older response models do not break during Release 0 review. New canonical IAM employee writes set principal_id and leave user_id zero/null at the repository boundary (internal/services/employee_service.go:113-138, internal/repositories/postgres/employee.go:407-425).

Actor UUID Columns

The migration adds UUID actor columns to the currently used write/audit surfaces:

ALTER TABLE IF EXISTS pay_periods ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS payroll_runs ADD COLUMN IF NOT EXISTS processed_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS time_entries ADD COLUMN IF NOT EXISTS approved_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS time_entries ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS time_entries ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_tax_settings ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_tax_settings ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_deductions ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_deductions ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_benefit_elections ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_benefit_elections ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_addresses ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_addresses ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS tax_elections ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS tax_elections ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS aca_employee_enrollments ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS aca_employee_enrollments ADD COLUMN IF NOT EXISTS updated_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_project_assignments ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;
ALTER TABLE IF EXISTS employee_equity_grants ADD COLUMN IF NOT EXISTS created_by_principal_id UUID NULL;

RLS Policy Design

RLS policy definitions are created before enforcement:

CREATE POLICY <table>_tenant_isolation ON <table>
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

Policy tables:

payroll_principals, employees, pay_periods, payroll_runs, employee_payroll_records, time_entries, employee_deductions, employee_benefit_elections, employee_addresses, employee_tax_settings, aca_employee_enrollments, employee_project_assignments, employee_equity_grants.

RLS enforcement PR:

ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table> FORCE ROW LEVEL SECURITY;

Enforcement must not ship until repositories set app.tenant_id for every tenant-scoped DB call. Before that point, explicit SQL tenant predicates are the active isolation control.

API Contract Changes

No response envelope change is required.

Request behavior:

Tenant identity comes from gateway/JWT auth context, not from client-controlled body fields on tenant-scoped routes.
POST /api/v1/employees may include principal_id to create an employee for a target principal; if absent, the authenticated principal is used.
tenant_id in the create request is accepted only for privileged admin creation paths and must match authorized context in service policy. The default operational path uses the authenticated tenant.
user_id remains present in models for compatibility but is not an IAM identity input.

Implementation references:

Create request UUID fields: internal/models/employee.go:434-450
Employee create handler principal requirement: internal/handlers/employee_apix.go:139-166
Service-side UUID gate: internal/services/employee_service.go:56-83

Event Contract Changes

No new Kafka topic is introduced. Existing payroll events that carry employee identity add optional fields:

{
  "tenant_id": "uuid",
  "principal_id": "uuid"
}

user_id may remain present during Release 0 for older consumers, but consumers must treat tenant_id and principal_id as authoritative when present. Employee service publish payload already includes these fields (internal/services/employee_service.go:607-611).

Query-Update Inventory

Completed in this branch:

internal/repositories/postgres/employee.go
- SELECT includes tenant and principal columns.
- GetByID, GetByUserID, GetByEmployeeNumber, and update optimistic-lock checks add tenant predicates when context has tenant.
- GetByPrincipalID supports canonical (tenant_id, principal_id) lookup.
- Insert/update write tenant_id, principal_id, and UUID actor columns.
internal/repositories/postgres/payroll_principal.go
- Upsert and lookup by (tenant_id, iam_principal_id).
internal/services/employee_service.go
- Employee creation resolves payroll principals and removes Core user validation.
internal/middleware/auth.go and internal/middleware/auth_enhanced.go
- Canonical gateway, dev, and JWT identity parsing.

Remaining repository files for the follow-up PRs:

internal/repositories/postgres/payroll.go: pay periods, payroll runs, employee payroll records.
internal/repositories/postgres/time_entry.go: time entries, approvals, sessions.
internal/repositories/postgres/deductions.go and related deduction processors.
internal/repositories/postgres/benefits.go and benefit election paths.
internal/repositories/postgres/tax_election.go, employee tax settings, tax calculation audit.
internal/repositories/postgres/employee_address.go.
internal/repositories/postgres/employee_project_assignment.go.
internal/repositories/postgres/equity_compensation.go.
internal/repositories/postgres/aca*.go for ACA enrollments and forms.

Each repository update must add tenant predicates for read/update/delete, write tenant_id on insert, preserve existing pagination/order behavior, and include negative cross-tenant tests.

Rollback SQL

The down migration removes policies, UUID actor columns, employee principal linkage, tenant indexes/columns, payroll_principals, and the default tenant function:

DROP POLICY IF EXISTS <table>_tenant_isolation ON <table>;
ALTER TABLE IF EXISTS employees DROP CONSTRAINT IF EXISTS employees_tenant_principal_fk;
DROP INDEX IF EXISTS idx_employees_tenant_user_id;
DROP INDEX IF EXISTS idx_employees_tenant_principal;
ALTER TABLE IF EXISTS employees DROP COLUMN IF EXISTS updated_by_principal_id;
ALTER TABLE IF EXISTS employees DROP COLUMN IF EXISTS created_by_principal_id;
ALTER TABLE IF EXISTS employees DROP COLUMN IF EXISTS principal_id;
ALTER TABLE <table> DROP COLUMN IF EXISTS tenant_id;
DROP TABLE IF EXISTS payroll_principals;
DROP FUNCTION IF EXISTS payroll_default_tenant_id();

The full generated rollback is in migrations/000023_add_multitenancy_uuid_identity.down.sql:1-131.

Open Questions

RLS setting name: Project Finance has both app.current_tenant_id and app.tenant_id in older migrations. Recommended answer: payroll standardizes on app.tenant_id because current payroll middleware/context code already uses a tenant context key and new migration policy definitions use that setting.
Gateway header naming: IAM contract docs show X-Tenant-Id; deployed gateway emits X-IAM-Tenant-Id. Recommended answer: payroll follows deployed gateway now and tracks the naming convergence in gateway/IAM docs.