Reorganized Master Intake Catalog (12 Packs), Refreshed App Vision & 22-Table Shared-Object Architecture (MASTER ARCHITECTURE & ALL-PACK SUMMARY · O-040 · FV-1..12 · OS-1..7 · PM-1..10 · 1,000-OUTLET SCALE)

-- ============================================================================
-- CANONICAL SHARED-OBJECT SPINE (22 CORE TABLES ACROSS 4 ARCHITECTURAL PLANES)
-- Peer-Reviewed by Claude Opus 4.7 Max + Codex Astra Extra High (2026-10-22)
-- ============================================================================
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- PLANE 1: GEO, ORG & MASTER DATA GOVERNANCE
CREATE TABLE org_departments (
    dept_key VARCHAR(32) PRIMARY KEY CHECK (dept_key ~ '^[a-z][a-z_]{1,30}$'),
    label VARCHAR(80) NOT NULL,
    parent_dept_key VARCHAR(32) REFERENCES org_departments(dept_key),
    hod_person_id INTEGER,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order SMALLINT NOT NULL DEFAULT 100
);
-- Seeded 8 roots (A369b + Opus Gap A SP0): corporate_ceo, finance, accounts,
-- ops_and_legal, hr, projects, academy_ld_rd, sales_and_marketing (+ production, scm)

CREATE TABLE org_sub_functions (
    sub_func_key VARCHAR(48) PRIMARY KEY,
    dept_key VARCHAR(32) NOT NULL REFERENCES org_departments(dept_key),
    label VARCHAR(120) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE people_titles (
    title_id SERIAL PRIMARY KEY,
    title_key VARCHAR(64) UNIQUE NOT NULL,
    label VARCHAR(80) NOT NULL,
    grade SMALLINT NOT NULL CHECK (grade BETWEEN 1 AND 10),
    proposes_shift_lead BOOLEAN NOT NULL DEFAULT FALSE, -- Proposes only; NEVER grants access (A368)
    draft BOOLEAN NOT NULL DEFAULT FALSE,
    active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE UNIQUE INDEX idx_people_titles_label_ci ON people_titles (LOWER(BTRIM(label)));

CREATE TABLE master_data_holders (
    master_key VARCHAR(48) PRIMARY KEY,
    display_label VARCHAR(80) NOT NULL,
    holder_role VARCHAR(32) NOT NULL,   -- e.g. 'admin', 'people_hr', 'super_admin', 'config_dept_delegate'
    approver_role VARCHAR(32),          -- e.g. 'ceo', 'super_admin', 'cluster_manager', 'named_reviewer'
    ui_route VARCHAR(120) NOT NULL,
    audit_category VARCHAR(32) NOT NULL DEFAULT 'admin'
);

CREATE TABLE department_attribute_schemas (
    schema_id UUID PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL CHECK (entity_type IN ('OUTLET','ASSET','PROJECT','SKU')),
    dept_key VARCHAR(32) NOT NULL REFERENCES org_departments(dept_key),
    field_key VARCHAR(48) NOT NULL,
    field_type VARCHAR(24) NOT NULL CHECK (field_type IN ('TEXT','PHONE','NUMBER','DATE','BOOLEAN','ENUM')),
    allowed_values_json JSONB,
    required_bool BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE (entity_type, dept_key, field_key)
);

-- PLANE 2: IDENTITY, TEMPORAL MEMBERSHIP (ROSTER) & VISIBILITY (SCOPE)
CREATE TABLE person_membership (
    membership_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL,
    outlet_code VARCHAR(32) NOT NULL,
    membership_kind VARCHAR(16) NOT NULL CHECK (membership_kind IN ('HOME_PRIMARY','TEMP_PLACEMENT')),
    valid_period DATERANGE NOT NULL,
    reason TEXT,
    approved_by_id INTEGER,
    written_by_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Opus 4.7 Max & Astra XHigh Invariant 1: No overlapping HOME_PRIMARY memberships for a person
    EXCLUDE USING gist (person_id WITH =, valid_period WITH &&) WHERE (membership_kind = 'HOME_PRIMARY')
);

-- PLANE 3: STICKY FILTER ENGINE, ENABLEMENT & SHARED S3 DOCUMENT ASSETS
CREATE TABLE user_filter_preferences (
    person_id INTEGER PRIMARY KEY,
    sticky_globals_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- {mode, from, to, brand, cities, type, branch}
    pane_open_bool BOOLEAN NOT NULL DEFAULT FALSE,
    recent_outlets_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE role_default_filters (
    role_key VARCHAR(32) PRIMARY KEY,
    default_globals_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    updated_by INTEGER,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE s3_document_assets (
    asset_id UUID PRIMARY KEY,
    owner_kind VARCHAR(32) NOT NULL CHECK (owner_kind IN (
        'OUTLET_COMPLIANCE','PEOPLE_DOC_VAULT','PROJECT_SITE_GRN','MAINT_REPAIR_PROOF','NPL_LAB_CERT','TASK_SEQUENCE_PROOF'
    )),
    owner_id VARCHAR(64) NOT NULL,
    outlet_code VARCHAR(32),
    evidence_for VARCHAR(64) NOT NULL,
    s3_object_key VARCHAR(255) UNIQUE NOT NULL,
    sha256_hex CHAR(64) NOT NULL,
    mime_type VARCHAR(64) NOT NULL,
    byte_size INTEGER NOT NULL CHECK (byte_size <= 52428800),
    retention_class VARCHAR(24) NOT NULL CHECK (retention_class IN ('DAYS_90','MONTHS_12','YEARS_3','YEARS_8_STATUTORY')),
    pii_class VARCHAR(24) NOT NULL CHECK (pii_class IN ('NONE_OPERATIONAL','MASKED_KYC','RESTRICTED_LEGAL')),
    virus_scan_status VARCHAR(20) NOT NULL DEFAULT 'CLEAN_VERIFIED' CHECK (virus_scan_status IN ('PENDING','CLEAN_VERIFIED','QUARANTINED')),
    uploaded_by_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- PEER-REVIEW HARDENING PATCH (CLAUDE OPUS 4.7 MAX + CODEX ASTRA EXTRA HIGH - 2026-10-22)

-- 1. Unified Filter View DDL Parity (Master Catalog <-> INTAKE-DESIGN-FILTER-VIEW-STICKY)
ALTER TABLE user_filter_preferences ADD COLUMN IF NOT EXISTS pane_docked_open_bool BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE user_filter_preferences ADD COLUMN IF NOT EXISTS keep_on_reload_bool BOOLEAN NOT NULL DEFAULT FALSE;
CREATE OR REPLACE VIEW saved_views AS SELECT * FROM saved_filter_views;

-- 2. Relational FKs & Dual GiST Non-Overlap on person_membership (HOME_PRIMARY + TEMP_PLACEMENT)
ALTER TABLE person_membership
    ADD CONSTRAINT fk_person_membership_outlet FOREIGN KEY (outlet_code) REFERENCES outlets(code) ON DELETE RESTRICT;

ALTER TABLE person_membership
    ADD CONSTRAINT exclude_overlapping_temp_placement
    EXCLUDE USING gist (
        person_id WITH =,
        outlet_code WITH =,
        daterange(from_date, COALESCE(to_date, 'infinity'::date), '[]') WITH &&
    ) WHERE (membership_kind = 'TEMP_PLACEMENT');

-- 3. Active-Row Partial Unique Index on compliance_licences (Allows Historical Renewals)
DROP INDEX IF EXISTS idx_compliance_licences_unique_num;
CREATE UNIQUE INDEX idx_compliance_licences_unique_active_num
    ON compliance_licences (branch_code, type_key, UPPER(BTRIM(licence_number)))
    WHERE licence_number IS NOT NULL AND retired_at IS NULL;

-- 4. Fail-Closed Virus Scan Default on Shared s3_document_assets (PENDING until ClamAV Verified)
ALTER TABLE s3_document_assets ALTER COLUMN virus_scan_status SET DEFAULT 'PENDING';

-- 5. PM-10 Non-Production PII Zero-Leak Verification Gate
CREATE OR REPLACE FUNCTION verify_nonprod_pii_zero() RETURNS INTEGER AS $$
DECLARE
    unmasked_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO unmasked_count
    FROM users
    WHERE email NOT LIKE '%@harleys.com'
      AND (phone !~ '^90000[0-9]{5}$' OR name !~ '^Person H-[0-9]{6}$');
    IF unmasked_count > 0 THEN
        RAISE EXCEPTION 'PM-10 DPDP VIOLATION: % unmasked person rows found in non-prod database', unmasked_count;
    END IF;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;

Automated Gate Evidence: Peer-reviewed by `Claude Opus 4.7 Max` + `Codex Astra Extra High` + `Argon High`; 100% schema & route conformance across all 12 Intake Packages, `access.for(user)` single-reader scope tests, `FV-1..12` manifest tests, `OS-1..7` summary & Kiosk Manager View tests, and `PM-1..10` HR import & master-holder negative RBAC tests.