Skip to content

armgit / ARES

Public Imported from GitHub
0

ARES - authorized red-team engagement automation with dashboard, campaign scope, module orchestration, OPSEC controls, encrypted vault, and reporting.

273 KB
Raw Blame
"""
ARES Database - PostgreSQL async backend via asyncpg.

Used when ARES_DATABASE_URL starts with postgresql+asyncpg:// or postgresql://.

Install:
    pip install ares-redteam[postgres]      # adds asyncpg>=0.29
    # or:
    pip install asyncpg

Configuration (.env):
    ARES_DATABASE_URL=postgresql+asyncpg://ares_user:strong_password@db:5432/ares_db
    ARES_ENCRYPTION_KEY=<256-bit-encryption-key>

Design:
  - Same public API as AresDatabase (SQLite) - zero changes to server.py or engine.py
  - asyncpg connection pool (min=2, max=10)
  - All credential/token content encrypted at rest via AES-256-GCM (same as SQLite backend)
  - Alembic-managed migrations: `alembic -x db_url=<url> upgrade head`
  - Parameterized queries throughout - no string interpolation

Production checklist:
  □ Create ares_user with CREATEDB privilege or pre-create ares_db
  □ Set ARES_DATABASE_URL in .env (never commit)
  □ Run alembic upgrade head before first start
  □ Set max_connections in postgresql.conf to > (max_workers × pool_max + 5)
"""

from __future__ import annotations

import asyncio
import hashlib
import json
import re
import secrets
import uuid
from datetime import UTC, datetime, timedelta
from typing import Any

from ares.core.logger import get_logger
from ares.core.security import DataEncryptor, hash_password, verify_password
from ares.core.token_sessions import (
    FAMILY_ABSOLUTE_LIFETIME_DAYS,
    FAMILY_RETENTION_DAYS,
    AccessTokenFactory,
    IssuedTokenSession,
    RefreshRotationResult,
    RefreshRotationStatus,
    SessionIssueResult,
    SessionIssueStatus,
    SessionRevocationResult,
    SessionRevocationStatus,
    generate_family_id,
    generate_refresh_token,
    hash_refresh_token,
)
from ares.db.execution_lifecycle import (
    SYSTEM_PRINCIPAL_SUBJECT_REF,
    ExecutionLifecycleStore,
    FixedResult,
    OperationResult,
    valid_uuid,
    validate_postgresql_admission_authority_catalog,
    validate_postgresql_lifecycle_catalog,  # noqa: F401  re-export: ares.db.postgres.<name> is a public hook (integration tests patch it here)
)
from ares.db.websocket_tickets import (
    WEBSOCKET_TICKET_TTL_SECONDS,
    ApiKeyTicketSource,
    BearerTicketSource,
    ConsumedWebSocketTicket,
    WebSocketTicketCredentialKind,
    WebSocketTicketPrincipal,
    generate_websocket_ticket,
    hash_websocket_ticket,
    is_canonical_websocket_ticket,
    is_valid_websocket_principal_role,
    normalize_api_key_scopes,
)

logger = get_logger("ares.db.postgres")

_REFRESH_TOKEN_LOCK_NAMESPACE = b"ares:refresh-token-user:v1\x00"
_CAMPAIGN_DELETE_OPERATION_DOMAIN = b"ares.campaign-delete.compat-operation.v1\x00"
_CAMPAIGN_SLUG_TARGET_DOMAIN = b"ares.campaign-slug.target-uuid.v1\x00"


def _campaign_delete_operation_id(campaign_id: str) -> str:
    """Derive the stable UUIDv4-shaped identity for an implicit delete operation."""
    digest = bytearray(
        hashlib.sha256(_CAMPAIGN_DELETE_OPERATION_DOMAIN + campaign_id.encode("utf-8")).digest()[
            :16
        ]
    )
    digest[6] = (digest[6] & 0x0F) | 0x40
    digest[8] = (digest[8] & 0x3F) | 0x80
    return str(uuid.UUID(bytes=bytes(digest)))


def _campaign_target_uuid(campaign_id: str) -> str:
    """Return campaign_id if it's already a valid UUID, otherwise derive a stable UUIDv4 from its slug."""
    if valid_uuid(campaign_id):
        return campaign_id
    digest = bytearray(
        hashlib.sha256(_CAMPAIGN_SLUG_TARGET_DOMAIN + campaign_id.encode("utf-8")).digest()[:16]
    )
    digest[6] = (digest[6] & 0x0F) | 0x40
    digest[8] = (digest[8] & 0x3F) | 0x80
    return str(uuid.UUID(bytes=bytes(digest)))



def _refresh_token_user_lock_key(user_id: str) -> int:
    """Return a stable signed BIGINT key for a user's refresh-token writes."""
    digest = hashlib.sha256(_REFRESH_TOKEN_LOCK_NAMESPACE + user_id.encode("utf-8")).digest()
    return int.from_bytes(digest[:8], byteorder="big", signed=True)


async def _acquire_refresh_token_user_lock(conn: Any, user_id: str) -> None:
    await conn.execute(
        "SELECT pg_advisory_xact_lock($1::BIGINT)",
        _refresh_token_user_lock_key(user_id),
    )


def _parse_postgres_timestamptz(value: str) -> datetime:
    """Convert the public access-token expiry string to an aware datetime."""
    parsed = datetime.fromisoformat(value)
    if parsed.tzinfo is None:
        parsed = datetime.strptime(value, "%Y-%m-%d %H:%M:%S").replace(tzinfo=UTC)
    return parsed.astimezone(UTC)


# ── Postgres schema DDL ────────────────────────────────────────────────────────
# Equivalent to schema.py but for PostgreSQL syntax.
# Alembic handles migrations; this DDL is the "create if not exists" fallback.

_PG_CREATE_TABLES = """
CREATE TABLE IF NOT EXISTS campaigns (
    id              TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    client          TEXT NOT NULL DEFAULT 'Internal',
    operator        TEXT NOT NULL DEFAULT 'unknown',
    noise_profile   TEXT NOT NULL DEFAULT 'stealth',
    status          TEXT NOT NULL DEFAULT 'created',
    scope_json      TEXT NOT NULL DEFAULT '[]',
    targets_json    TEXT NOT NULL DEFAULT '[]',
    notes           TEXT DEFAULT '',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS module_runs (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    module_id       TEXT NOT NULL,
    outcome         TEXT NOT NULL,
    success         INTEGER NOT NULL DEFAULT 0,
    duration_ms     DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    completed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pg_module_runs_campaign ON module_runs(campaign_id);
CREATE INDEX IF NOT EXISTS idx_pg_module_runs_completed ON module_runs(completed_at);

CREATE TABLE IF NOT EXISTS findings (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    module_id       TEXT NOT NULL,
    title           TEXT NOT NULL,
    description     TEXT NOT NULL,
    severity        TEXT NOT NULL,
    confidence      FLOAT NOT NULL DEFAULT 1.0,
    mitre_technique TEXT,
    mitre_tactic    TEXT,
    cvss_score      FLOAT NOT NULL DEFAULT 0.0,
    cvss_vector     TEXT NOT NULL DEFAULT '',
    trace_id        TEXT NOT NULL DEFAULT '',
    evidence_json   TEXT NOT NULL DEFAULT '{}',
    remediation     TEXT DEFAULT '',
    host            TEXT,
    validated       INTEGER NOT NULL DEFAULT 0,
    false_positive  INTEGER NOT NULL DEFAULT 0,
    discovered_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pg_findings_campaign  ON findings(campaign_id);
CREATE INDEX IF NOT EXISTS idx_pg_findings_severity  ON findings(severity);
CREATE INDEX IF NOT EXISTS idx_pg_findings_fp        ON findings(false_positive);

CREATE TABLE IF NOT EXISTS hosts (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    ip_address      TEXT NOT NULL,
    hostname        TEXT,
    fqdn            TEXT,
    os              TEXT,
    os_version      TEXT,
    domain          TEXT,
    is_dc           INTEGER NOT NULL DEFAULT 0,
    open_ports_json TEXT NOT NULL DEFAULT '[]',
    tags_json       TEXT NOT NULL DEFAULT '[]',
    first_seen      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(campaign_id, ip_address)
);
CREATE INDEX IF NOT EXISTS idx_pg_hosts_campaign ON hosts(campaign_id);
CREATE INDEX IF NOT EXISTS idx_pg_hosts_ip       ON hosts(ip_address);

CREATE TABLE IF NOT EXISTS credentials (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    host_id         TEXT REFERENCES hosts(id) ON DELETE SET NULL,
    username        TEXT NOT NULL,
    secret_enc      TEXT,
    cred_type       TEXT NOT NULL,
    domain          TEXT,
    source_module   TEXT,
    notes           TEXT DEFAULT '',
    cracked         INTEGER NOT NULL DEFAULT 0,
    cracked_value_enc TEXT,
    captured_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pg_creds_campaign ON credentials(campaign_id);

CREATE TABLE IF NOT EXISTS loot (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    host_id         TEXT REFERENCES hosts(id) ON DELETE SET NULL,
    loot_type       TEXT NOT NULL,
    name            TEXT NOT NULL,
    description     TEXT DEFAULT '',
    content_enc     TEXT,
    size_bytes      INTEGER DEFAULT 0,
    path_on_target  TEXT,
    source_module   TEXT,
    tags_json       TEXT NOT NULL DEFAULT '[]',
    captured_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (
    id              SERIAL PRIMARY KEY,
    campaign_id     TEXT REFERENCES campaigns(id) ON DELETE SET NULL,
    actor           TEXT NOT NULL DEFAULT 'system',
    action          TEXT NOT NULL,
    detail          TEXT DEFAULT '',
    module_id       TEXT,
    timestamp       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pg_audit_campaign ON audit_log(campaign_id);

CREATE TABLE IF NOT EXISTS users (
    id              TEXT PRIMARY KEY,
    username        TEXT NOT NULL UNIQUE,
    hashed_password TEXT NOT NULL,
    role            TEXT NOT NULL DEFAULT 'reporter',
    is_active       INTEGER NOT NULL DEFAULT 1,
    created_by      TEXT NOT NULL DEFAULT 'system',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_login      TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_pg_users_username ON users(username);

CREATE TABLE IF NOT EXISTS api_keys (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    key_hash    TEXT NOT NULL,
    key_prefix  TEXT NOT NULL,
    scopes      TEXT NOT NULL DEFAULT 'read',
    is_active   INTEGER NOT NULL DEFAULT 1,
    last_used   TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pg_apikeys_user   ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_pg_apikeys_prefix ON api_keys(key_prefix);

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    is_revoked  INTEGER NOT NULL DEFAULT 0,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    used_at     TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_pg_refresh_user ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_pg_refresh_exp  ON refresh_tokens(expires_at);

CREATE TABLE IF NOT EXISTS revoked_access_tokens (
    jti         TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL,
    revoked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at  TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_pg_rat_expires ON revoked_access_tokens(expires_at);

CREATE TABLE IF NOT EXISTS websocket_tickets (
    ticket_hash       TEXT NOT NULL PRIMARY KEY
                      CONSTRAINT ck_ws_ticket_hash CHECK (
                          ticket_hash ~ '^[0-9a-f]{64}$'
                      ),
    campaign_id       TEXT NOT NULL
                      CONSTRAINT fk_ws_ticket_campaign
                      REFERENCES campaigns(id) ON DELETE CASCADE,
    user_id           TEXT NOT NULL
                      CONSTRAINT fk_ws_ticket_user
                      REFERENCES users(id) ON DELETE CASCADE,
    credential_kind   TEXT NOT NULL
                      CONSTRAINT ck_ws_ticket_kind CHECK (
                          credential_kind IN ('bearer', 'api_key')
                      ),
    bearer_subject    TEXT,
    bearer_jti        TEXT,
    bearer_expires_at TIMESTAMPTZ,
    api_key_id        TEXT
                      CONSTRAINT fk_ws_ticket_api_key
                      REFERENCES api_keys(id) ON DELETE CASCADE,
    required_scope    TEXT,
    created_at        TIMESTAMPTZ NOT NULL,
    expires_at        TIMESTAMPTZ NOT NULL,
    consumed_at       TIMESTAMPTZ,
    CONSTRAINT ck_ws_ticket_created_at CHECK (created_at < expires_at),
    CONSTRAINT ck_ws_ticket_expires_at CHECK (expires_at > created_at),
    CONSTRAINT ck_ws_ticket_consumed_at CHECK (
        consumed_at IS NULL OR consumed_at < expires_at
    ),
    CONSTRAINT ck_ws_ticket_bearer_expires_finite CHECK (
        bearer_expires_at IS NULL OR isfinite(bearer_expires_at)
    ),
    CONSTRAINT ck_ws_ticket_created_finite CHECK (isfinite(created_at)),
    CONSTRAINT ck_ws_ticket_expires_finite CHECK (isfinite(expires_at)),
    CONSTRAINT ck_ws_ticket_consumed_finite CHECK (
        consumed_at IS NULL OR isfinite(consumed_at)
    ),
    CONSTRAINT ck_ws_ticket_time_order CHECK (
        expires_at > created_at
        AND (consumed_at IS NULL OR consumed_at < expires_at)
    ),
    CONSTRAINT ck_ws_ticket_source_shape CHECK (
        (
            credential_kind='bearer'
            AND bearer_subject IS NOT NULL
            AND length(btrim(bearer_subject)) > 0
            AND bearer_subject=btrim(bearer_subject)
            AND bearer_jti IS NOT NULL
            AND length(btrim(bearer_jti)) > 0
            AND bearer_jti=btrim(bearer_jti)
            AND bearer_expires_at IS NOT NULL
            AND api_key_id IS NULL
            AND required_scope IS NULL
        )
        OR
        (
            credential_kind='api_key'
            AND bearer_subject IS NULL
            AND bearer_jti IS NULL
            AND bearer_expires_at IS NULL
            AND api_key_id IS NOT NULL
            AND length(btrim(api_key_id)) > 0
            AND api_key_id=btrim(api_key_id)
            AND required_scope='read'
        )
    )
);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_expires
    ON websocket_tickets(expires_at);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_user
    ON websocket_tickets(user_id);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_campaign
    ON websocket_tickets(campaign_id);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_api_key
    ON websocket_tickets(api_key_id);
"""

_POSTGRES_FALLBACK_DDL_CODES = (
    "campaigns-table",
    "module-runs-table",
    "module-runs-campaign-index",
    "module-runs-completed-index",
    "findings-table",
    "findings-campaign-index",
    "findings-severity-index",
    "findings-false-positive-index",
    "hosts-table",
    "hosts-campaign-index",
    "hosts-ip-index",
    "credentials-table",
    "credentials-campaign-index",
    "loot-table",
    "audit-log-table",
    "audit-log-campaign-index",
    "users-table",
    "users-username-index",
    "api-keys-table",
    "api-keys-user-index",
    "api-keys-prefix-index",
    "refresh-tokens-table",
    "refresh-tokens-user-index",
    "refresh-tokens-expiry-index",
    "revoked-access-tokens-table",
    "revoked-access-tokens-expiry-index",
    "websocket-tickets-table",
    "websocket-tickets-expiry-index",
    "websocket-tickets-user-index",
    "websocket-tickets-campaign-index",
    "websocket-tickets-api-key-index",
)


def _postgres_fallback_statement_spans() -> tuple[tuple[str, int, int], ...]:
    spans: list[tuple[str, int, int]] = []
    start = 0
    for code in _POSTGRES_FALLBACK_DDL_CODES:
        end = _PG_CREATE_TABLES.find(";", start)
        if end < 0:
            raise RuntimeError("Invalid PostgreSQL fallback DDL contract")
        spans.append((code, start + 1, end + 1))
        start = end + 1
    if _PG_CREATE_TABLES[start:].strip():
        raise RuntimeError("Invalid PostgreSQL fallback DDL contract")
    return tuple(spans)


_POSTGRES_FALLBACK_STATEMENT_SPANS = _postgres_fallback_statement_spans()
_POSTGRES_STARTUP_STAGES = frozenset(
    {
        "ownership",
        "fallback-ddl",
        "fallback-validation",
        "managed-validation",
        "unclassified",
    }
)
_POSTGRES_STARTUP_OPERATIONAL_CATEGORIES = frozenset(
    {"none", "timeout", "connection", "database", "runtime", "other"}
)
_POSTGRES_TICKET_RELATION_PROPERTY_BITS = {
    "relkind": 0x001,
    "relpersistence": 0x002,
    "relispartition": 0x004,
    "parent_count": 0x008,
    "child_count": 0x010,
    "relrowsecurity": 0x020,
    "relforcerowsecurity": 0x040,
    "policy_count": 0x080,
    "user_trigger_count": 0x100,
    "user_rule_count": 0x200,
}
_POSTGRES_TICKET_RELATION_PROPERTY_MASK = 0x3FF
_POSTGRES_TICKET_RELATION_AGGREGATE_ONLY = "ticket-relation-metadata:aggregate-comparison-only"
_POSTGRES_TICKET_RELATION_METADATA_PREFIX = "ticket-relation-metadata:"
_POSTGRES_TICKET_RELATION_MISSING = object()
_POSTGRES_TICKET_RELATION_UNREADABLE = object()


def _postgres_ticket_relation_value(
    relation: Any,
    field: str,
) -> object:
    try:
        return relation[field]
    except (KeyError, IndexError):
        return _POSTGRES_TICKET_RELATION_MISSING
    except Exception:
        return _POSTGRES_TICKET_RELATION_UNREADABLE


def _postgres_ticket_relation_is_canonical(relation: Any) -> bool:
    try:
        relation_contract = (
            str(relation["relkind"]),
            str(relation["relpersistence"]),
            bool(relation["relispartition"]),
            bool(relation["relrowsecurity"]),
            bool(relation["relforcerowsecurity"]),
            int(relation["parent_count"]),
            int(relation["child_count"]),
            int(relation["policy_count"]),
            int(relation["user_trigger_count"]),
            int(relation["user_rule_count"]),
        )
    except (KeyError, IndexError, TypeError, ValueError):
        return False
    return relation_contract == (
        "r",
        "p",
        False,
        False,
        False,
        0,
        0,
        0,
        0,
        0,
    )


def _postgres_ticket_relation_diagnostic_masks(
    relation: Any,
) -> tuple[int, int, int, int]:
    mismatched = 0
    alternate = 0
    unexpected = 0
    missing = 0

    for field, expected in (
        ("relkind", "r"),
        ("relpersistence", "p"),
    ):
        bit = _POSTGRES_TICKET_RELATION_PROPERTY_BITS[field]
        value = _postgres_ticket_relation_value(relation, field)
        if value is _POSTGRES_TICKET_RELATION_MISSING:
            missing |= bit
        elif value is _POSTGRES_TICKET_RELATION_UNREADABLE:
            unexpected |= bit
        elif type(value) is str:
            if value != expected:
                mismatched |= bit
        elif type(value) is bytes and len(value) == 1:
            mismatched |= bit
            if value == expected.encode("ascii"):
                alternate |= bit
        else:
            unexpected |= bit

    for field in (
        "relispartition",
        "relrowsecurity",
        "relforcerowsecurity",
    ):
        bit = _POSTGRES_TICKET_RELATION_PROPERTY_BITS[field]
        value = _postgres_ticket_relation_value(relation, field)
        if value is _POSTGRES_TICKET_RELATION_MISSING:
            missing |= bit
        elif value is _POSTGRES_TICKET_RELATION_UNREADABLE:
            unexpected |= bit
        elif type(value) is not bool:
            unexpected |= bit
        elif value:
            mismatched |= bit

    for field in (
        "parent_count",
        "child_count",
        "policy_count",
        "user_trigger_count",
        "user_rule_count",
    ):
        bit = _POSTGRES_TICKET_RELATION_PROPERTY_BITS[field]
        value = _postgres_ticket_relation_value(relation, field)
        if value is _POSTGRES_TICKET_RELATION_MISSING:
            missing |= bit
        elif value is _POSTGRES_TICKET_RELATION_UNREADABLE:
            unexpected |= bit
        elif type(value) is not int:
            unexpected |= bit
        elif value != 0:
            mismatched |= bit

    return mismatched, alternate, unexpected, missing


def _postgres_ticket_relation_diagnostic_invariant(
    masks: tuple[int, int, int, int],
) -> str:
    if (
        type(masks) is not tuple
        or len(masks) != 4
        or any(type(value) is not int for value in masks)
    ):
        return "ticket-validation-unclassified"
    mismatched, alternate, unexpected, missing = masks
    if any(value < 0 or value & ~_POSTGRES_TICKET_RELATION_PROPERTY_MASK for value in masks):
        return "ticket-validation-unclassified"
    if (
        alternate & ~mismatched
        or unexpected & (mismatched | alternate | missing)
        or missing & (mismatched | alternate | unexpected)
    ):
        return "ticket-validation-unclassified"
    if not (mismatched or alternate or unexpected or missing):
        return _POSTGRES_TICKET_RELATION_AGGREGATE_ONLY
    return (
        f"{_POSTGRES_TICKET_RELATION_METADATA_PREFIX}"
        f"m={mismatched:03x};a={alternate:03x};"
        f"x={unexpected:03x};n={missing:03x}"
    )


def _is_postgres_ticket_relation_diagnostic(invariant: str) -> bool:
    if invariant == _POSTGRES_TICKET_RELATION_AGGREGATE_ONLY:
        return True
    prefix = _POSTGRES_TICKET_RELATION_METADATA_PREFIX
    if not invariant.startswith(prefix):
        return False
    parts = invariant[len(prefix) :].split(";")
    if len(parts) != 4:
        return False
    expected_keys = ("m", "a", "x", "n")
    values: list[int] = []
    for part, expected_key in zip(parts, expected_keys, strict=True):
        key, separator, encoded = part.partition("=")
        if (
            separator != "="
            or key != expected_key
            or len(encoded) != 3
            or any(character not in "0123456789abcdef" for character in encoded)
        ):
            return False
        values.append(int(encoded, 16))
    return _postgres_ticket_relation_diagnostic_invariant(tuple(values)) == invariant


_POSTGRES_TICKET_CENSUS_FIELDS = (
    ("r", (("m", 0x3FF, 3), ("a", 0x3FF, 3), ("x", 0x3FF, 3), ("n", 0x3FF, 3), ("g", 0x1, 1))),
    (
        "c",
        (
            ("m", 0x3FFF, 4),
            ("e", 0x1, 1),
            ("o", 0x3FFF, 4),
            ("t", 0x3FFF, 4),
            ("u", 0x3FFF, 4),
            ("d", 0x3FFF, 4),
            ("l", 0x3FFF, 4),
            ("i", 0x3FFF, 4),
            ("a", 0x3FFF, 4),
            ("x", 0x3FFF, 4),
            ("g", 0x1, 1),
        ),
    ),
    (
        "q",
        (
            ("m", 0xFFFF, 4),
            ("e", 0x1, 1),
            ("t", 0xFFFF, 4),
            ("v", 0xFFFF, 4),
            ("f", 0xFFFF, 4),
            ("d", 0xFFFF, 4),
            ("s", 0xFFFF, 4),
            ("b", 0xFFFF, 4),
            ("a", 0xFFFF, 4),
            ("x", 0xFFFF, 4),
            ("g", 0x1, 1),
        ),
    ),
    (
        "p",
        (
            ("l", 0x1, 1),
            ("r", 0x1, 1),
            ("n", 0x1, 1),
            ("i", 0x1, 1),
            ("a", 0x1, 1),
            ("x", 0x1, 1),
            ("g", 0x1, 1),
        ),
    ),
    (
        "h",
        (
            ("t", 0x7FF, 3),
            ("v", 0x7FF, 3),
            ("f", 0x7FF, 3),
            ("d", 0x7FF, 3),
            ("s", 0x7FF, 3),
            ("a", 0x7FF, 3),
            ("x", 0x7FF, 3),
            ("g", 0x1, 1),
        ),
    ),
    (
        "f",
        (
            ("l", 0xF, 1),
            ("s", 0xF, 1),
            ("t", 0xF, 1),
            ("r", 0xF, 1),
            ("u", 0xF, 1),
            ("d", 0xF, 1),
            ("i", 0xF, 1),
            ("a", 0xF, 1),
            ("x", 0xF, 1),
            ("g", 0x1, 1),
        ),
    ),
    (
        "i",
        (
            ("m", 0x3F, 2),
            ("e", 0x1, 1),
            ("o", 0x3F, 2),
            ("b", 0x3F, 2),
            ("u", 0x3F, 2),
            ("p", 0x3F, 2),
            ("v", 0x3F, 2),
            ("r", 0x3F, 2),
            ("l", 0x3F, 2),
            ("k", 0x3F, 2),
            ("n", 0x3F, 2),
            ("c", 0x3F, 2),
            ("d", 0x3F, 2),
            ("a", 0x3F, 2),
            ("j", 0x3F, 2),
            ("s", 0x3F, 2),
            ("t", 0x3F, 2),
            ("y", 0x3F, 2),
            ("h", 0x3F, 2),
            ("x", 0x3F, 2),
            ("g", 0x1, 1),
        ),
    ),
    ("o", (("m", 0x3, 1), ("e", 0x1, 1), ("x", 0x3, 1), ("g", 0x1, 1))),
    ("z", (("q", 0x7F, 2), ("x", 0x7F, 2))),
)


def _new_postgres_ticket_schema_census() -> dict[str, dict[str, int]]:
    return {
        section: {field: 0 for field, _maximum, _width in fields}
        for section, fields in _POSTGRES_TICKET_CENSUS_FIELDS
    }


def _postgres_ticket_schema_census_invariant(
    census: dict[str, dict[str, int]],
) -> str:
    sections: list[str] = []
    for section, fields in _POSTGRES_TICKET_CENSUS_FIELDS:
        values = census.get(section)
        if not isinstance(values, dict):
            return "ticket-validation-unclassified"
        encoded: list[str] = []
        for field, maximum, width in fields:
            value = values.get(field)
            if type(value) is not int or not 0 <= value <= maximum:
                return "ticket-validation-unclassified"
            encoded.append(f"{field}{value:0{width}x}")
        sections.append(f"{section}=" + ",".join(encoded))
    return "ticket-census:" + ";".join(sections)


def _is_postgres_ticket_schema_census(invariant: str) -> bool:
    if not invariant.startswith("ticket-census:"):
        return False
    census = _new_postgres_ticket_schema_census()
    sections = invariant.removeprefix("ticket-census:").split(";")
    if len(sections) != len(_POSTGRES_TICKET_CENSUS_FIELDS):
        return False
    for encoded_section, (section, fields) in zip(
        sections, _POSTGRES_TICKET_CENSUS_FIELDS, strict=True
    ):
        key, separator, encoded_fields = encoded_section.partition("=")
        parts = encoded_fields.split(",")
        if separator != "=" or key != section or len(parts) != len(fields):
            return False
        for part, (field, maximum, width) in zip(parts, fields, strict=True):
            encoded = part.removeprefix(field)
            if (
                not part.startswith(field)
                or len(encoded) != width
                or any(character not in "0123456789abcdef" for character in encoded)
            ):
                return False
            value = int(encoded, 16)
            if value > maximum:
                return False
            census[section][field] = value
    return _postgres_ticket_schema_census_invariant(census) == invariant


def _postgres_internal_character(
    value: object,
) -> tuple[str | None, bool, bool]:
    if type(value) is str:
        return value, False, False
    if type(value) is bytes and len(value) == 0:
        return "", True, False
    if type(value) is bytes and len(value) == 1:
        raw = value[0]
        if raw == 0:
            return "", True, False
        if 1 <= raw <= 0x7F:
            return chr(raw), True, False
    return None, False, True


_POSTGRES_Q78_OPERATION_BITS = {
    "index-catalog": 0x08,
    "canonical-opclasses": 0x10,
    "current-schema": 0x20,
    "referenced-relations": 0x40,
}
_POSTGRES_Q78_SUBPHASES = frozenset(
    {
        "construct",
        "dispatch",
        "server-execute",
        "server-fetch",
        "row-cardinality",
        "field-presence",
        "asyncpg-codec",
        "normalize",
        "aggregate",
        "compare",
        "cleanup",
        "other",
    }
)
_POSTGRES_Q78_SERVER_CATEGORIES = frozenset(
    {
        "syntax",
        "undefined-table",
        "undefined-column",
        "undefined-object",
        "undefined-function-or-operator",
        "datatype-mismatch",
        "cardinality",
        "permission",
        "transaction",
        "connection",
        "other-postgres",
    }
)
_POSTGRES_Q78_CLIENT_CATEGORIES = frozenset(
    {
        "missing-field",
        "wrong-shape",
        "wrong-type",
        "key",
        "index",
        "value",
        "attribute",
        "aggregate-container",
        "other-client",
    }
)
_POSTGRES_Q78_ABSENT_SQLSTATE = "-----"


def _postgres_q78_server_category(sqlstate: str) -> str:
    if sqlstate == "42601":
        return "syntax"
    if sqlstate == "42P01":
        return "undefined-table"
    if sqlstate == "42703":
        return "undefined-column"
    if sqlstate == "42704":
        return "undefined-object"
    if sqlstate in {"42883", "42809"}:
        return "undefined-function-or-operator"
    if sqlstate in {"42804", "42846"}:
        return "datatype-mismatch"
    if sqlstate.startswith("21"):
        return "cardinality"
    if sqlstate == "42501":
        return "permission"
    if sqlstate.startswith(("25", "40")):
        return "transaction"
    if sqlstate.startswith("08"):
        return "connection"
    return "other-postgres"


def _postgres_q78_client_category(error: Exception) -> str:
    if isinstance(error, KeyError):
        return "key"
    if isinstance(error, IndexError):
        return "index"
    if isinstance(error, TypeError):
        return "wrong-type"
    if isinstance(error, ValueError):
        return "value"
    if isinstance(error, AttributeError):
        return "attribute"
    return "other-client"


def _postgres_q78_subphase(error: Exception) -> str:
    raw_state = getattr(error, "sqlstate", None)
    if type(raw_state) is str:
        return "server-execute"
    if isinstance(error, (TimeoutError, ConnectionError)):
        return "dispatch"
    if isinstance(error, (KeyError, IndexError, TypeError, ValueError, AttributeError)):
        return "asyncpg-codec"
    return "server-fetch"


def _postgres_q78_failure(
    error: Exception,
    *,
    subphase: str,
) -> str:
    safe_subphase = subphase if subphase in _POSTGRES_Q78_SUBPHASES else "other"
    raw_state = getattr(error, "sqlstate", None)
    if (
        type(raw_state) is str
        and len(raw_state) == 5
        and all(character in "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" for character in raw_state)
    ):
        state = raw_state
        category = _postgres_q78_server_category(state)
    else:
        state = _POSTGRES_Q78_ABSENT_SQLSTATE
        category = _postgres_q78_client_category(error)
    return f"q78:{safe_subphase}:state={state}:category={category}"


def _is_postgres_q78_failure(value: str) -> bool:
    parts = value.split(":")
    if len(parts) != 4 or parts[0] != "q78":
        return False
    subphase = parts[1]
    state_key, separator, state = parts[2].partition("=")
    category_key, category_separator, category = parts[3].partition("=")
    if (
        subphase not in _POSTGRES_Q78_SUBPHASES
        or separator != "="
        or state_key != "state"
        or category_separator != "="
        or category_key != "category"
    ):
        return False
    if state == _POSTGRES_Q78_ABSENT_SQLSTATE:
        return category in _POSTGRES_Q78_CLIENT_CATEGORIES
    return (
        len(state) == 5
        and all(character in "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" for character in state)
        and category in _POSTGRES_Q78_SERVER_CATEGORIES
        and _postgres_q78_server_category(state) == category
    )


_POSTGRES_Q78_SPLIT_INDEX_FIELDS = (
    ("m", 0x3F, 2),
    ("e", 0x1, 1),
    ("o", 0x3F, 2),
    ("b", 0x3F, 2),
    ("u", 0x3F, 2),
    ("p", 0x3F, 2),
    ("v", 0x3F, 2),
    ("r", 0x3F, 2),
    ("l", 0x3F, 2),
    ("k", 0x3F, 2),
    ("n", 0x3F, 2),
    ("c", 0x3F, 2),
    ("d", 0x3F, 2),
    ("a", 0x3F, 2),
    ("j", 0x3F, 2),
    ("s", 0x3F, 2),
    ("t", 0x3F, 2),
    ("y", 0x3F, 2),
    ("h", 0x3F, 2),
    ("x", 0x3F, 2),
    ("g", 0x1, 1),
)


def _postgres_q78_split_invariant(
    index: dict[str, int],
    *,
    primary_binding: int,
    opclass_missing: int,
    opclass_extra: int,
    opclass_shape: int,
    schema_mismatch: int,
    schema_shape: int,
    reference_missing: int,
    reference_extra: int,
    reference_order: int,
    reference_binding: int,
    reference_shape: int,
    query_failure: int,
) -> str:
    encoded_index: list[str] = []
    for field, maximum, width in _POSTGRES_Q78_SPLIT_INDEX_FIELDS:
        value = index.get(field)
        if type(value) is not int or not 0 <= value <= maximum:
            return "q78-split-unclassified"
        encoded_index.append(f"{field}{value:0{width}x}")
    scalar_values = (
        primary_binding,
        opclass_missing,
        opclass_extra,
        opclass_shape,
        schema_mismatch,
        schema_shape,
        reference_missing,
        reference_extra,
        reference_order,
        reference_binding,
        reference_shape,
        query_failure,
    )
    if (
        any(type(value) is not int or not 0 <= value <= 0xF for value in scalar_values[:-1])
        or type(query_failure) is not int
        or not 0 <= query_failure <= 0xF
    ):
        return "q78-split-unclassified"
    return (
        "q78-split:i="
        + ",".join(encoded_index)
        + f";p=i{primary_binding:x}"
        + f";o=m{opclass_missing:x},e{opclass_extra:x},x{opclass_shape:x}"
        + f";s=m{schema_mismatch:x},x{schema_shape:x}"
        + (
            f";f=m{reference_missing:x},e{reference_extra:x},"
            f"o{reference_order:x},b{reference_binding:x},x{reference_shape:x}"
        )
        + f";z=q{query_failure:x}"
    )


def _is_postgres_q78_split(value: str) -> bool:
    if not value.startswith("q78-split:i="):
        return False
    try:
        index_part, primary_part, opclass_part, schema_part, reference_part, query_part = (
            value.removeprefix("q78-split:").split(";")
        )
        if not index_part.startswith("i="):
            return False
        encoded_fields = index_part.removeprefix("i=").split(",")
        if len(encoded_fields) != len(_POSTGRES_Q78_SPLIT_INDEX_FIELDS):
            return False
        index: dict[str, int] = {}
        for encoded, (field, maximum, width) in zip(
            encoded_fields, _POSTGRES_Q78_SPLIT_INDEX_FIELDS, strict=True
        ):
            raw = encoded.removeprefix(field)
            if (
                not encoded.startswith(field)
                or len(raw) != width
                or any(character not in "0123456789abcdef" for character in raw)
            ):
                return False
            parsed = int(raw, 16)
            if parsed > maximum:
                return False
            index[field] = parsed
        scalar_parts = (
            (primary_part, ("p=i",)),
            (opclass_part, ("o=m", ",e", ",x")),
            (schema_part, ("s=m", ",x")),
            (reference_part, ("f=m", ",e", ",o", ",b", ",x")),
            (query_part, ("z=q",)),
        )
        scalars: list[int] = []
        for encoded, markers in scalar_parts:
            position = 0
            for marker in markers:
                if not encoded.startswith(marker, position):
                    return False
                position += len(marker)
                allowed = "0123456789abcdef"
                if position >= len(encoded) or encoded[position] not in allowed:
                    return False
                scalars.append(int(encoded[position], 16))
                position += 1
            if position != len(encoded):
                return False
    except (TypeError, ValueError):
        return False
    return (
        _postgres_q78_split_invariant(
            index,
            primary_binding=scalars[0],
            opclass_missing=scalars[1],
            opclass_extra=scalars[2],
            opclass_shape=scalars[3],
            schema_mismatch=scalars[4],
            schema_shape=scalars[5],
            reference_missing=scalars[6],
            reference_extra=scalars[7],
            reference_order=scalars[8],
            reference_binding=scalars[9],
            reference_shape=scalars[10],
            query_failure=scalars[11],
        )
        == value
    )


def _postgres_q78_index_split_census(
    rows: object,
    *,
    canonical_opclasses: dict[str, int],
    table_oid: int,
    schema_oid: int,
    schema_name: str,
    constraint_rows: object,
) -> tuple[dict[str, int], int]:
    section = {field: 0 for field, _maximum, _width in _POSTGRES_Q78_SPLIT_INDEX_FIELDS}
    expected = {
        "idx_ws_tickets_api_key": (
            "btree",
            False,
            False,
            True,
            True,
            True,
            1,
            1,
            ("api_key_id",),
            (0,),
            ("text_ops",),
            (True,),
            None,
            None,
        ),
        "idx_ws_tickets_bearer_family": (
            "btree",
            False,
            False,
            True,
            True,
            True,
            1,
            1,
            ("bearer_family_id",),
            (0,),
            ("text_ops",),
            (True,),
            None,
            None,
        ),
        "idx_ws_tickets_campaign": (
            "btree",
            False,
            False,
            True,
            True,
            True,
            1,
            1,
            ("campaign_id",),
            (0,),
            ("text_ops",),
            (True,),
            None,
            None,
        ),
        "idx_ws_tickets_expires": (
            "btree",
            False,
            False,
            True,
            True,
            True,
            1,
            1,
            ("expires_at",),
            (0,),
            ("timestamptz_ops",),
            (True,),
            None,
            None,
        ),
        "idx_ws_tickets_user": (
            "btree",
            False,
            False,
            True,
            True,
            True,
            1,
            1,
            ("user_id",),
            (0,),
            ("text_ops",),
            (True,),
            None,
            None,
        ),
        "websocket_tickets_pkey": (
            "btree",
            True,
            True,
            True,
            True,
            True,
            1,
            1,
            ("ticket_hash",),
            (0,),
            ("text_ops",),
            (True,),
            None,
            None,
        ),
    }
    expected_names = tuple(expected)
    expected_ordinals = {name: ordinal for ordinal, name in enumerate(expected_names)}
    if type(rows) not in {list, tuple}:
        section["x"] = 0x3F
        return section, 1
    actual_names: list[str] = []
    actual: dict[str, tuple[object, ...]] = {}
    index_oids: set[int] = set()
    primary_index_oid: int | None = None
    for raw_row in rows:
        try:
            name = raw_row["index_name"]
            if type(name) is not str:
                raise TypeError
            ordinal = expected_ordinals.get(name)
            bit = 0 if ordinal is None else 1 << ordinal
            index_oid = int(raw_row["index_oid"])
            relkind, relkind_alternate, relkind_unexpected = _postgres_internal_character(
                raw_row["index_relkind"]
            )
            persistence, persistence_alternate, persistence_unexpected = (
                _postgres_internal_character(raw_row["index_relpersistence"])
            )
            operator_classes = tuple(str(value) for value in raw_row["operator_classes"])
            operator_class_oids = tuple(int(value) for value in raw_row["operator_class_oids"])
            canonical_oids = tuple(canonical_opclasses.get(value, -1) for value in operator_classes)
            identity_noncodec_is_exact = (
                int(raw_row["table_oid"]) == table_oid
                and int(raw_row["index_schema_oid"]) == schema_oid
                and str(raw_row["index_schema"]) == schema_name
                and not bool(raw_row["index_is_partition"])
                and tuple(str(value) for value in raw_row["operator_class_namespaces"])
                == ("pg_catalog",)
                and operator_class_oids == canonical_oids
                and index_oid not in index_oids
            )
            identity_is_exact = (
                identity_noncodec_is_exact
                and type(raw_row["index_relkind"]) is str
                and relkind == "i"
                and type(raw_row["index_relpersistence"]) is str
                and persistence == "p"
            )
            if not identity_is_exact and bit:
                if (
                    identity_noncodec_is_exact
                    and not relkind_unexpected
                    and not persistence_unexpected
                    and relkind == "i"
                    and persistence == "p"
                    and (relkind_alternate or persistence_alternate)
                ):
                    section["h"] |= bit
                else:
                    section["y"] |= bit
            index_oids.add(index_oid)
            actual_names.append(name)
            actual[name] = (
                str(raw_row["access_method"]),
                bool(raw_row["indisunique"]),
                bool(raw_row["indisprimary"]),
                bool(raw_row["indisvalid"]),
                bool(raw_row["indisready"]),
                bool(raw_row["indislive"]),
                int(raw_row["indnkeyatts"]),
                int(raw_row["indnatts"]),
                tuple(None if value is None else str(value) for value in raw_row["columns"]),
                tuple(int(value) for value in raw_row["column_options"]),
                operator_classes,
                tuple(bool(value) for value in raw_row["column_collations_match"]),
                None if raw_row["expressions"] is None else str(raw_row["expressions"]),
                None if raw_row["predicate"] is None else str(raw_row["predicate"]),
            )
            if name == "websocket_tickets_pkey":
                primary_index_oid = index_oid
        except (KeyError, IndexError, TypeError, ValueError):
            section["x"] |= 0x3F
            continue
    actual_name_set = set(actual_names)
    for name in set(expected_names) - actual_name_set:
        section["m"] |= 1 << expected_ordinals[name]
    section["e"] = int(
        bool(actual_name_set - set(expected_names)) or len(actual) != len(actual_names)
    )
    for ordinal, name in enumerate(expected_names):
        bit = 1 << ordinal
        if ordinal >= len(actual_names) or actual_names[ordinal] != name:
            section["o"] |= bit
        actual_item = actual.get(name)
        if actual_item is None:
            continue
        expected_item = expected[name]
        for field, position in (
            ("b", 0),
            ("u", 1),
            ("p", 2),
            ("v", 3),
            ("r", 4),
            ("l", 5),
            ("k", 6),
            ("n", 7),
            ("c", 8),
            ("d", 9),
            ("a", 10),
            ("j", 11),
            ("s", 12),
            ("t", 13),
        ):
            if actual_item[position] != expected_item[position]:
                section[field] |= bit
    if actual != expected and not any(section.values()):
        section["g"] = 1
    primary_binding = 1
    if type(constraint_rows) in {list, tuple} and primary_index_oid is not None:
        for raw_row in constraint_rows:
            try:
                if raw_row["conname"] == "websocket_tickets_pkey":
                    primary_binding = int(int(raw_row["constraint_index_oid"]) != primary_index_oid)
                    break
            except (KeyError, IndexError, TypeError, ValueError):
                continue
    return section, primary_binding


_POSTGRES_Q78_SPLIT_INDEX_QUERY = """
SELECT ind.indrelid::bigint AS table_oid,
       ind.indexrelid::bigint AS index_oid,
       idx.relnamespace::bigint AS index_schema_oid,
       idx_nsp.nspname AS index_schema,
       idx.relname AS index_name,
       idx.relkind::text AS index_relkind,
       idx.relpersistence::text AS index_relpersistence,
       idx.relispartition AS index_is_partition,
       am.amname AS access_method,
       ind.indisunique,
       ind.indisprimary,
       ind.indisvalid,
       ind.indisready,
       ind.indislive,
       ind.indnkeyatts,
       ind.indnatts,
       ARRAY(
           SELECT CASE WHEN key.attnum=0 THEN NULL ELSE att.attname END
           FROM unnest(ind.indkey::smallint[]) WITH ORDINALITY
                AS key(attnum, ordinality)
           LEFT JOIN pg_attribute AS att
             ON att.attrelid=ind.indrelid AND att.attnum=key.attnum
           ORDER BY key.ordinality
       ) AS columns,
       ARRAY(
           SELECT option
           FROM unnest(ind.indoption::smallint[]) WITH ORDINALITY
                AS item(option, ordinality)
           ORDER BY item.ordinality
       ) AS column_options,
       ARRAY(
           SELECT opc.opcname
           FROM unnest(ind.indclass::oid[]) WITH ORDINALITY
                AS item(opclass, ordinality)
           JOIN pg_opclass AS opc ON opc.oid=item.opclass
           ORDER BY item.ordinality
       ) AS operator_classes,
       ARRAY(
           SELECT opc.oid::bigint
           FROM unnest(ind.indclass::oid[]) WITH ORDINALITY
                AS item(opclass, ordinality)
           JOIN pg_opclass AS opc ON opc.oid=item.opclass
           ORDER BY item.ordinality
       ) AS operator_class_oids,
       ARRAY(
           SELECT opc_nsp.nspname
           FROM unnest(ind.indclass::oid[]) WITH ORDINALITY
                AS item(opclass, ordinality)
           JOIN pg_opclass AS opc ON opc.oid=item.opclass
           JOIN pg_namespace AS opc_nsp ON opc_nsp.oid=opc.opcnamespace
           ORDER BY item.ordinality
       ) AS operator_class_namespaces,
       ARRAY(
           SELECT item.collation_oid=att.attcollation
           FROM unnest(ind.indcollation::oid[]) WITH ORDINALITY
                AS item(collation_oid, ordinality)
           JOIN unnest(ind.indkey::smallint[]) WITH ORDINALITY
                AS key(attnum, ordinality)
             ON key.ordinality=item.ordinality
           LEFT JOIN pg_attribute AS att
             ON att.attrelid=ind.indrelid AND att.attnum=key.attnum
           ORDER BY item.ordinality
       ) AS column_collations_match,
       pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
       pg_get_expr(ind.indpred, ind.indrelid) AS predicate,
       pg_get_indexdef(ind.indexrelid) AS definition
FROM pg_index AS ind
JOIN pg_class AS idx ON idx.oid=ind.indexrelid
JOIN pg_namespace AS idx_nsp ON idx_nsp.oid=idx.relnamespace
JOIN pg_am AS am ON am.oid=idx.relam
WHERE ind.indrelid=$1::oid
ORDER BY idx.relname
"""


async def _postgres_q78_isolated_call(
    connection: Any,
    method_name: str,
    query: str,
    *args: object,
) -> object:
    method = getattr(connection, method_name)
    transaction_factory = getattr(connection, "transaction", None)
    if callable(transaction_factory):
        async with transaction_factory():
            return await method(query, *args)
    return await method(query, *args)


async def _postgres_q78_split_observation(
    connection: Any,
    *,
    table_oid: int,
    schema_oid: int,
    schema_name: str,
    constraint_rows: object,
) -> str:
    query_failure = 0
    split_index_rows: object = []
    opclass_rows: object = []
    raw_schema: object = None
    referenced_rows: object = []
    try:
        split_index_rows = await _postgres_q78_isolated_call(
            connection,
            "fetch",
            _POSTGRES_Q78_SPLIT_INDEX_QUERY,
            table_oid,
        )
    except asyncio.CancelledError:
        raise
    except Exception:
        query_failure |= 0x1
    try:
        opclass_rows = await _postgres_q78_isolated_call(
            connection,
            "fetch",
            """
            SELECT opc.opcname, opc.oid::bigint AS opclass_oid
            FROM pg_opclass AS opc
            JOIN pg_namespace AS nsp ON nsp.oid=opc.opcnamespace
            JOIN pg_am AS am ON am.oid=opc.opcmethod
            WHERE nsp.nspname='pg_catalog'
              AND am.amname='btree'
              AND opc.opcname=ANY($1::text[])
            """,
            ["text_ops", "timestamptz_ops"],
        )
    except asyncio.CancelledError:
        raise
    except Exception:
        query_failure |= 0x2
    try:
        raw_schema = await _postgres_q78_isolated_call(
            connection,
            "fetchval",
            "SELECT current_schema()",
        )
    except asyncio.CancelledError:
        raise
    except Exception:
        query_failure |= 0x4
    try:
        referenced_rows = await _postgres_q78_isolated_call(
            connection,
            "fetch",
            """
            SELECT rel.relname, rel.oid::bigint AS relation_oid
            FROM pg_class AS rel
            WHERE rel.relnamespace=$1::oid
              AND rel.relname=ANY($2::text[])
            ORDER BY rel.relname
            """,
            schema_oid,
            ["api_keys", "campaigns", "refresh_token_families", "users"],
        )
    except asyncio.CancelledError:
        raise
    except Exception:
        query_failure |= 0x8

    opclass_missing = 0x3
    opclass_extra = 0
    opclass_shape = 0
    canonical_opclasses: dict[str, int] = {}
    if type(opclass_rows) in {list, tuple}:
        try:
            canonical_opclasses = {
                str(row["opcname"]): int(row["opclass_oid"]) for row in opclass_rows
            }
            opclass_missing = int("text_ops" not in canonical_opclasses) | (
                int("timestamptz_ops" not in canonical_opclasses) << 1
            )
            opclass_extra = int(bool(set(canonical_opclasses) - {"text_ops", "timestamptz_ops"}))
        except (KeyError, IndexError, TypeError, ValueError):
            opclass_shape = 1
    else:
        opclass_shape = 1

    index, primary_binding = _postgres_q78_index_split_census(
        split_index_rows,
        canonical_opclasses=canonical_opclasses,
        table_oid=table_oid,
        schema_oid=schema_oid,
        schema_name=schema_name,
        constraint_rows=constraint_rows,
    )
    schema_shape = int(type(raw_schema) is not str)
    schema_mismatch = int(type(raw_schema) is str and raw_schema != schema_name)

    expected_reference_names = (
        "api_keys",
        "campaigns",
        "refresh_token_families",
        "users",
    )
    reference_missing = 0xF
    reference_extra = 0
    reference_order = 0
    reference_binding = 0x7
    reference_shape = 0
    reference_oids: dict[str, int] = {}
    reference_names: list[str] = []
    if type(referenced_rows) in {list, tuple}:
        try:
            reference_names = [str(row["relname"]) for row in referenced_rows]
            reference_oids = {
                str(row["relname"]): int(row["relation_oid"]) for row in referenced_rows
            }
            reference_missing = 0
            for ordinal, name in enumerate(expected_reference_names):
                if name not in reference_oids:
                    reference_missing |= 1 << ordinal
                if ordinal >= len(reference_names) or reference_names[ordinal] != name:
                    reference_order |= 1 << ordinal
            reference_extra = int(
                bool(set(reference_oids) - set(expected_reference_names))
                or len(reference_oids) != len(reference_names)
            )
            reference_binding = 0
            expected_constraint_names = (
                "fk_ws_ticket_api_key",
                "fk_ws_ticket_campaign",
                "fk_ws_ticket_bearer_family",
                "fk_ws_ticket_user",
            )
            if type(constraint_rows) not in {list, tuple}:
                reference_binding = 0xF
            else:
                constraints = {str(row["conname"]): row for row in constraint_rows}
                for ordinal, (constraint_name, relation_name) in enumerate(
                    zip(expected_constraint_names, expected_reference_names, strict=True)
                ):
                    row = constraints.get(constraint_name)
                    if row is None or int(row["referenced_oid"]) != reference_oids.get(
                        relation_name
                    ):
                        reference_binding |= 1 << ordinal
        except (KeyError, IndexError, TypeError, ValueError):
            reference_shape = 1
    else:
        reference_shape = 1
    return _postgres_q78_split_invariant(
        index,
        primary_binding=primary_binding,
        opclass_missing=opclass_missing,
        opclass_extra=opclass_extra,
        opclass_shape=opclass_shape,
        schema_mismatch=schema_mismatch,
        schema_shape=schema_shape,
        reference_missing=reference_missing,
        reference_extra=reference_extra,
        reference_order=reference_order,
        reference_binding=reference_binding,
        reference_shape=reference_shape,
        query_failure=query_failure,
    )


_POSTGRES_TICKET_CENSUS_REJECTION_FIELDS = {
    "ticket-relation-present": "r.n",
    "ticket-relation-query": "z.q",
    "ticket-relation-metadata": "r.m",
    "ticket-columns": "c",
    "ticket-constraint-inventory": "q.m",
    "ticket-canonical-opclasses": "o.m",
    "ticket-index-identity": "i.y",
    "ticket-index-inventory": "i",
    "ticket-constraint-definition-primary": "q",
    "ticket-constraint-definition-campaign": "q",
    "ticket-constraint-definition-user": "q",
    "ticket-constraint-definition-api-key": "q",
    "ticket-reference-inventory": "f.i",
    "ticket-constraint-table-binding": "q.b",
    "ticket-foreign-key-campaign": "f",
    "ticket-foreign-key-user": "f",
    "ticket-foreign-key-api-key": "f",
    "ticket-primary-key-binding": "p",
    "ticket-check-hash": "h",
    "ticket-check-kind": "h",
    "ticket-check-created-at": "h",
    "ticket-check-expires-at": "h",
    "ticket-check-consumed-at": "h",
    "ticket-check-bearer-expires-finite": "h",
    "ticket-check-created-finite": "h",
    "ticket-check-expires-finite": "h",
    "ticket-check-consumed-finite": "h",
    "ticket-check-time-order": "h",
    "ticket-check-source-shape": "h",
    "ticket-validation-unclassified": "z.x",
}


def _postgres_ticket_column_census(
    rows: object,
    expected: list[tuple[str, str, bool, str, str, bool, str | None]],
) -> dict[str, int]:
    section = _new_postgres_ticket_schema_census()["c"]
    if not isinstance(rows, (list, tuple)):
        section["x"] = 0xFFF
        return section

    expected_names = tuple(item[0] for item in expected)
    actual_names: list[str | None] = []
    by_name: dict[str, object] = {}
    for row in rows:
        try:
            raw_name = row["column_name"]
        except (KeyError, TypeError, IndexError):
            actual_names.append(None)
            section["x"] = 0xFFF
            continue
        if type(raw_name) is not str:
            actual_names.append(None)
            section["x"] = 0xFFF
            continue
        actual_names.append(raw_name)
        if raw_name in by_name:
            section["e"] = 1
        by_name[raw_name] = row

    actual_name_set = {name for name in actual_names if name is not None}
    section["e"] |= int(bool(actual_name_set - set(expected_names)))
    if tuple(actual_names) != expected_names:
        for ordinal, expected_name in enumerate(expected_names):
            if ordinal >= len(actual_names) or actual_names[ordinal] != expected_name:
                section["o"] |= 1 << ordinal

    for ordinal, expected_item in enumerate(expected):
        bit = 1 << ordinal
        row = by_name.get(expected_item[0])
        if row is None:
            section["m"] |= bit
            continue
        try:
            raw_type = row["data_type"]  # type: ignore[index]
            raw_nullability = row["attnotnull"]  # type: ignore[index]
            raw_identity = row["attidentity"]  # type: ignore[index]
            raw_generated = row["attgenerated"]  # type: ignore[index]
            raw_collation = row["collation_is_default"]  # type: ignore[index]
            raw_default = row["column_default"]  # type: ignore[index]
        except (KeyError, TypeError, IndexError):
            section["x"] |= bit
            continue

        if type(raw_type) is not str:
            section["x"] |= bit
        elif raw_type != expected_item[1]:
            section["t"] |= bit
        if type(raw_nullability) is not bool:
            section["x"] |= bit
        elif raw_nullability is not expected_item[2]:
            section["u"] |= bit

        for raw_internal, expected_internal in (
            (raw_identity, expected_item[3]),
            (raw_generated, expected_item[4]),
        ):
            semantic, alternate, unexpected = _postgres_internal_character(raw_internal)
            if unexpected:
                section["x"] |= bit
            elif semantic != expected_internal:
                section["i"] |= bit
            elif alternate:
                section["a"] |= bit

        if type(raw_collation) is not bool:
            section["x"] |= bit
        elif raw_collation is not expected_item[5]:
            section["l"] |= bit
        if raw_default is None:
            actual_default = None
        elif type(raw_default) is str:
            actual_default = raw_default
        else:
            actual_default = None
            section["x"] |= bit
        if actual_default != expected_item[6]:
            section["d"] |= bit
    return section


_POSTGRES_TICKET_INVARIANTS = frozenset(
    {
        "ticket-relation-present",
        "ticket-relation-query",
        "ticket-relation-metadata",
        "ticket-columns",
        "ticket-constraint-inventory",
        "ticket-canonical-opclasses",
        "ticket-index-identity",
        "ticket-index-inventory",
        "ticket-constraint-definition-primary",
        "ticket-constraint-definition-campaign",
        "ticket-constraint-definition-user",
        "ticket-constraint-definition-api-key",
        "ticket-reference-inventory",
        "ticket-constraint-table-binding",
        "ticket-foreign-key-campaign",
        "ticket-foreign-key-user",
        "ticket-foreign-key-api-key",
        "ticket-primary-key-binding",
        "ticket-check-hash",
        "ticket-check-kind",
        "ticket-check-created-at",
        "ticket-check-expires-at",
        "ticket-check-consumed-at",
        "ticket-check-bearer-expires-finite",
        "ticket-check-created-finite",
        "ticket-check-expires-finite",
        "ticket-check-consumed-finite",
        "ticket-check-time-order",
        "ticket-check-source-shape",
        "ticket-validation-unclassified",
    }
)
_POSTGRES_OWNERSHIP_INVARIANTS = frozenset(
    {
        "ownership-relation-query",
        "ownership-relation-metadata",
        "ownership-revision-query",
        "ownership-revision-cardinality",
        "ownership-revision-value",
        "ownership-known-older-revision",
        "ownership-unclassified",
    }
)
_POSTGRES_STARTUP_INVARIANTS = (
    frozenset(_POSTGRES_FALLBACK_DDL_CODES)
    | _POSTGRES_TICKET_INVARIANTS
    | _POSTGRES_OWNERSHIP_INVARIANTS
    | frozenset({"fallback-ddl-unclassified", "managed-validation-unclassified"})
)
_POSTGRES_STARTUP_TRACE_CODES = frozenset(
    {"fallback-entered", "fallback-ddl-complete", "startup-ready"}
)


def _postgres_operational_category(error: BaseException) -> str:
    if isinstance(error, TimeoutError):
        return "timeout"
    if isinstance(error, ConnectionError):
        return "connection"
    error_type_names = {base.__name__ for base in type(error).__mro__}
    if error_type_names & {
        "PostgresError",
        "InterfaceError",
        "ConnectionDoesNotExistError",
        "CannotConnectNowError",
    }:
        return "database"
    if isinstance(error, RuntimeError):
        return "runtime"
    return "other"


class _PostgresStartupDiagnosticError(RuntimeError):
    def __init__(
        self,
        public_message: str,
        *,
        diagnostic_stage: str,
        diagnostic_invariant: str,
        operational_category: str = "none",
        q78_failure: str = "",
        q78_split: str = "",
    ) -> None:
        super().__init__(public_message)
        self.diagnostic_stage = (
            diagnostic_stage if diagnostic_stage in _POSTGRES_STARTUP_STAGES else "unclassified"
        )
        self.diagnostic_invariant = (
            diagnostic_invariant
            if (
                diagnostic_invariant in _POSTGRES_STARTUP_INVARIANTS
                or _is_postgres_ticket_relation_diagnostic(diagnostic_invariant)
                or _is_postgres_ticket_schema_census(diagnostic_invariant)
                or (
                    diagnostic_invariant.startswith(("managed-check-", "managed-foreign-"))
                    and len(diagnostic_invariant)
                    in {len("managed-check-00"), len("managed-foreign-00")}
                    and diagnostic_invariant[-2:].isdigit()
                )
            )
            else "ownership-unclassified"
        )
        self.operational_category = (
            operational_category
            if operational_category in _POSTGRES_STARTUP_OPERATIONAL_CATEGORIES
            else "other"
        )
        self.q78_failure = q78_failure if _is_postgres_q78_failure(q78_failure) else ""
        self.q78_split = q78_split if _is_postgres_q78_split(q78_split) else ""


def _postgres_startup_diagnostic_label(error: BaseException) -> str:
    if not isinstance(error, _PostgresStartupDiagnosticError):
        return "unclassified"
    if error.diagnostic_invariant in {
        "ownership-unclassified",
        "fallback-ddl-unclassified",
        "managed-validation-unclassified",
        "ticket-validation-unclassified",
    }:
        return "unclassified"
    if (
        error.diagnostic_stage not in _POSTGRES_STARTUP_STAGES
        or (
            error.diagnostic_invariant not in _POSTGRES_STARTUP_INVARIANTS
            and not _is_postgres_ticket_relation_diagnostic(error.diagnostic_invariant)
            and not _is_postgres_ticket_schema_census(error.diagnostic_invariant)
        )
        or error.operational_category not in _POSTGRES_STARTUP_OPERATIONAL_CATEGORIES
    ):
        return "unclassified"
    label = f"{error.diagnostic_stage}:{error.diagnostic_invariant}:{error.operational_category}"
    if error.q78_failure or error.q78_split:
        if not error.q78_failure or not error.q78_split:
            return "unclassified"
        label = f"{label}:{error.q78_failure}:{error.q78_split}"
    return label


def _postgres_startup_error(
    public_message: str,
    *,
    stage: str,
    invariant: str,
    cause: BaseException | None = None,
) -> _PostgresStartupDiagnosticError:
    category = "none" if cause is None else _postgres_operational_category(cause)
    return _PostgresStartupDiagnosticError(
        public_message,
        diagnostic_stage=stage,
        diagnostic_invariant=invariant,
        operational_category=category,
    )


def _postgres_fallback_failure_invariant(error: BaseException) -> str:
    raw_position = getattr(error, "position", None)
    if not isinstance(raw_position, (int, str)):
        return "fallback-ddl-unclassified"
    try:
        position = int(raw_position)
    except (TypeError, ValueError):
        return "fallback-ddl-unclassified"
    for code, start, end in _POSTGRES_FALLBACK_STATEMENT_SPANS:
        if start <= position <= end:
            return code
    return "fallback-ddl-unclassified"


def _postgres_restage_startup_error(
    error: _PostgresStartupDiagnosticError,
    stage: str,
) -> _PostgresStartupDiagnosticError:
    return _PostgresStartupDiagnosticError(
        str(error),
        diagnostic_stage=stage,
        diagnostic_invariant=error.diagnostic_invariant,
        operational_category=error.operational_category,
        q78_failure=error.q78_failure,
        q78_split=error.q78_split,
    )


def _postgres_ticket_schema_error(
    invariant: str,
    *,
    cause: Exception | None = None,
    q78_failure: str = "",
    q78_split: str = "",
) -> _PostgresStartupDiagnosticError:
    error = _postgres_startup_error(
        "Incompatible WebSocket ticket schema",
        stage="managed-validation",
        invariant=invariant,
        cause=cause,
    )
    error.q78_failure = q78_failure if _is_postgres_q78_failure(q78_failure) else ""
    error.q78_split = q78_split if _is_postgres_q78_split(q78_split) else ""
    return error


_POSTGRES_MANAGED_REVISION = "0011"
_POSTGRES_OLDER_REVISIONS = frozenset(
    {
        "0001",
        "0002",
        "0003",
        "0004",
        "0005",
        "0006",
        "0007",
        "0008",
        "0009",
        "0010",
    }
)
_POSTGRES_MIGRATION_REQUIRED_ERROR = "PostgreSQL schema migration required"
_POSTGRES_MANAGED_SCHEMA_ERROR = "Incompatible managed PostgreSQL schema"
_POSTGRES_SCHEMA_VALIDATION_ERROR = "PostgreSQL schema validation failed"


class _PostgresMigrationRequiredError(_PostgresStartupDiagnosticError):
    pass


class _PostgresManagedSchemaError(_PostgresStartupDiagnosticError):
    pass


_POSTGRES_MANAGED_TABLES = frozenset(
    {
        "campaigns",
        "module_runs",
        "findings",
        "hosts",
        "credentials",
        "loot",
        "audit_log",
        "users",
        "api_keys",
        "refresh_tokens",
        "refresh_token_families",
        "revoked_access_tokens",
        "rate_limit_events",
        "websocket_tickets",
    }
)

_POSTGRES_MANAGED_INDEXES = (
    ("module_runs", "idx_module_runs_campaign", ("campaign_id",)),
    ("module_runs", "idx_module_runs_completed", ("completed_at",)),
    ("findings", "idx_findings_campaign", ("campaign_id",)),
    ("findings", "idx_findings_severity", ("severity",)),
    ("findings", "idx_findings_fp", ("false_positive",)),
    ("findings", "idx_findings_mitre", ("mitre_technique",)),
    ("findings", "idx_findings_cvss", ("cvss_score",)),
    ("hosts", "idx_hosts_campaign", ("campaign_id",)),
    ("hosts", "idx_hosts_ip", ("ip_address",)),
    ("hosts", "idx_hosts_domain", ("domain",)),
    ("credentials", "idx_creds_campaign", ("campaign_id",)),
    ("credentials", "idx_creds_username", ("username",)),
    ("credentials", "idx_creds_type", ("cred_type",)),
    (
        "credentials",
        "ix_credentials_execution_authority",
        ("campaign_id", "execution_authority_state", "id"),
    ),
    ("loot", "idx_loot_campaign", ("campaign_id",)),
    ("loot", "idx_loot_type", ("loot_type",)),
    ("audit_log", "idx_audit_campaign", ("campaign_id",)),
    ("audit_log", "idx_audit_actor", ("actor",)),
    ("audit_log", "idx_audit_action", ("action",)),
    ("users", "idx_users_username", ("username",)),
    ("users", "idx_users_role", ("role",)),
    ("api_keys", "idx_apikeys_user", ("user_id",)),
    ("api_keys", "idx_apikeys_prefix", ("key_prefix",)),
    ("refresh_tokens", "idx_refresh_user", ("user_id",)),
    ("refresh_tokens", "idx_refresh_exp", ("expires_at",)),
    (
        "refresh_token_families",
        "idx_refresh_family_user_state_exp",
        ("user_id", "state", "absolute_expires_at"),
    ),
    (
        "refresh_token_families",
        "idx_refresh_family_retain",
        ("retain_until",),
    ),
    (
        "refresh_tokens",
        "idx_refresh_family_generation",
        ("family_id", "generation"),
    ),
    (
        "refresh_tokens",
        "uq_refresh_token_one_active",
        ("family_id",),
    ),
    ("revoked_access_tokens", "idx_rat_expires", ("expires_at",)),
    ("rate_limit_events", "idx_rle_ip", ("ip_address",)),
    ("rate_limit_events", "idx_rle_timestamp", ("timestamp",)),
    ("rate_limit_events", "idx_rle_blocked", ("blocked",)),
)

_POSTGRES_MANAGED_PRIMARY_KEYS = (
    ("campaigns", "campaigns_pkey", ("id",)),
    ("module_runs", "module_runs_pkey", ("id",)),
    ("findings", "findings_pkey", ("id",)),
    ("hosts", "hosts_pkey", ("id",)),
    ("credentials", "credentials_pkey", ("id",)),
    ("loot", "loot_pkey", ("id",)),
    ("audit_log", "audit_log_pkey", ("id",)),
    ("users", "users_pkey", ("id",)),
    ("api_keys", "api_keys_pkey", ("id",)),
    ("refresh_tokens", "refresh_tokens_pkey", ("id",)),
    (
        "refresh_token_families",
        "refresh_token_families_pkey",
        ("id",),
    ),
    (
        "revoked_access_tokens",
        "revoked_access_tokens_pkey",
        ("jti",),
    ),
    ("rate_limit_events", "rate_limit_events_pkey", ("id",)),
)

_POSTGRES_MANAGED_FOREIGN_KEYS = (
    (
        "module_runs",
        "fk_module_runs_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "findings",
        "fk_findings_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "hosts",
        "fk_hosts_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "credentials",
        "fk_credentials_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "credentials",
        "fk_credentials_host",
        ("host_id",),
        "hosts",
        ("id",),
        "SET NULL",
        "n",
    ),
    (
        "loot",
        "fk_loot_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "loot",
        "fk_loot_host",
        ("host_id",),
        "hosts",
        ("id",),
        "SET NULL",
        "n",
    ),
    (
        "audit_log",
        "fk_audit_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "SET NULL",
        "n",
    ),
    (
        "api_keys",
        "fk_api_keys_user",
        ("user_id",),
        "users",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "refresh_tokens",
        "fk_refresh_tokens_user",
        ("user_id",),
        "users",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "refresh_token_families",
        "fk_refresh_family_user",
        ("user_id",),
        "users",
        ("id",),
        "CASCADE",
        "c",
    ),
    (
        "refresh_tokens",
        "fk_refresh_token_family_owner",
        ("family_id", "user_id"),
        "refresh_token_families",
        ("id", "user_id"),
        "CASCADE",
        "c",
    ),
    (
        "refresh_tokens",
        "fk_refresh_token_parent",
        ("family_id", "parent_id"),
        "refresh_tokens",
        ("family_id", "id"),
        "CASCADE",
        "c",
    ),
)

_POSTGRES_MANAGED_UNIQUES = (
    ("findings", "uq_findings_campaign_id_id", ("campaign_id", "id")),
    ("credentials", "uq_credentials_campaign_id_id", ("campaign_id", "id")),
    ("hosts", "uq_hosts_campaign_id_id", ("campaign_id", "id")),
    ("loot", "uq_loot_campaign_id_id", ("campaign_id", "id")),
    ("hosts", "uq_hosts_campaign_ip", ("campaign_id", "ip_address")),
    ("users", "uq_users_username", ("username",)),
    (
        "refresh_token_families",
        "uq_refresh_family_owner",
        ("id", "user_id"),
    ),
    (
        "refresh_tokens",
        "uq_refresh_token_family_hash",
        ("family_id", "id"),
    ),
    (
        "refresh_tokens",
        "uq_refresh_token_family_generation",
        ("family_id", "generation"),
    ),
    ("refresh_tokens", "uq_refresh_token_parent", ("parent_id",)),
)

_POSTGRES_MANAGED_FINITE_CHECKS = (
    ("campaigns", "created_at", False),
    ("campaigns", "updated_at", False),
    ("module_runs", "completed_at", False),
    ("findings", "discovered_at", False),
    ("hosts", "first_seen", False),
    ("hosts", "last_seen", False),
    ("credentials", "captured_at", False),
    ("loot", "captured_at", False),
    ("audit_log", "timestamp", False),
    ("users", "created_at", False),
    ("users", "last_login", True),
    ("api_keys", "last_used", True),
    ("api_keys", "expires_at", True),
    ("api_keys", "created_at", False),
    ("refresh_tokens", "expires_at", False),
    ("refresh_tokens", "created_at", False),
    ("refresh_tokens", "used_at", True),
    ("refresh_tokens", "revoked_at", True),
    ("refresh_token_families", "created_at", False),
    ("refresh_token_families", "absolute_expires_at", False),
    ("refresh_token_families", "revoked_at", True),
    ("refresh_token_families", "retain_until", False),
    ("rate_limit_events", "timestamp", False),
    ("revoked_access_tokens", "revoked_at", False),
    ("revoked_access_tokens", "expires_at", False),
)

_POSTGRES_V11_CREDENTIAL_AUTHORITY_COLUMNS = frozenset(
    {
        "execution_authority_state",
        "execution_authority_revision",
        "execution_authority_binding_digest",
        "execution_authority_latest_operation_id",
        "execution_authority_latest_operation_base_revision",
        "execution_authority_latest_operation_code",
    }
)


def _postgres_finite_check_name(table: str, column: str) -> str:
    if table == "module_runs":
        return "ck_module_runs_completed_at_finite"
    if table == "audit_log":
        return "ck_audit_log_timestamp_finite"
    if table == "rate_limit_events":
        return "ck_rate_limit_events_timestamp_finite"
    if table == "refresh_tokens" and column == "revoked_at":
        return "ck_refresh_token_revoked_finite"
    if table == "refresh_token_families":
        return {
            "created_at": "ck_refresh_family_created_finite",
            "absolute_expires_at": "ck_refresh_family_expires_finite",
            "revoked_at": "ck_refresh_family_revoked_finite",
            "retain_until": "ck_refresh_family_retain_finite",
        }[column]
    return f"ck_{table}_{column}_finite"


def _postgres_constraint_definition(value: object) -> str:
    normalized = " ".join(str(value).split())
    return re.sub(r'"([a-z_][a-z0-9_]*)"', r"\1", normalized)


def _postgres_index_opclass(table: str, column: str) -> str:
    if (table, column) in {
        ("findings", "false_positive"),
        ("rate_limit_events", "blocked"),
    }:
        return "int4_ops"
    if (table, column) == ("findings", "cvss_score"):
        return "float8_ops"
    if (table, column) in {
        ("module_runs", "completed_at"),
        ("refresh_tokens", "expires_at"),
        ("refresh_token_families", "absolute_expires_at"),
        ("refresh_token_families", "retain_until"),
        ("revoked_access_tokens", "expires_at"),
        ("rate_limit_events", "timestamp"),
    }:
        return "timestamptz_ops"
    if (table, column) == ("refresh_tokens", "generation"):
        return "int8_ops"
    return "text_ops"


def _classify_postgres_revision(values: tuple[object, ...]) -> str:
    if len(values) != 1:
        raise _PostgresManagedSchemaError(
            _POSTGRES_MANAGED_SCHEMA_ERROR,
            diagnostic_stage="ownership",
            diagnostic_invariant="ownership-revision-cardinality",
        )
    if not isinstance(values[0], str):
        raise _PostgresManagedSchemaError(
            _POSTGRES_MANAGED_SCHEMA_ERROR,
            diagnostic_stage="ownership",
            diagnostic_invariant="ownership-revision-value",
        )
    revision = values[0]
    if revision == _POSTGRES_MANAGED_REVISION:
        return revision
    if revision in _POSTGRES_OLDER_REVISIONS:
        raise _PostgresMigrationRequiredError(
            _POSTGRES_MIGRATION_REQUIRED_ERROR,
            diagnostic_stage="ownership",
            diagnostic_invariant="ownership-known-older-revision",
        )
    raise _PostgresManagedSchemaError(
        _POSTGRES_MANAGED_SCHEMA_ERROR,
        diagnostic_stage="ownership",
        diagnostic_invariant="ownership-revision-value",
    )


class PostgresDatabase:
    """
    Async PostgreSQL database backend via asyncpg.
    Public API is identical to AresDatabase (SQLite) - drop-in replacement.

    Usage:
        db = await PostgresDatabase.create(
            dsn            = "postgresql+asyncpg://user:pass@host/db",
            encryption_key = settings.encryption_key_value,
        )
        app.state.db = db
    """

    def __init__(
        self,
        dsn: str,
        encryption_key: str | None = None,
        pool_min: int = 2,
        pool_max: int = 10,
    ) -> None:
        # Strip SQLAlchemy dialect prefix - asyncpg uses plain postgres:// DSN
        self._dsn = dsn.replace("postgresql+asyncpg://", "postgresql://").replace(
            "postgres+asyncpg://", "postgres://"
        )
        self._enc: DataEncryptor | None = DataEncryptor(encryption_key) if encryption_key else None
        self._pool_min = pool_min
        self._pool_max = pool_max
        self._pool: Any = None  # asyncpg.Pool
        self._startup_trace: list[str] = []
        self._sso_schema_ensured: bool = False

    # ── Lifecycle ──────────────────────────────────────────────────────────────

    async def connect(self) -> "PostgresDatabase":
        try:
            import asyncpg
        except ImportError as exc:
            raise RuntimeError(
                "asyncpg is required for PostgreSQL support. "
                "Install: pip install ares-redteam[postgres]"
            ) from exc
        pool = await asyncpg.create_pool(
            self._dsn,
            min_size=self._pool_min,
            max_size=self._pool_max,
            command_timeout=30,
        )
        self._pool = pool
        try:
            await self._init_schema()
        except Exception as primary:
            await self._close_failed_startup_pool(pool, primary)
            raise
        except asyncio.CancelledError as primary:
            await self._close_failed_startup_pool(pool, primary)
            raise
        self._record_startup_trace("startup-ready")
        logger.info("pg_db_ready")
        return self

    async def _close_failed_startup_pool(
        self,
        pool: Any,
        primary: BaseException,
    ) -> None:
        try:
            await pool.close()
        except Exception as cleanup_error:
            cleanup_category = _postgres_operational_category(cleanup_error)
            primary.add_note(f"PostgreSQL startup cleanup failure [pool-close: {cleanup_category}]")
        finally:
            if self._pool is pool:
                self._pool = None

    def _record_startup_trace(self, code: str) -> None:
        if code not in _POSTGRES_STARTUP_TRACE_CODES:
            raise RuntimeError("Invalid PostgreSQL startup trace identifier")
        self._startup_trace.append(code)

    async def __aenter__(self) -> "PostgresDatabase":
        return await self.connect()

    async def __aexit__(self, *_: object) -> None:
        await self.close()

    @classmethod
    async def create(
        cls,
        dsn: str,
        encryption_key: str | None = None,
    ) -> "PostgresDatabase":
        db = cls(dsn, encryption_key)
        return await db.connect()

    async def close(self) -> None:
        if self._pool:
            await self._pool.close()

    @staticmethod
    async def _managed_schema_revision(connection: Any) -> str | None:
        try:
            relation_rows = await connection.fetch(
                """
                SELECT rel.oid::bigint AS table_oid,
                   nsp.nspname AS schema_name,
                   relation_type.oid::bigint AS type_oid,
                   type_nsp.nspname AS type_schema,
                   relation_type.typname AS type_name,
                   relation_type.typtype::text AS typtype,
                   relation_type.typrelid::bigint AS type_relation_oid,
                   rel.relkind::text AS relkind,
                   rel.relpersistence::text AS relpersistence,
                   rel.relispartition,
                   rel.relrowsecurity,
                   rel.relforcerowsecurity,
                   (
                       SELECT count(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhrelid=rel.oid
                   ) AS parent_count,
                   (
                       SELECT count(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhparent=rel.oid
                   ) AS child_count,
                   (
                       SELECT count(*)
                       FROM pg_policy AS policy
                       WHERE policy.polrelid=rel.oid
                   ) AS policy_count,
                   (
                       SELECT count(*)
                       FROM pg_trigger AS trigger_record
                       WHERE trigger_record.tgrelid=rel.oid
                         AND NOT trigger_record.tgisinternal
                   ) AS user_trigger_count,
                   (
                       SELECT count(*)
                       FROM pg_rewrite AS rewrite
                       WHERE rewrite.ev_class=rel.oid
                   ) AS user_rule_count
            FROM pg_class AS rel
            JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
            LEFT JOIN pg_type AS relation_type
              ON relation_type.oid=rel.reltype
            LEFT JOIN pg_namespace AS type_nsp
              ON type_nsp.oid=relation_type.typnamespace
                WHERE nsp.nspname=current_schema()
                  AND rel.relname='alembic_version'
                """
            )
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            raise _postgres_startup_error(
                _POSTGRES_SCHEMA_VALIDATION_ERROR,
                stage="ownership",
                invariant="ownership-relation-query",
                cause=exc,
            ) from None
        if not relation_rows:
            return None
        if len(relation_rows) != 1:
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            )
        relation = relation_rows[0]
        try:
            table_oid = int(relation["table_oid"])
            schema_name = relation["schema_name"]
            type_oid = int(relation["type_oid"])
            type_contract = (
                relation["type_schema"],
                relation["type_name"],
                str(relation["typtype"]),
                int(relation["type_relation_oid"]),
            )
            relation_contract = (
                str(relation["relkind"]),
                str(relation["relpersistence"]),
                bool(relation["relispartition"]),
                bool(relation["relrowsecurity"]),
                bool(relation["relforcerowsecurity"]),
                int(relation["parent_count"]),
                int(relation["child_count"]),
                int(relation["policy_count"]),
                int(relation["user_trigger_count"]),
                int(relation["user_rule_count"]),
            )
        except (KeyError, TypeError, ValueError):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            ) from None
        if (
            not isinstance(schema_name, str)
            or not schema_name
            or type_oid <= 0
            or type_contract != (schema_name, "alembic_version", "c", table_oid)
            or relation_contract != ("r", "p", False, False, False, 0, 0, 0, 0, 0)
        ):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            )

        column_rows = await connection.fetch(
            """
            SELECT att.attnum,
                   att.attname AS column_name,
                   pg_catalog.format_type(
                       att.atttypid,
                       att.atttypmod
                   ) AS data_type,
                   att.attnotnull,
                   att.attidentity::text AS attidentity,
                   att.attgenerated::text AS attgenerated,
                   att.attisdropped,
                   att.attinhcount,
                   att.attislocal,
                   att.atthasmissing,
                   att.attmissingval::text AS missing_value,
                   att.attcollation=type_record.typcollation
                       AS collation_is_default,
                   pg_get_expr(def.adbin, def.adrelid) AS column_default
            FROM pg_attribute AS att
            LEFT JOIN pg_type AS type_record
              ON type_record.oid=att.atttypid
            LEFT JOIN pg_attrdef AS def
              ON def.adrelid=att.attrelid
             AND def.adnum=att.attnum
            WHERE att.attrelid=$1::oid
              AND att.attnum > 0
            ORDER BY att.attnum
            """,
            table_oid,
        )
        try:
            columns = tuple(
                (
                    int(row["attnum"]),
                    str(row["column_name"]),
                    str(row["data_type"]),
                    bool(row["attnotnull"]),
                    str(row["attidentity"]),
                    str(row["attgenerated"]),
                    bool(row["attisdropped"]),
                    int(row["attinhcount"]),
                    bool(row["attislocal"]),
                    bool(row["atthasmissing"]),
                    row["missing_value"],
                    bool(row["collation_is_default"]),
                    row["column_default"],
                )
                for row in column_rows
            )
        except (KeyError, TypeError, ValueError):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            ) from None
        if columns != (
            (
                1,
                "version_num",
                "character varying(32)",
                True,
                "",
                "",
                False,
                0,
                True,
                False,
                None,
                True,
                None,
            ),
        ):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            )

        primary_rows = await connection.fetch(
            """
            SELECT con.conname,
                   con.contype::text AS contype,
                   con.convalidated,
                   con.condeferrable,
                   con.condeferred,
                   ARRAY(
                       SELECT att.attname
                       FROM unnest(con.conkey)
                            WITH ORDINALITY AS key(attnum, position)
                       JOIN pg_attribute AS att
                         ON att.attrelid=con.conrelid
                        AND att.attnum=key.attnum
                       ORDER BY key.position
                   ) AS columns
            FROM pg_constraint AS con
            WHERE con.conrelid=$1::oid
            ORDER BY con.conname
            """,
            table_oid,
        )
        try:
            primary_contract = tuple(
                (
                    str(row["conname"]),
                    str(row["contype"]),
                    bool(row["convalidated"]),
                    bool(row["condeferrable"]),
                    bool(row["condeferred"]),
                    tuple(str(column) for column in row["columns"]),
                )
                for row in primary_rows
            )
        except (KeyError, TypeError, ValueError):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            ) from None
        if primary_contract != (
            (
                "alembic_version_pkc",
                "p",
                True,
                False,
                False,
                ("version_num",),
            ),
        ):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            )

        version_index_rows = await connection.fetch(
            """
            SELECT index_rel.relname AS index_name,
                   index_rel.relkind::text AS index_relkind,
                   index_rel.relpersistence::text AS index_relpersistence,
                   index_rel.relispartition AS index_is_partition,
                   access_method.amname AS access_method,
                   ind.indisunique,
                   ind.indisprimary,
                   ind.indisvalid,
                   ind.indisready,
                   ind.indislive,
                   ind.indnkeyatts,
                   ind.indnatts,
                   con.conname AS constraint_name,
                   ARRAY(
                       SELECT att.attname
                       FROM unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY key.position
                   ) AS columns,
                   ARRAY(
                       SELECT option
                       FROM unnest(ind.indoption)
                            WITH ORDINALITY AS item(option, position)
                       ORDER BY item.position
                   ) AS column_options,
                   ARRAY(
                       SELECT opc.opcname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       ORDER BY item.position
                   ) AS operator_classes,
                   ARRAY(
                       SELECT opc_nsp.nspname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       JOIN pg_namespace AS opc_nsp
                         ON opc_nsp.oid=opc.opcnamespace
                       ORDER BY item.position
                   ) AS operator_class_namespaces,
                   ARRAY(
                       SELECT item.collation_oid=att.attcollation
                       FROM unnest(ind.indcollation)
                            WITH ORDINALITY AS item(collation_oid, position)
                       JOIN unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                         ON key.position=item.position
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY item.position
                   ) AS column_collations_match,
                   pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
                   pg_get_expr(ind.indpred, ind.indrelid) AS predicate
            FROM pg_index AS ind
            JOIN pg_class AS index_rel ON index_rel.oid=ind.indexrelid
            JOIN pg_am AS access_method ON access_method.oid=index_rel.relam
            LEFT JOIN pg_constraint AS con
              ON con.conindid=ind.indexrelid
            WHERE ind.indrelid=$1::oid
            ORDER BY index_rel.relname
            """,
            table_oid,
        )
        try:
            version_indexes = tuple(
                (
                    str(row["index_name"]),
                    str(row["index_relkind"]),
                    str(row["index_relpersistence"]),
                    bool(row["index_is_partition"]),
                    str(row["access_method"]),
                    bool(row["indisunique"]),
                    bool(row["indisprimary"]),
                    bool(row["indisvalid"]),
                    bool(row["indisready"]),
                    bool(row["indislive"]),
                    int(row["indnkeyatts"]),
                    int(row["indnatts"]),
                    row["constraint_name"],
                    tuple(str(column) for column in row["columns"]),
                    tuple(int(option) for option in row["column_options"]),
                    tuple(str(opclass) for opclass in row["operator_classes"]),
                    tuple(str(namespace) for namespace in row["operator_class_namespaces"]),
                    tuple(bool(value) for value in row["column_collations_match"]),
                    row["expressions"],
                    row["predicate"],
                )
                for row in version_index_rows
            )
        except (KeyError, TypeError, ValueError):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            ) from None
        if version_indexes != (
            (
                "alembic_version_pkc",
                "i",
                "p",
                False,
                "btree",
                True,
                True,
                True,
                True,
                True,
                1,
                1,
                "alembic_version_pkc",
                ("version_num",),
                (0,),
                ("text_ops",),
                ("pg_catalog",),
                (True,),
                None,
                None,
            ),
        ):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-relation-metadata",
            )

        quoted_schema = '"' + schema_name.replace('"', '""') + '"'
        try:
            revision_rows = await connection.fetch(
                f'SELECT version_num FROM {quoted_schema}."alembic_version"'  # noqa: S608
            )
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            raise _postgres_startup_error(
                _POSTGRES_SCHEMA_VALIDATION_ERROR,
                stage="ownership",
                invariant="ownership-revision-query",
                cause=exc,
            ) from None
        try:
            values = tuple(row["version_num"] for row in revision_rows)
        except (KeyError, TypeError):
            raise _PostgresManagedSchemaError(
                _POSTGRES_MANAGED_SCHEMA_ERROR,
                diagnostic_stage="ownership",
                diagnostic_invariant="ownership-revision-cardinality",
            ) from None
        return _classify_postgres_revision(values)

    @staticmethod
    async def _validate_managed_schema(connection: Any) -> None:
        table_rows = await connection.fetch(
            """
            SELECT rel.oid::bigint AS table_oid,
                   rel.relname AS table_name,
                   nsp.nspname AS table_schema,
                   relation_type.oid::bigint AS type_oid,
                   type_nsp.nspname AS type_schema,
                   relation_type.typname AS type_name,
                   relation_type.typtype::text AS typtype,
                   relation_type.typrelid::bigint AS type_relation_oid,
                   rel.relkind::text AS relkind,
                   rel.relpersistence::text AS relpersistence,
                   rel.relispartition,
                   rel.relrowsecurity,
                   rel.relforcerowsecurity,
                   (
                       SELECT count(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhrelid=rel.oid
                   ) AS parent_count,
                   (
                       SELECT count(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhparent=rel.oid
                   ) AS child_count,
                   (
                       SELECT count(*)
                       FROM pg_policy AS policy
                       WHERE policy.polrelid=rel.oid
                   ) AS policy_count,
                   (
                       SELECT count(*)
                       FROM pg_trigger AS trigger_record
                       WHERE trigger_record.tgrelid=rel.oid
                         AND NOT trigger_record.tgisinternal
                   ) AS user_trigger_count,
                   (
                       SELECT count(*)
                       FROM pg_rewrite AS rewrite
                       WHERE rewrite.ev_class=rel.oid
                   ) AS user_rule_count
            FROM pg_class AS rel
            JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
            JOIN pg_type AS relation_type ON relation_type.oid=rel.reltype
            JOIN pg_namespace AS type_nsp
              ON type_nsp.oid=relation_type.typnamespace
            WHERE nsp.nspname=current_schema()
              AND rel.relname=ANY($1::text[])
            ORDER BY rel.relname
            """,
            sorted(_POSTGRES_MANAGED_TABLES),
        )
        try:
            table_contract = {
                str(row["table_name"]): (
                    int(row["table_oid"]),
                    str(row["relkind"]),
                    str(row["relpersistence"]),
                    bool(row["relispartition"]),
                    bool(row["relrowsecurity"]),
                    bool(row["relforcerowsecurity"]),
                    int(row["parent_count"]),
                    int(row["child_count"]),
                    int(row["policy_count"]),
                    int(row["user_trigger_count"]),
                    int(row["user_rule_count"]),
                    str(row["table_schema"]),
                    int(row["type_oid"]),
                    str(row["type_schema"]),
                    str(row["type_name"]),
                    str(row["typtype"]),
                    int(row["type_relation_oid"]),
                )
                for row in table_rows
            }
        except (KeyError, TypeError, ValueError):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
        if set(table_contract) != _POSTGRES_MANAGED_TABLES:
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
        for table, metadata in table_contract.items():
            expected = (
                ("r", "p", False)
                if table == "websocket_tickets"
                else (
                    "r",
                    "p",
                    False,
                    False,
                    False,
                    0,
                    0,
                    0,
                    0,
                    0,
                )
            )
            actual = metadata[1:4] if table == "websocket_tickets" else metadata[1:11]
            (
                table_schema,
                type_oid,
                type_schema,
                type_name,
                type_kind,
                type_relation_oid,
            ) = metadata[11:]
            if (
                actual != expected
                or not table_schema
                or type_oid <= 0
                or type_schema != table_schema
                or type_name != table
                or type_kind != "c"
                or type_relation_oid != metadata[0]
            ):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

        non_ticket_tables = sorted(_POSTGRES_MANAGED_TABLES - {"websocket_tickets"})
        legacy_index_rows = await connection.fetch(
            """
            SELECT table_rel.relname AS table_name,
                   index_rel.relname AS index_name
            FROM pg_index AS ind
            JOIN pg_class AS table_rel ON table_rel.oid=ind.indrelid
            JOIN pg_namespace AS table_nsp
              ON table_nsp.oid=table_rel.relnamespace
            JOIN pg_class AS index_rel ON index_rel.oid=ind.indexrelid
            WHERE table_nsp.nspname=current_schema()
              AND table_rel.relname=ANY($1::text[])
              AND left(index_rel.relname, 7)='idx_pg_'
            ORDER BY table_rel.relname, index_rel.relname
            """,
            sorted(_POSTGRES_MANAGED_TABLES),
        )
        if legacy_index_rows:
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

        index_rows = await connection.fetch(
            """
            SELECT table_rel.relname AS table_name,
                   index_rel.relname AS index_name,
                   index_rel.relkind::text AS index_relkind,
                   index_rel.relpersistence::text AS index_relpersistence,
                   index_rel.relispartition AS index_is_partition,
                   access_method.amname AS access_method,
                   ind.indisunique,
                   ind.indisprimary,
                   ind.indisvalid,
                   ind.indisready,
                   ind.indislive,
                   ind.indnkeyatts,
                   ind.indnatts,
                   ARRAY(
                       SELECT attribute.attname
                       FROM unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                       LEFT JOIN pg_attribute AS attribute
                         ON attribute.attrelid=ind.indrelid
                        AND attribute.attnum=key.attnum
                       ORDER BY key.position
                   ) AS index_columns,
                   ARRAY(
                       SELECT CASE
                           WHEN key.attnum=0 THEN NULL
                           ELSE att.attname
                       END
                       FROM unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY key.position
                   ) AS columns,
                   ARRAY(
                       SELECT option
                       FROM unnest(ind.indoption)
                            WITH ORDINALITY AS item(option, position)
                       ORDER BY item.position
                   ) AS column_options,
                   ARRAY(
                       SELECT opc.opcname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       ORDER BY item.position
                   ) AS operator_classes,
                   ARRAY(
                       SELECT opc_nsp.nspname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       JOIN pg_namespace AS opc_nsp
                         ON opc_nsp.oid=opc.opcnamespace
                       ORDER BY item.position
                   ) AS operator_class_namespaces,
                   ARRAY(
                       SELECT item.collation_oid=att.attcollation
                       FROM unnest(ind.indcollation)
                            WITH ORDINALITY AS item(collation_oid, position)
                       JOIN unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                         ON key.position=item.position
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY item.position
                   ) AS column_collations_match,
                   pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
                   pg_get_expr(ind.indpred, ind.indrelid) AS predicate
            FROM pg_index AS ind
            JOIN pg_class AS table_rel ON table_rel.oid=ind.indrelid
            JOIN pg_namespace AS table_nsp
              ON table_nsp.oid=table_rel.relnamespace
            JOIN pg_class AS index_rel ON index_rel.oid=ind.indexrelid
            JOIN pg_am AS access_method
              ON access_method.oid=index_rel.relam
            WHERE table_nsp.nspname=current_schema()
              AND table_rel.relname=ANY($1::text[])
              AND NOT EXISTS (
                  SELECT 1
                  FROM pg_constraint AS con
                  WHERE con.conindid=ind.indexrelid
              )
            ORDER BY table_rel.relname, index_rel.relname
            """,
            non_ticket_tables,
        )
        try:
            index_contract = {
                (str(row["table_name"]), str(row["index_name"])): (
                    tuple(str(column) for column in row["columns"]),
                    bool(row["indisunique"]),
                    row["predicate"],
                    str(row["index_relkind"]),
                    str(row["index_relpersistence"]),
                    bool(row["index_is_partition"]),
                    str(row["access_method"]),
                    bool(row["indisprimary"]),
                    bool(row["indisvalid"]),
                    bool(row["indisready"]),
                    bool(row["indislive"]),
                    int(row["indnkeyatts"]),
                    int(row["indnatts"]),
                    tuple(int(option) for option in row["column_options"]),
                    tuple(str(opclass) for opclass in row["operator_classes"]),
                    tuple(str(namespace) for namespace in row["operator_class_namespaces"]),
                    tuple(bool(value) for value in row["column_collations_match"]),
                    row["expressions"],
                )
                for row in index_rows
            }
        except (KeyError, TypeError, ValueError):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
        expected_indexes = {
            (table, name): columns for table, name, columns in _POSTGRES_MANAGED_INDEXES
        }
        if set(index_contract) != set(expected_indexes):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
        for key, columns in expected_indexes.items():
            actual = index_contract[key]
            is_one_active = key == (
                "refresh_tokens",
                "uq_refresh_token_one_active",
            )
            expected_predicate = "(state = 'active'::text)" if is_one_active else None
            if actual != (
                columns,
                is_one_active,
                expected_predicate,
                "i",
                "p",
                False,
                "btree",
                False,
                True,
                True,
                True,
                len(columns),
                len(columns),
                (0,) * len(columns),
                tuple(_postgres_index_opclass(key[0], column) for column in columns),
                ("pg_catalog",) * len(columns),
                (True,) * len(columns),
                None,
            ):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

        primary_rows = await connection.fetch(
            """
            SELECT source.relname AS table_name,
                   con.conname,
                   con.contype::text AS contype,
                   con.convalidated,
                   con.condeferrable,
                   con.condeferred,
                   con.conindid::bigint AS constraint_index_oid,
                   ARRAY(
                       SELECT att.attname
                       FROM unnest(con.conkey)
                            WITH ORDINALITY AS key(attnum, position)
                       JOIN pg_attribute AS att
                         ON att.attrelid=con.conrelid
                        AND att.attnum=key.attnum
                       ORDER BY key.position
                   ) AS local_columns,
                   index_rel.relkind::text AS index_relkind,
                   index_rel.relpersistence::text AS index_relpersistence,
                   index_rel.relispartition AS index_is_partition,
                   ind.indisunique,
                   ind.indisprimary,
                   ind.indisvalid,
                   ind.indisready,
                   ind.indislive
            FROM pg_constraint AS con
            JOIN pg_class AS source ON source.oid=con.conrelid
            JOIN pg_namespace AS source_nsp
              ON source_nsp.oid=source.relnamespace
            JOIN pg_class AS index_rel ON index_rel.oid=con.conindid
            JOIN pg_index AS ind ON ind.indexrelid=con.conindid
            WHERE source_nsp.nspname=current_schema()
              AND source.relname=ANY($1::text[])
              AND con.contype='p'
            ORDER BY source.relname, con.conname
            """,
            non_ticket_tables,
        )
        try:
            primary_contract = {
                (str(row["table_name"]), str(row["conname"])): (
                    str(row["contype"]),
                    bool(row["convalidated"]),
                    bool(row["condeferrable"]),
                    bool(row["condeferred"]),
                    tuple(str(value) for value in row["local_columns"]),
                    int(row["constraint_index_oid"]),
                    str(row["index_relkind"]),
                    str(row["index_relpersistence"]),
                    bool(row["index_is_partition"]),
                    bool(row["indisunique"]),
                    bool(row["indisprimary"]),
                    bool(row["indisvalid"]),
                    bool(row["indisready"]),
                    bool(row["indislive"]),
                )
                for row in primary_rows
            }
        except (KeyError, TypeError, ValueError):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
        expected_primary_keys = {
            (table, name): columns for table, name, columns in _POSTGRES_MANAGED_PRIMARY_KEYS
        }
        if set(primary_contract) != set(expected_primary_keys):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
        for key, columns in expected_primary_keys.items():
            actual = primary_contract[key]
            if (
                actual
                != (
                    "p",
                    True,
                    False,
                    False,
                    columns,
                    actual[5],
                    "i",
                    "p",
                    False,
                    True,
                    True,
                    True,
                    True,
                    True,
                )
                or actual[5] <= 0
            ):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

        await PostgresDatabase._validate_managed_constraints(
            connection,
            non_ticket_tables,
        )
        await PostgresDatabase._validate_managed_serials(connection)

    @staticmethod
    async def _validate_managed_constraints(
        connection: Any,
        non_ticket_tables: list[str],
    ) -> None:
        rows = await connection.fetch(
            """
            SELECT source_nsp.nspname AS source_schema,
                   source.relname AS table_name,
                   con.conname,
                   con.contype::text AS contype,
                   con.convalidated,
                   con.condeferrable,
                   con.condeferred,
                   con.conindid::bigint AS constraint_index_oid,
                   pg_get_constraintdef(con.oid, true) AS definition,
                   reference_nsp.nspname AS referenced_schema,
                   reference.relname AS referenced_table,
                   con.confupdtype::text AS confupdtype,
                   con.confdeltype::text AS confdeltype,
                   ARRAY(
                       SELECT attribute.attname
                       FROM unnest(con.conkey)
                            WITH ORDINALITY AS key(attnum, position)
                       JOIN pg_attribute AS attribute
                         ON attribute.attrelid=con.conrelid
                        AND attribute.attnum=key.attnum
                       ORDER BY key.position
                   ) AS local_columns,
                   ARRAY(
                       SELECT attribute.attname
                       FROM unnest(con.confkey)
                            WITH ORDINALITY AS key(attnum, position)
                       JOIN pg_attribute AS attribute
                         ON attribute.attrelid=con.confrelid
                        AND attribute.attnum=key.attnum
                       ORDER BY key.position
                   ) AS remote_columns,
                   index_nsp.nspname AS index_schema,
                   index_rel.relname AS index_name,
                   index_rel.relkind::text AS index_relkind,
                   index_rel.relpersistence::text AS index_relpersistence,
                   index_rel.relispartition AS index_is_partition,
                   access_method.amname AS access_method,
                   ind.indisunique,
                   ind.indisprimary,
                   ind.indisvalid,
                   ind.indisready,
                   ind.indislive,
                   ind.indnkeyatts,
                   ind.indnatts,
                   ARRAY(
                       SELECT attribute.attname
                       FROM unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                       LEFT JOIN pg_attribute AS attribute
                         ON attribute.attrelid=ind.indrelid
                        AND attribute.attnum=key.attnum
                       ORDER BY key.position
                   ) AS index_columns,
                   ARRAY(
                       SELECT option
                       FROM unnest(ind.indoption)
                            WITH ORDINALITY AS item(option, position)
                       ORDER BY item.position
                   ) AS column_options,
                   ARRAY(
                       SELECT opc.opcname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       ORDER BY item.position
                   ) AS operator_classes,
                   ARRAY(
                       SELECT opc_nsp.nspname
                       FROM unnest(ind.indclass)
                            WITH ORDINALITY AS item(opclass, position)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       JOIN pg_namespace AS opc_nsp
                         ON opc_nsp.oid=opc.opcnamespace
                       ORDER BY item.position
                   ) AS operator_class_namespaces,
                   ARRAY(
                       SELECT item.collation_oid=attribute.attcollation
                       FROM unnest(ind.indcollation)
                            WITH ORDINALITY AS item(collation_oid, position)
                       JOIN unnest(ind.indkey)
                            WITH ORDINALITY AS key(attnum, position)
                         ON key.position=item.position
                       LEFT JOIN pg_attribute AS attribute
                         ON attribute.attrelid=ind.indrelid
                        AND attribute.attnum=key.attnum
                       ORDER BY item.position
                   ) AS column_collations_match,
                   pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
                   pg_get_expr(ind.indpred, ind.indrelid) AS predicate
            FROM pg_constraint AS con
            JOIN pg_class AS source ON source.oid=con.conrelid
            JOIN pg_namespace AS source_nsp
              ON source_nsp.oid=source.relnamespace
            LEFT JOIN pg_class AS reference
              ON reference.oid=con.confrelid
            LEFT JOIN pg_namespace AS reference_nsp
              ON reference_nsp.oid=reference.relnamespace
            LEFT JOIN pg_class AS index_rel
              ON index_rel.oid=con.conindid
            LEFT JOIN pg_namespace AS index_nsp
              ON index_nsp.oid=index_rel.relnamespace
            LEFT JOIN pg_index AS ind
              ON ind.indexrelid=con.conindid
            LEFT JOIN pg_am AS access_method
              ON access_method.oid=index_rel.relam
            WHERE source_nsp.nspname=current_schema()
              AND source.relname=ANY($1::text[])
              AND con.contype <> 'p'
            ORDER BY source.relname, con.conname
            """,
            non_ticket_tables,
        )
        try:
            delegated_v11_checks = tuple(
                row
                for row in rows
                if str(row["table_name"]) == "credentials"
                and str(row["contype"]) == "c"
                and tuple(str(column) for column in row["local_columns"])
                in {
                    (column,)
                    for column in _POSTGRES_V11_CREDENTIAL_AUTHORITY_COLUMNS
                }
            )
            if (
                len(delegated_v11_checks)
                != len(_POSTGRES_V11_CREDENTIAL_AUTHORITY_COLUMNS)
                or {
                    str(row["local_columns"][0])
                    for row in delegated_v11_checks
                }
                != _POSTGRES_V11_CREDENTIAL_AUTHORITY_COLUMNS
            ):
                raise ValueError
            delegated_v11_keys = {
                (str(row["table_name"]), str(row["conname"]))
                for row in delegated_v11_checks
            }
            actual = {
                (str(row["table_name"]), str(row["conname"])): row
                for row in rows
                if (str(row["table_name"]), str(row["conname"]))
                not in delegated_v11_keys
            }
        except (KeyError, TypeError, ValueError):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None

        expected_foreign = {
            (table, name): (
                local,
                parent,
                remote,
                on_delete,
                delete_code,
            )
            for (
                table,
                name,
                local,
                parent,
                remote,
                on_delete,
                delete_code,
            ) in _POSTGRES_MANAGED_FOREIGN_KEYS
        }
        expected_unique = {
            (table, name): columns for table, name, columns in _POSTGRES_MANAGED_UNIQUES
        }
        expected_checks = {
            (
                table,
                _postgres_finite_check_name(table, column),
            ): (
                f"CHECK ({column} IS NULL OR isfinite({column}))"
                if nullable
                else f"CHECK (isfinite({column}))"
            )
            for table, column, nullable in (_POSTGRES_MANAGED_FINITE_CHECKS)
        }
        expected_checks[
            (
                "rate_limit_events",
                "ck_rate_limit_events_blocked_bool",
            )
        ] = "CHECK (blocked = ANY (ARRAY[0, 1]))"
        expected_checks.update(
            {
                ("users", "ck_users_auth_epoch"): ("CHECK (auth_epoch >= 1)"),
                ("refresh_token_families", "ck_refresh_family_id"): (
                    "CHECK (id ~ '^[A-Za-z0-9_-]{43}$'::text)"
                ),
                ("refresh_token_families", "ck_refresh_family_epoch"): ("CHECK (auth_epoch >= 1)"),
                ("refresh_token_families", "ck_refresh_family_state"): (
                    "CHECK (state = ANY (ARRAY['active'::text, 'revoked'::text]))"
                ),
                (
                    "refresh_token_families",
                    "ck_refresh_family_revocation_shape",
                ): (
                    "CHECK (state = 'active'::text AND revoked_at IS NULL "
                    "AND revoke_reason IS NULL OR state = 'revoked'::text "
                    "AND revoked_at IS NOT NULL AND revoke_reason IS NOT NULL)"
                ),
                ("refresh_token_families", "ck_refresh_family_reason"): (
                    "CHECK (revoke_reason IS NULL OR (revoke_reason = ANY "
                    "(ARRAY['expired'::text, 'logout_all'::text, "
                    "'logout_current'::text, 'operator_revoke'::text, "
                    "'password_change'::text, 'password_reset'::text, "
                    "'replay'::text, 'role_change'::text, "
                    "'rollout_reset'::text, 'user_status_change'::text])))"
                ),
                (
                    "refresh_token_families",
                    "ck_refresh_family_expiry_order",
                ): "CHECK (absolute_expires_at > created_at)",
                (
                    "refresh_token_families",
                    "ck_refresh_family_retention_order",
                ): (
                    "CHECK (retain_until > absolute_expires_at AND "
                    "(revoked_at IS NULL OR retain_until > revoked_at))"
                ),
                ("refresh_tokens", "ck_refresh_token_hash"): (
                    "CHECK (id ~ '^[0-9a-f]{64}$'::text)"
                ),
                ("refresh_tokens", "ck_refresh_token_generation"): ("CHECK (generation >= 0)"),
                ("refresh_tokens", "ck_refresh_token_parent_shape"): (
                    "CHECK (generation = 0 AND parent_id IS NULL "
                    "OR generation > 0 AND parent_id IS NOT NULL)"
                ),
                ("refresh_tokens", "ck_refresh_token_state"): (
                    "CHECK (state = ANY (ARRAY['active'::text, 'consumed'::text, 'retired'::text]))"
                ),
                ("refresh_tokens", "ck_refresh_token_state_shape"): (
                    "CHECK (state = 'active'::text AND is_revoked = 0 "
                    "AND used_at IS NULL AND revoked_at IS NULL "
                    "OR state = 'consumed'::text AND is_revoked = 1 "
                    "AND used_at IS NOT NULL AND revoked_at IS NULL "
                    "OR state = 'retired'::text AND is_revoked = 1 "
                    "AND revoked_at IS NOT NULL)"
                ),
                ("refresh_tokens", "ck_refresh_token_expiry_order"): (
                    "CHECK (expires_at > created_at)"
                ),
            }
        )
        expected_keys = set(expected_foreign) | set(expected_unique) | set(expected_checks)
        if (
            len(rows) - len(delegated_v11_checks) != len(actual)
            or set(actual) != expected_keys
        ):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

        for key, contract in actual.items():
            try:
                source_schema = str(contract["source_schema"])
                common = (
                    bool(contract["convalidated"]),
                    bool(contract["condeferrable"]),
                    bool(contract["condeferred"]),
                )
                definition = _postgres_constraint_definition(contract["definition"])
                local_columns = tuple(str(column) for column in contract["local_columns"])
                remote_columns = tuple(str(column) for column in contract["remote_columns"])
            except (KeyError, TypeError, ValueError):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
            expected_common = (
                (True, True, True)
                if key == ("refresh_tokens", "fk_refresh_token_parent")
                else (True, False, False)
            )
            if not source_schema or common != expected_common:
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

            if key in expected_checks:
                if (
                    str(contract["contype"]) != "c"
                    or definition != expected_checks[key]
                    or local_columns == ()
                    or remote_columns
                    or int(contract["constraint_index_oid"]) != 0
                ):
                    ordinal = sorted(expected_checks).index(key)
                    raise _PostgresManagedSchemaError(
                        _POSTGRES_MANAGED_SCHEMA_ERROR,
                        diagnostic_stage="managed-validation",
                        diagnostic_invariant=f"managed-check-{ordinal:02d}",
                    ) from None
                continue

            if key in expected_foreign:
                (
                    expected_local,
                    parent,
                    expected_remote,
                    on_delete,
                    delete_code,
                ) = expected_foreign[key]
                expected_definition = (
                    f"FOREIGN KEY ({', '.join(expected_local)}) "
                    f"REFERENCES {parent}({', '.join(expected_remote)}) "
                    f"ON DELETE {on_delete}"
                )
                if key == ("refresh_tokens", "fk_refresh_token_parent"):
                    expected_definition += " DEFERRABLE INITIALLY DEFERRED"
                if (
                    str(contract["contype"]) != "f"
                    or definition != expected_definition
                    or local_columns != expected_local
                    or remote_columns != expected_remote
                    or str(contract["referenced_schema"]) != source_schema
                    or str(contract["referenced_table"]) != parent
                    or str(contract["confupdtype"]) != "a"
                    or str(contract["confdeltype"]) != delete_code
                ):
                    raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
                try:
                    foreign_index_contract = (
                        int(contract["constraint_index_oid"]),
                        str(contract["index_schema"]),
                        str(contract["index_name"]),
                        str(contract["index_relkind"]),
                        str(contract["index_relpersistence"]),
                        bool(contract["index_is_partition"]),
                        str(contract["access_method"]),
                        bool(contract["indisunique"]),
                        bool(contract["indisprimary"]),
                        bool(contract["indisvalid"]),
                        bool(contract["indisready"]),
                        bool(contract["indislive"]),
                        int(contract["indnkeyatts"]),
                        int(contract["indnatts"]),
                        tuple(str(column) for column in contract["index_columns"]),
                        tuple(int(option) for option in contract["column_options"]),
                        tuple(str(opclass) for opclass in contract["operator_classes"]),
                        tuple(
                            str(namespace) for namespace in contract["operator_class_namespaces"]
                        ),
                        tuple(bool(value) for value in contract["column_collations_match"]),
                        contract["expressions"],
                        contract["predicate"],
                    )
                except (KeyError, TypeError, ValueError):
                    raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
                reference_index_name = {
                    (
                        "refresh_tokens",
                        "fk_refresh_token_family_owner",
                    ): "uq_refresh_family_owner",
                    (
                        "refresh_tokens",
                        "fk_refresh_token_parent",
                    ): "uq_refresh_token_family_hash",
                }.get(key, f"{parent}_pkey")
                reference_is_primary = reference_index_name.endswith("_pkey")
                if (
                    foreign_index_contract
                    != (
                        foreign_index_contract[0],
                        source_schema,
                        reference_index_name,
                        "i",
                        "p",
                        False,
                        "btree",
                        True,
                        reference_is_primary,
                        True,
                        True,
                        True,
                        len(expected_remote),
                        len(expected_remote),
                        expected_remote,
                        (0,) * len(expected_remote),
                        ("text_ops",) * len(expected_remote),
                        ("pg_catalog",) * len(expected_remote),
                        (True,) * len(expected_remote),
                        None,
                        None,
                    )
                    or foreign_index_contract[0] <= 0
                ):
                    ordinal = sorted(expected_foreign).index(key)
                    raise _PostgresManagedSchemaError(
                        _POSTGRES_MANAGED_SCHEMA_ERROR,
                        diagnostic_stage="managed-validation",
                        diagnostic_invariant=f"managed-foreign-{ordinal:02d}",
                    ) from None
                continue

            columns = expected_unique[key]
            expected_definition = f"UNIQUE ({', '.join(columns)})"
            try:
                unique_contract = (
                    str(contract["contype"]),
                    definition,
                    local_columns,
                    contract["referenced_schema"],
                    contract["referenced_table"],
                    remote_columns,
                    str(contract["index_schema"]),
                    str(contract["index_name"]),
                    str(contract["index_relkind"]),
                    str(contract["index_relpersistence"]),
                    bool(contract["index_is_partition"]),
                    str(contract["access_method"]),
                    bool(contract["indisunique"]),
                    bool(contract["indisprimary"]),
                    bool(contract["indisvalid"]),
                    bool(contract["indisready"]),
                    bool(contract["indislive"]),
                    int(contract["indnkeyatts"]),
                    int(contract["indnatts"]),
                    tuple(str(column) for column in contract["index_columns"]),
                    tuple(int(option) for option in contract["column_options"]),
                    tuple(str(opclass) for opclass in contract["operator_classes"]),
                    tuple(str(namespace) for namespace in (contract["operator_class_namespaces"])),
                    tuple(bool(value) for value in contract["column_collations_match"]),
                    contract["expressions"],
                    contract["predicate"],
                )
            except (KeyError, TypeError, ValueError):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
            if (
                unique_contract
                != (
                    "u",
                    expected_definition,
                    columns,
                    None,
                    None,
                    (),
                    source_schema,
                    key[1],
                    "i",
                    "p",
                    False,
                    "btree",
                    True,
                    False,
                    True,
                    True,
                    True,
                    len(columns),
                    len(columns),
                    columns,
                    (0,) * len(columns),
                    tuple(_postgres_index_opclass(key[0], column) for column in columns),
                    ("pg_catalog",) * len(columns),
                    (True,) * len(columns),
                    None,
                    None,
                )
                or int(contract["constraint_index_oid"]) <= 0
            ):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)

    @staticmethod
    async def _validate_managed_serials(connection: Any) -> None:
        rows = await connection.fetch(
            """
            SELECT sequence_nsp.nspname AS sequence_schema,
                   sequence_rel.relname AS sequence_name,
                   sequence_rel.relkind::text AS relkind,
                   sequence_rel.relpersistence::text AS relpersistence,
                   format_type(sequence_def.seqtypid, NULL) AS data_type,
                   sequence_def.seqstart,
                   sequence_def.seqincrement,
                   sequence_def.seqmax,
                   sequence_def.seqmin,
                   sequence_def.seqcache,
                   sequence_def.seqcycle,
                   owner_nsp.nspname AS owner_schema,
                   owner_rel.relname AS owner_table,
                   owner_att.attname AS owner_column,
                   dependency.deptype::text AS deptype,
                   format_type(
                       owner_att.atttypid,
                       owner_att.atttypmod
                   ) AS owner_data_type,
                   owner_att.attnotnull,
                   owner_att.attidentity::text AS attidentity,
                   owner_att.attgenerated::text AS attgenerated,
                   owner_att.attcollation=owner_type.typcollation
                       AS collation_is_default,
                   pg_get_expr(
                       owner_default.adbin,
                       owner_default.adrelid
                   ) AS column_default
            FROM pg_class AS sequence_rel
            JOIN pg_namespace AS sequence_nsp
              ON sequence_nsp.oid=sequence_rel.relnamespace
            JOIN pg_sequence AS sequence_def
              ON sequence_def.seqrelid=sequence_rel.oid
            LEFT JOIN pg_depend AS dependency
              ON dependency.classid='pg_class'::regclass
             AND dependency.objid=sequence_rel.oid
             AND dependency.objsubid=0
             AND dependency.refclassid='pg_class'::regclass
             AND dependency.deptype IN ('a', 'i')
            LEFT JOIN pg_class AS owner_rel
              ON owner_rel.oid=dependency.refobjid
            LEFT JOIN pg_namespace AS owner_nsp
              ON owner_nsp.oid=owner_rel.relnamespace
            LEFT JOIN pg_attribute AS owner_att
              ON owner_att.attrelid=dependency.refobjid
             AND owner_att.attnum=dependency.refobjsubid
            LEFT JOIN pg_type AS owner_type
              ON owner_type.oid=owner_att.atttypid
            LEFT JOIN pg_attrdef AS owner_default
              ON owner_default.adrelid=owner_att.attrelid
             AND owner_default.adnum=owner_att.attnum
            WHERE sequence_nsp.nspname=current_schema()
              AND sequence_rel.relname=ANY($1::text[])
            ORDER BY sequence_rel.relname
            """,
            ["audit_log_id_seq", "rate_limit_events_id_seq"],
        )
        expected = {
            "audit_log_id_seq": (
                "audit_log",
                "id",
                "nextval('audit_log_id_seq'::regclass)",
            ),
            "rate_limit_events_id_seq": (
                "rate_limit_events",
                "id",
                "nextval('rate_limit_events_id_seq'::regclass)",
            ),
        }
        if len(rows) != len(expected):
            raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
        seen: set[str] = set()
        for row in rows:
            try:
                name = str(row["sequence_name"])
                owner = expected.get(name)
                contract = (
                    str(row["relkind"]),
                    str(row["relpersistence"]),
                    str(row["data_type"]),
                    int(row["seqstart"]),
                    int(row["seqincrement"]),
                    int(row["seqmax"]),
                    int(row["seqmin"]),
                    int(row["seqcache"]),
                    bool(row["seqcycle"]),
                    str(row["owner_schema"]),
                    str(row["owner_table"]),
                    str(row["owner_column"]),
                    str(row["deptype"]),
                    str(row["owner_data_type"]),
                    bool(row["attnotnull"]),
                    str(row["attidentity"]),
                    str(row["attgenerated"]),
                    bool(row["collation_is_default"]),
                    row["column_default"],
                )
            except (KeyError, TypeError, ValueError):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR) from None
            if owner is None or name in seen:
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
            if contract != (
                "S",
                "p",
                "integer",
                1,
                1,
                2147483647,
                1,
                1,
                False,
                str(row["sequence_schema"]),
                owner[0],
                owner[1],
                "a",
                "integer",
                True,
                "",
                "",
                True,
                owner[2],
            ):
                raise RuntimeError(_POSTGRES_MANAGED_SCHEMA_ERROR)
            seen.add(name)

    async def _init_schema(self) -> None:
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                try:
                    revision = await self._managed_schema_revision(conn)
                except (
                    _PostgresManagedSchemaError,
                    _PostgresMigrationRequiredError,
                    _PostgresStartupDiagnosticError,
                ):
                    raise
                except asyncio.CancelledError:
                    raise
                except Exception as exc:
                    raise _postgres_startup_error(
                        _POSTGRES_SCHEMA_VALIDATION_ERROR,
                        stage="ownership",
                        invariant="ownership-relation-metadata",
                        cause=exc,
                    ) from None
                if revision is not None:
                    try:
                        await self._validate_managed_schema(conn)
                        await self._validate_websocket_ticket_schema(conn)
                        await validate_postgresql_admission_authority_catalog(conn)
                    except _PostgresMigrationRequiredError:
                        raise
                    except _PostgresStartupDiagnosticError as exc:
                        raise _postgres_restage_startup_error(
                            exc,
                            "managed-validation",
                        ) from None
                    except asyncio.CancelledError:
                        raise
                    except RuntimeError as exc:
                        if str(exc) == _POSTGRES_MANAGED_SCHEMA_ERROR:
                            raise _PostgresManagedSchemaError(
                                _POSTGRES_MANAGED_SCHEMA_ERROR,
                                diagnostic_stage="managed-validation",
                                diagnostic_invariant=("managed-validation-unclassified"),
                            ) from None
                        raise _postgres_startup_error(
                            _POSTGRES_SCHEMA_VALIDATION_ERROR,
                            stage="managed-validation",
                            invariant="managed-validation-unclassified",
                            cause=exc,
                        ) from None
                    except Exception as exc:
                        raise _postgres_startup_error(
                            _POSTGRES_SCHEMA_VALIDATION_ERROR,
                            stage="managed-validation",
                            invariant="managed-validation-unclassified",
                            cause=exc,
                        ) from None
                else:
                    raise _PostgresMigrationRequiredError(
                        _POSTGRES_MIGRATION_REQUIRED_ERROR,
                        diagnostic_stage="ownership",
                        diagnostic_invariant="ownership-unversioned",
                    )
        logger.info("pg_schema_ready")

    @staticmethod
    async def _validate_websocket_ticket_schema(connection: Any) -> None:
        """Reject any ticket catalog that differs from the owned schema."""
        census = _new_postgres_ticket_schema_census()
        rejected = False
        q78_cause: Exception | None = None
        q78_failure = ""
        q78_split = ""
        try:
            relation = await connection.fetchrow(
                """
                SELECT rel.oid::bigint AS table_oid,
                   nsp.oid::bigint AS schema_oid,
                   nsp.nspname AS schema_name,
                   rel.relkind::text AS relkind,
                   rel.relpersistence::text AS relpersistence,
                   rel.relispartition,
                   rel.relrowsecurity,
                   rel.relforcerowsecurity,
                   (
                       SELECT COUNT(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhrelid=rel.oid
                   ) AS parent_count,
                   (
                       SELECT COUNT(*)
                       FROM pg_inherits AS inherited
                       WHERE inherited.inhparent=rel.oid
                   ) AS child_count,
                   (
                       SELECT COUNT(*)
                       FROM pg_policy AS policy
                       WHERE policy.polrelid=rel.oid
                   ) AS policy_count,
                   (
                       SELECT COUNT(*)
                       FROM pg_trigger AS trg
                       WHERE trg.tgrelid=rel.oid
                         AND NOT trg.tgisinternal
                   ) AS user_trigger_count,
                   (
                       SELECT COUNT(*)
                       FROM pg_rewrite AS rewrite
                       WHERE rewrite.ev_class=rel.oid
                   ) AS user_rule_count
            FROM pg_class AS rel
            JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
                WHERE nsp.nspname=current_schema()
                  AND rel.relname='websocket_tickets'
                """
            )
        except asyncio.CancelledError:
            raise
        except Exception:
            relation = None
            census["z"]["q"] |= 0x01
            rejected = True
        if relation is None:
            census["r"]["n"] = 0x3FF
            rejected = True
            table_oid = 0
            schema_oid = 0
            schema_name = ""
        else:
            table_oid = int(relation["table_oid"])
            schema_oid = int(relation["schema_oid"])
            schema_name = str(relation["schema_name"])
            if not _postgres_ticket_relation_is_canonical(relation):
                relation_masks = _postgres_ticket_relation_diagnostic_masks(relation)
                for field, value in zip(("m", "a", "x", "n"), relation_masks, strict=True):
                    census["r"][field] = value
                if relation_masks == (0, 0, 0, 0):
                    census["r"]["g"] = 1
                rejected = True

        try:
            rows = await connection.fetch(
                """
            SELECT att.attname AS column_name,
                   pg_catalog.format_type(att.atttypid, att.atttypmod) AS data_type,
                   att.attnotnull,
                   att.attidentity::text AS attidentity,
                   att.attgenerated::text AS attgenerated,
                   att.attcollation=typ.typcollation AS collation_is_default,
                   pg_get_expr(def.adbin, def.adrelid) AS column_default
            FROM pg_attribute AS att
            JOIN pg_type AS typ ON typ.oid=att.atttypid
            LEFT JOIN pg_attrdef AS def
              ON def.adrelid=att.attrelid AND def.adnum=att.attnum
            WHERE att.attrelid=$1::oid
              AND att.attnum > 0
              AND NOT att.attisdropped
            ORDER BY att.attnum
                """,
                table_oid,
            )
        except asyncio.CancelledError:
            raise
        except Exception:
            rows = []
            census["z"]["q"] |= 0x02
            rejected = True
        actual_columns = [
            (
                str(row["column_name"]),
                str(row["data_type"]),
                bool(row["attnotnull"]),
                str(row["attidentity"]),
                str(row["attgenerated"]),
                bool(row["collation_is_default"]),
                None if row["column_default"] is None else str(row["column_default"]),
            )
            for row in rows
        ]
        expected_columns = [
            ("ticket_hash", "text", True, "", "", True, None),
            ("campaign_id", "text", True, "", "", True, None),
            ("user_id", "text", True, "", "", True, None),
            ("credential_kind", "text", True, "", "", True, None),
            ("bearer_subject", "text", False, "", "", True, None),
            ("bearer_jti", "text", False, "", "", True, None),
            (
                "bearer_expires_at",
                "timestamp with time zone",
                False,
                "",
                "",
                True,
                None,
            ),
            ("api_key_id", "text", False, "", "", True, None),
            ("required_scope", "text", False, "", "", True, None),
            (
                "created_at",
                "timestamp with time zone",
                True,
                "",
                "",
                True,
                None,
            ),
            (
                "expires_at",
                "timestamp with time zone",
                True,
                "",
                "",
                True,
                None,
            ),
            (
                "consumed_at",
                "timestamp with time zone",
                False,
                "",
                "",
                True,
                None,
            ),
            ("bearer_family_id", "text", False, "", "", True, None),
            ("bearer_auth_epoch", "bigint", False, "", "", True, None),
        ]
        if actual_columns != expected_columns:
            census["c"] = _postgres_ticket_column_census(
                rows,
                expected_columns,
            )
            if not any(census["c"].values()):
                census["c"]["g"] = 1
            rejected = True

        try:
            constraint_rows = await connection.fetch(
                """
            SELECT con.conname, con.contype::text AS contype,
                   con.conrelid::bigint AS table_oid,
                   con.confrelid::bigint AS referenced_oid,
                   con.conindid::bigint AS constraint_index_oid,
                   con.convalidated,
                   con.condeferrable, con.condeferred,
                   pg_get_constraintdef(con.oid, true) AS definition,
                   ref_nsp.oid::bigint AS referenced_schema_oid,
                   ref_nsp.nspname AS referenced_schema,
                   ref_rel.relname AS referenced_table,
                   ARRAY(
                       SELECT local_att.attname
                       FROM unnest(con.conkey) WITH ORDINALITY AS key(attnum, ordinality)
                       JOIN pg_attribute AS local_att
                         ON local_att.attrelid=con.conrelid
                        AND local_att.attnum=key.attnum
                       ORDER BY key.ordinality
                   ) AS local_columns,
                   ARRAY(
                       SELECT remote_att.attname
                       FROM unnest(con.confkey) WITH ORDINALITY AS key(attnum, ordinality)
                       JOIN pg_attribute AS remote_att
                         ON remote_att.attrelid=con.confrelid
                        AND remote_att.attnum=key.attnum
                       ORDER BY key.ordinality
                   ) AS remote_columns,
                   con.confupdtype::text AS confupdtype,
                   con.confdeltype::text AS confdeltype
            FROM pg_constraint AS con
            LEFT JOIN pg_class AS ref_rel ON ref_rel.oid=con.confrelid
            LEFT JOIN pg_namespace AS ref_nsp ON ref_nsp.oid=ref_rel.relnamespace
            WHERE con.conrelid=$1::oid
            ORDER BY con.conname
                """,
                table_oid,
            )
        except asyncio.CancelledError:
            raise
        except Exception:
            constraint_rows = []
            census["z"]["q"] |= 0x04
            rejected = True
        actual_constraints = {str(row["conname"]): row for row in constraint_rows}
        expected_constraint_names = {
            "ck_ws_ticket_bearer_expires_finite",
            "ck_ws_ticket_consumed_at",
            "ck_ws_ticket_consumed_finite",
            "ck_ws_ticket_created_at",
            "ck_ws_ticket_created_finite",
            "ck_ws_ticket_expires_at",
            "ck_ws_ticket_expires_finite",
            "ck_ws_ticket_hash",
            "ck_ws_ticket_kind",
            "ck_ws_ticket_source_shape",
            "ck_ws_ticket_time_order",
            "fk_ws_ticket_api_key",
            "fk_ws_ticket_bearer_family",
            "fk_ws_ticket_campaign",
            "fk_ws_ticket_user",
            "websocket_tickets_pkey",
        }
        constraint_ordinals = {
            name: ordinal for ordinal, name in enumerate(sorted(expected_constraint_names))
        }
        if set(actual_constraints) != expected_constraint_names:
            for name in expected_constraint_names - set(actual_constraints):
                census["q"]["m"] |= 1 << constraint_ordinals[name]
            census["q"]["e"] = int(
                bool(set(actual_constraints) - expected_constraint_names)
                or len(actual_constraints) != len(constraint_rows)
            )
            rejected = True

        try:
            q78_split = await _postgres_q78_split_observation(
                connection,
                table_oid=table_oid,
                schema_oid=schema_oid,
                schema_name=schema_name,
                constraint_rows=constraint_rows,
            )
        except asyncio.CancelledError:
            raise
        except Exception:
            q78_split = _postgres_q78_split_invariant(
                {
                    field: (0x3F if field == "x" else 0)
                    for field, _maximum, _width in _POSTGRES_Q78_SPLIT_INDEX_FIELDS
                },
                primary_binding=1,
                opclass_missing=3,
                opclass_extra=0,
                opclass_shape=1,
                schema_mismatch=0,
                schema_shape=1,
                reference_missing=7,
                reference_extra=0,
                reference_order=7,
                reference_binding=7,
                reference_shape=1,
                query_failure=7,
            )

        try:
            index_rows = await _postgres_q78_isolated_call(
                connection,
                "fetch",
                """
            SELECT ind.indrelid::bigint AS table_oid,
                   ind.indexrelid::bigint AS index_oid,
                   idx.relnamespace::bigint AS index_schema_oid,
                   idx_nsp.nspname AS index_schema,
                   idx.relname AS index_name,
                   idx.relkind::text AS index_relkind,
                   idx.relpersistence::text AS index_relpersistence,
                   idx.relispartition AS index_is_partition,
                   am.amname AS access_method,
                   ind.indisunique,
                   ind.indisprimary,
                   ind.indisvalid,
                   ind.indisready,
                   ind.indislive,
                   ind.indnkeyatts,
                   ind.indnatts,
                   ARRAY(
                       SELECT CASE
                           WHEN key.attnum=0 THEN NULL
                           ELSE att.attname
                       END
                       FROM unnest(ind.indkey) WITH ORDINALITY AS key(attnum, ordinality)
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY key.ordinality
                   ) AS columns,
                   ARRAY(
                       SELECT option
                       FROM unnest(ind.indoption) WITH ORDINALITY AS item(option, ordinality)
                       ORDER BY item.ordinality
                   ) AS column_options,
                   ARRAY(
                       SELECT opc.opcname
                       FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       ORDER BY item.ordinality
                   ) AS operator_classes,
                   ARRAY(
                       SELECT opc.oid::bigint
                       FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       ORDER BY item.ordinality
                   ) AS operator_class_oids,
                   ARRAY(
                       SELECT opc_nsp.nspname
                       FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                       JOIN pg_opclass AS opc ON opc.oid=item.opclass
                       JOIN pg_namespace AS opc_nsp
                         ON opc_nsp.oid=opc.opcnamespace
                       ORDER BY item.ordinality
                   ) AS operator_class_namespaces,
                   ARRAY(
                       SELECT item.collation_oid=att.attcollation
                       FROM unnest(ind.indcollation) WITH ORDINALITY
                            AS item(collation_oid, ordinality)
                       JOIN unnest(ind.indkey) WITH ORDINALITY
                            AS key(attnum, ordinality)
                         ON key.ordinality=item.ordinality
                       LEFT JOIN pg_attribute AS att
                         ON att.attrelid=ind.indrelid
                        AND att.attnum=key.attnum
                       ORDER BY item.ordinality
                   ) AS column_collations_match,
                   pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
                   pg_get_expr(ind.indpred, ind.indrelid) AS predicate,
                   pg_get_indexdef(ind.indexrelid) AS definition
            FROM pg_index AS ind
            JOIN pg_class AS idx ON idx.oid=ind.indexrelid
            JOIN pg_namespace AS idx_nsp ON idx_nsp.oid=idx.relnamespace
            JOIN pg_am AS am ON am.oid=idx.relam
            WHERE ind.indrelid=$1::oid
            ORDER BY idx.relname
                """,
                table_oid,
            )
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            index_rows = []
            census["z"]["q"] |= 0x08
            rejected = True
            q78_cause = exc
            q78_failure = _postgres_q78_failure(
                exc,
                subphase=_postgres_q78_subphase(exc),
            )
        try:
            canonical_opclass_rows = await _postgres_q78_isolated_call(
                connection,
                "fetch",
                """
            SELECT opc.opcname, opc.oid::bigint AS opclass_oid
            FROM pg_opclass AS opc
            JOIN pg_namespace AS nsp ON nsp.oid=opc.opcnamespace
            JOIN pg_am AS am ON am.oid=opc.opcmethod
            WHERE nsp.nspname='pg_catalog'
              AND am.amname='btree'
              AND opc.opcname=ANY($1::text[])
                """,
                ["text_ops", "timestamptz_ops"],
            )
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            canonical_opclass_rows = []
            census["z"]["q"] |= 0x10
            rejected = True
            if not q78_failure:
                q78_cause = exc
                q78_failure = _postgres_q78_failure(
                    exc,
                    subphase=_postgres_q78_subphase(exc),
                )
        canonical_opclasses = {
            str(row["opcname"]): int(row["opclass_oid"]) for row in canonical_opclass_rows
        }
        if set(canonical_opclasses) != {"text_ops", "timestamptz_ops"}:
            census["o"]["m"] = int("text_ops" not in canonical_opclasses) | (
                int("timestamptz_ops" not in canonical_opclasses) << 1
            )
            census["o"]["e"] = int(bool(set(canonical_opclasses) - {"text_ops", "timestamptz_ops"}))
            rejected = True

        index_oids: set[int] = set()
        index_identity_failures: set[str] = set()
        index_identity_alternates: set[str] = set()
        for row in index_rows:
            operator_classes = tuple(str(opclass) for opclass in row["operator_classes"])
            operator_class_oids = tuple(
                int(opclass_oid) for opclass_oid in row["operator_class_oids"]
            )
            canonical_oids = tuple(
                canonical_opclasses.get(opclass, -1) for opclass in operator_classes
            )
            index_oid = int(row["index_oid"])
            relkind, relkind_alternate, relkind_unexpected = _postgres_internal_character(
                row["index_relkind"]
            )
            persistence, persistence_alternate, persistence_unexpected = (
                _postgres_internal_character(row["index_relpersistence"])
            )
            identity_noncodec_is_exact = (
                int(row["table_oid"]) == table_oid
                and int(row["index_schema_oid"]) == schema_oid
                and str(row["index_schema"]) == schema_name
                and not bool(row["index_is_partition"])
                and tuple(str(namespace) for namespace in row["operator_class_namespaces"])
                == ("pg_catalog",) * len(operator_classes)
                and operator_class_oids == canonical_oids
                and index_oid > 0
                and index_oid not in index_oids
            )
            identity_is_exact = (
                identity_noncodec_is_exact
                and type(row["index_relkind"]) is str
                and relkind == "i"
                and type(row["index_relpersistence"]) is str
                and persistence == "p"
            )
            if not identity_is_exact:
                index_identity_failures.add(str(row["index_name"]))
                if (
                    not relkind_unexpected
                    and not persistence_unexpected
                    and identity_noncodec_is_exact
                    and relkind == "i"
                    and persistence == "p"
                    and (relkind_alternate or persistence_alternate)
                ):
                    index_identity_alternates.add(str(row["index_name"]))
            index_oids.add(index_oid)

        actual_indexes = {
            str(row["index_name"]): (
                str(row["access_method"]),
                bool(row["indisunique"]),
                bool(row["indisprimary"]),
                bool(row["indisvalid"]),
                bool(row["indisready"]),
                bool(row["indislive"]),
                int(row["indnkeyatts"]),
                int(row["indnatts"]),
                tuple(None if column is None else str(column) for column in row["columns"]),
                tuple(int(option) for option in row["column_options"]),
                tuple(str(opclass) for opclass in row["operator_classes"]),
                tuple(bool(value) for value in row["column_collations_match"]),
                None if row["expressions"] is None else str(row["expressions"]),
                None if row["predicate"] is None else str(row["predicate"]),
            )
            for row in index_rows
        }
        expected_indexes = {
            "idx_ws_tickets_api_key": (
                "btree",
                False,
                False,
                True,
                True,
                True,
                1,
                1,
                ("api_key_id",),
                (0,),
                ("text_ops",),
                (True,),
                None,
                None,
            ),
            "idx_ws_tickets_bearer_family": (
                "btree",
                False,
                False,
                True,
                True,
                True,
                1,
                1,
                ("bearer_family_id",),
                (0,),
                ("text_ops",),
                (True,),
                None,
                None,
            ),
            "idx_ws_tickets_campaign": (
                "btree",
                False,
                False,
                True,
                True,
                True,
                1,
                1,
                ("campaign_id",),
                (0,),
                ("text_ops",),
                (True,),
                None,
                None,
            ),
            "idx_ws_tickets_expires": (
                "btree",
                False,
                False,
                True,
                True,
                True,
                1,
                1,
                ("expires_at",),
                (0,),
                ("timestamptz_ops",),
                (True,),
                None,
                None,
            ),
            "idx_ws_tickets_user": (
                "btree",
                False,
                False,
                True,
                True,
                True,
                1,
                1,
                ("user_id",),
                (0,),
                ("text_ops",),
                (True,),
                None,
                None,
            ),
            "websocket_tickets_pkey": (
                "btree",
                True,
                True,
                True,
                True,
                True,
                1,
                1,
                ("ticket_hash",),
                (0,),
                ("text_ops",),
                (True,),
                None,
                None,
            ),
        }
        if actual_indexes != expected_indexes:
            expected_index_names = tuple(expected_indexes)
            index_ordinals = {name: ordinal for ordinal, name in enumerate(expected_index_names)}
            actual_index_names = tuple(str(row["index_name"]) for row in index_rows)
            actual_index_name_set = set(actual_index_names)
            for name in set(expected_index_names) - actual_index_name_set:
                census["i"]["m"] |= 1 << index_ordinals[name]
            census["i"]["e"] = int(
                bool(actual_index_name_set - set(expected_index_names))
                or len(actual_indexes) != len(index_rows)
            )
            for ordinal, name in enumerate(expected_index_names):
                bit = 1 << ordinal
                if ordinal >= len(actual_index_names) or actual_index_names[ordinal] != name:
                    census["i"]["o"] |= bit
                actual = actual_indexes.get(name)
                if actual is None:
                    continue
                expected = expected_indexes[name]
                for field, position in (
                    ("b", 0),
                    ("u", 1),
                    ("p", 2),
                    ("v", 3),
                    ("r", 4),
                    ("l", 5),
                    ("k", 6),
                    ("n", 7),
                    ("c", 8),
                    ("d", 9),
                    ("a", 10),
                    ("j", 11),
                    ("s", 12),
                    ("t", 13),
                ):
                    if actual[position] != expected[position]:
                        census["i"][field] |= bit
            rejected = True

        expected_index_ordinals = {name: ordinal for ordinal, name in enumerate(expected_indexes)}
        for name in index_identity_failures:
            ordinal = expected_index_ordinals.get(name)
            if ordinal is None:
                census["i"]["e"] = 1
                continue
            bit = 1 << ordinal
            if name in index_identity_alternates:
                census["i"]["h"] |= bit
            else:
                census["i"]["y"] |= bit
        if index_identity_failures:
            rejected = True

        expected_constraint_fragments = {
            "websocket_tickets_pkey": ("p", "PRIMARY KEY (ticket_hash)"),
            "fk_ws_ticket_campaign": (
                "f",
                "FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE",
            ),
            "fk_ws_ticket_user": (
                "f",
                "FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE",
            ),
            "fk_ws_ticket_api_key": (
                "f",
                "FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE CASCADE",
            ),
            "fk_ws_ticket_bearer_family": (
                "f",
                "FOREIGN KEY (bearer_family_id, user_id) "
                "REFERENCES refresh_token_families(id, user_id) ON DELETE CASCADE",
            ),
        }
        for name, definition in expected_constraint_fragments.items():
            row = actual_constraints.get(name)
            if row is None:
                continue
            bit = 1 << constraint_ordinals[name]
            contype, alternate, unexpected = _postgres_internal_character(row["contype"])
            mismatch = False
            if unexpected:
                census["q"]["x"] |= bit
                mismatch = True
            elif contype != definition[0]:
                census["q"]["t"] |= bit
                mismatch = True
            elif alternate:
                census["q"]["a"] |= bit
                mismatch = True
            if not bool(row["convalidated"]):
                census["q"]["v"] |= bit
                mismatch = True
            if bool(row["condeferrable"]):
                census["q"]["f"] |= bit
                mismatch = True
            if bool(row["condeferred"]):
                census["q"]["d"] |= bit
                mismatch = True
            if " ".join(str(row["definition"]).split()) != definition[1]:
                census["q"]["s"] |= bit
                mismatch = True
            rejected = rejected or mismatch

        try:
            raw_referenced_schema = await _postgres_q78_isolated_call(
                connection,
                "fetchval",
                "SELECT current_schema()",
            )
            referenced_schema = raw_referenced_schema if type(raw_referenced_schema) is str else ""
            if not referenced_schema:
                census["z"]["x"] |= 0x20
                rejected = True
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            referenced_schema = ""
            census["z"]["q"] |= 0x20
            rejected = True
            if not q78_failure:
                q78_cause = exc
                q78_failure = _postgres_q78_failure(
                    exc,
                    subphase=_postgres_q78_subphase(exc),
                )
        expected_foreign_keys = {
            "fk_ws_ticket_campaign": (
                ("campaign_id",),
                referenced_schema,
                "campaigns",
                ("id",),
                "a",
                "c",
            ),
            "fk_ws_ticket_user": (
                ("user_id",),
                referenced_schema,
                "users",
                ("id",),
                "a",
                "c",
            ),
            "fk_ws_ticket_api_key": (
                ("api_key_id",),
                referenced_schema,
                "api_keys",
                ("id",),
                "a",
                "c",
            ),
            "fk_ws_ticket_bearer_family": (
                ("bearer_family_id", "user_id"),
                referenced_schema,
                "refresh_token_families",
                ("id", "user_id"),
                "a",
                "c",
            ),
        }
        try:
            referenced_rows = await _postgres_q78_isolated_call(
                connection,
                "fetch",
                """
            SELECT rel.relname, rel.oid::bigint AS relation_oid
            FROM pg_class AS rel
            WHERE rel.relnamespace=$1::oid
              AND rel.relname=ANY($2::text[])
                """,
                schema_oid,
                ["api_keys", "campaigns", "refresh_token_families", "users"],
            )
        except asyncio.CancelledError:
            raise
        except Exception as exc:
            referenced_rows = []
            census["z"]["q"] |= 0x40
            rejected = True
            if not q78_failure:
                q78_cause = exc
                q78_failure = _postgres_q78_failure(
                    exc,
                    subphase=_postgres_q78_subphase(exc),
                )
        referenced_oids = {str(row["relname"]): int(row["relation_oid"]) for row in referenced_rows}
        if set(referenced_oids) != {
            "api_keys",
            "campaigns",
            "refresh_token_families",
            "users",
        }:
            census["f"]["i"] = 0xF
            rejected = True

        for name, row in actual_constraints.items():
            ordinal = constraint_ordinals.get(name)
            if ordinal is not None and int(row["table_oid"]) != table_oid:
                census["q"]["b"] |= 1 << ordinal
                rejected = True

        foreign_key_ordinals = {name: ordinal for ordinal, name in enumerate(expected_foreign_keys)}
        for name, expected in expected_foreign_keys.items():
            row = actual_constraints.get(name)
            if row is None:
                continue
            bit = 1 << foreign_key_ordinals[name]
            actual = (
                tuple(str(column) for column in row["local_columns"]),
                str(row["referenced_schema"]),
                str(row["referenced_table"]),
                tuple(str(column) for column in row["remote_columns"]),
                str(row["confupdtype"]),
                str(row["confdeltype"]),
            )
            reference_identity_is_exact = (
                row["referenced_schema_oid"] is not None
                and int(row["referenced_schema_oid"]) == schema_oid
                and int(row["referenced_oid"]) == referenced_oids.get(expected[2])
            )
            for field, actual_value, expected_value in zip(
                ("l", "s", "t", "r"), actual[:4], expected[:4], strict=True
            ):
                if actual_value != expected_value:
                    census["f"][field] |= bit
                    rejected = True
            for field, raw_value, expected_value in (
                ("u", row["confupdtype"], expected[4]),
                ("d", row["confdeltype"], expected[5]),
            ):
                semantic, alternate, unexpected = _postgres_internal_character(raw_value)
                if unexpected:
                    census["f"]["x"] |= bit
                    rejected = True
                elif semantic != expected_value:
                    census["f"][field] |= bit
                    rejected = True
                elif alternate:
                    census["f"]["a"] |= bit
                    rejected = True
            if not reference_identity_is_exact:
                census["f"]["i"] |= bit
                rejected = True

        primary = actual_constraints.get("websocket_tickets_pkey")
        primary_index = next(
            (row for row in index_rows if str(row["index_name"]) == "websocket_tickets_pkey"),
            None,
        )
        if primary is not None:
            if tuple(str(column) for column in primary["local_columns"]) != ("ticket_hash",):
                census["p"]["l"] = 1
                rejected = True
            if tuple(primary["remote_columns"]):
                census["p"]["r"] = 1
                rejected = True
            if primary["referenced_schema"] is not None or primary["referenced_table"] is not None:
                census["p"]["n"] = 1
                rejected = True
            if primary_index is None or int(primary["constraint_index_oid"]) != int(
                primary_index["index_oid"]
            ):
                census["p"]["i"] = 1
                rejected = True

        expected_check_definitions = {
            "ck_ws_ticket_hash": ("CHECK (ticket_hash ~ '^[0-9a-f]{64}$'::text)"),
            "ck_ws_ticket_kind": (
                "CHECK (credential_kind = ANY (ARRAY['bearer'::text, 'api_key'::text]))"
            ),
            "ck_ws_ticket_created_at": "CHECK (created_at < expires_at)",
            "ck_ws_ticket_expires_at": "CHECK (expires_at > created_at)",
            "ck_ws_ticket_consumed_at": ("CHECK (consumed_at IS NULL OR consumed_at < expires_at)"),
            "ck_ws_ticket_bearer_expires_finite": (
                "CHECK (bearer_expires_at IS NULL OR isfinite(bearer_expires_at))"
            ),
            "ck_ws_ticket_created_finite": "CHECK (isfinite(created_at))",
            "ck_ws_ticket_expires_finite": "CHECK (isfinite(expires_at))",
            "ck_ws_ticket_consumed_finite": (
                "CHECK (consumed_at IS NULL OR isfinite(consumed_at))"
            ),
            "ck_ws_ticket_time_order": (
                "CHECK (expires_at > created_at AND "
                "(consumed_at IS NULL OR consumed_at < expires_at))"
            ),
            "ck_ws_ticket_source_shape": (
                "CHECK (credential_kind = 'bearer'::text "
                "AND bearer_subject IS NOT NULL "
                "AND length(btrim(bearer_subject)) > 0 "
                "AND bearer_subject = btrim(bearer_subject) "
                "AND bearer_jti IS NOT NULL "
                "AND length(btrim(bearer_jti)) > 0 "
                "AND bearer_jti = btrim(bearer_jti) "
                "AND bearer_expires_at IS NOT NULL "
                "AND bearer_family_id IS NOT NULL "
                "AND bearer_auth_epoch IS NOT NULL "
                "AND bearer_auth_epoch >= 1 "
                "AND api_key_id IS NULL "
                "AND required_scope IS NULL "
                "OR credential_kind = 'api_key'::text "
                "AND bearer_subject IS NULL "
                "AND bearer_jti IS NULL "
                "AND bearer_expires_at IS NULL "
                "AND bearer_family_id IS NULL "
                "AND bearer_auth_epoch IS NULL "
                "AND api_key_id IS NOT NULL "
                "AND length(btrim(api_key_id)) > 0 "
                "AND api_key_id = btrim(api_key_id) "
                "AND required_scope = 'read'::text)"
            ),
        }
        check_ordinals = {name: ordinal for ordinal, name in enumerate(expected_check_definitions)}
        for name, expected_definition in expected_check_definitions.items():
            row = actual_constraints.get(name)
            if row is None:
                continue
            bit = 1 << check_ordinals[name]
            constraint_bit = 1 << constraint_ordinals[name]
            contype, alternate, unexpected = _postgres_internal_character(row["contype"])
            if unexpected:
                census["h"]["x"] |= bit
                census["q"]["x"] |= constraint_bit
                rejected = True
            elif contype != "c":
                census["h"]["t"] |= bit
                census["q"]["t"] |= constraint_bit
                rejected = True
            elif alternate:
                census["h"]["a"] |= bit
                census["q"]["a"] |= constraint_bit
                rejected = True
            if not bool(row["convalidated"]):
                census["h"]["v"] |= bit
                census["q"]["v"] |= constraint_bit
                rejected = True
            if bool(row["condeferrable"]):
                census["h"]["f"] |= bit
                census["q"]["f"] |= constraint_bit
                rejected = True
            if bool(row["condeferred"]):
                census["h"]["d"] |= bit
                census["q"]["d"] |= constraint_bit
                rejected = True
            actual_definition = " ".join(str(row["definition"]).split())
            if actual_definition != expected_definition:
                census["h"]["s"] |= bit
                census["q"]["s"] |= constraint_bit
                rejected = True

        if rejected:
            invariant = _postgres_ticket_schema_census_invariant(census)
            raise _postgres_ticket_schema_error(
                invariant,
                cause=q78_cause,
                q78_failure=q78_failure,
                q78_split=q78_split,
            )

    # ── Helpers ────────────────────────────────────────────────────────────────

    def _enc_val(self, v: str | None) -> str | None:
        return self._enc.encrypt(v) if self._enc and v else v

    def _dec_val(self, v: str | None) -> str | None:
        return self._enc.decrypt(v) if self._enc and v else v

    def _row_to_dict(self, row: Any) -> dict[str, Any]:
        """Convert asyncpg Record to plain dict; convert datetime → ISO string."""
        if row is None:
            return {}
        d = dict(row)
        for k, v in d.items():
            if isinstance(v, datetime):
                d[k] = v.isoformat()
        return d

    # ── Campaigns ──────────────────────────────────────────────────────────────

    async def save_campaign(self, c: Any) -> None:

        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO campaigns(id,name,client,operator,noise_profile,status,scope_json,targets_json,notes)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)
                ON CONFLICT(id) DO UPDATE SET
                  name=EXCLUDED.name, client=EXCLUDED.client, operator=EXCLUDED.operator,
                  noise_profile=EXCLUDED.noise_profile, status=EXCLUDED.status,
                  scope_json=EXCLUDED.scope_json, targets_json=EXCLUDED.targets_json,
                  notes=EXCLUDED.notes,
                  updated_at=now()
            """,
                c.id,
                c.name,
                c.client,
                c.operator,
                c.noise_profile.value
                if hasattr(c.noise_profile, "value")
                else str(c.noise_profile),
                c.status.value if hasattr(c.status, "value") else str(c.status),
                json.dumps([s.model_dump() for s in c.scope]),
                json.dumps(c.targets),
                c.notes,
            )

    async def update_campaign_status(self, campaign_id: str, status: str) -> bool:
        """Update campaign operational status (e.g. 'created' -> 'running')."""
        async with self._pool.acquire() as conn:
            tag = await conn.execute(
                "UPDATE campaigns SET status = $1, updated_at = now() WHERE id = $2",
                status,
                campaign_id,
            )
            return tag.endswith("1")

    async def get_campaign(self, campaign_id: str) -> dict[str, Any] | None:
        async with self._pool.acquire() as conn:
            row = await conn.fetchrow("SELECT * FROM campaigns WHERE id=$1", campaign_id)
        return self._row_to_dict(row) if row else None

    async def list_campaigns(
        self, page: int = 1, per_page: int = 50, operator: str | None = None
    ) -> tuple[list[dict[str, Any]], int]:
        offset = (page - 1) * per_page
        where = "WHERE operator=$1" if operator else ""
        params = [operator] if operator else []

        async with self._pool.acquire() as conn:
            total = await conn.fetchval(f"SELECT COUNT(*) FROM campaigns {where}", *params)
            rows = await conn.fetch(
                f"SELECT * FROM campaigns {where} ORDER BY created_at DESC LIMIT ${len(params) + 1} OFFSET ${len(params) + 2}",
                *params,
                per_page,
                offset,
            )
        return [self._row_to_dict(r) for r in rows], total or 0

    async def delete_campaign(
        self,
        campaign_id: str,
    ) -> bool:
        """Delete once through the stable compatibility receipt authority."""
        result = await self.delete_campaign_lifecycle(campaign_id)
        return result.result is FixedResult.APPLIED

    async def delete_campaign_lifecycle(
        self,
        campaign_id: str,
        *,
        operation_id: str | None = None,
        lifecycle_operation_id: str | None = None,
        principal_kind: str = "system",
        principal_subject_ref: str | None = None,
        principal_user_id: str | None = None,
        principal_authority_revision: int | None = None,
    ) -> OperationResult:
        """Delete a campaign with explicit immutable operation/principal binding."""
        if (
            operation_id is not None
            and lifecycle_operation_id is not None
            and operation_id != lifecycle_operation_id
        ):
            return OperationResult(FixedResult.INVALID_CONTRACT, None)
        resolved_operation_id = operation_id if operation_id is not None else lifecycle_operation_id
        if resolved_operation_id is None:
            resolved_operation_id = _campaign_delete_operation_id(campaign_id)
        if principal_subject_ref is None:
            if principal_kind != "system":
                return OperationResult(FixedResult.INVALID_CONTRACT, None)
            principal_subject_ref = SYSTEM_PRINCIPAL_SUBJECT_REF

        receipt_target_id = _campaign_target_uuid(campaign_id)
        store = ExecutionLifecycleStore(self._pool, "postgresql")
        try:
            receipt_spec = store._receipt_spec(
                operation_id=resolved_operation_id,
                operation_code="campaign_delete",
                campaign_id=receipt_target_id,
                primary_target_id=receipt_target_id,
                principal_kind=principal_kind,
                principal_subject_ref=principal_subject_ref,
                principal_user_id=principal_user_id,
                principal_authority_revision=principal_authority_revision,
            )
        except ValueError:
            return OperationResult(FixedResult.INVALID_CONTRACT, None)

        async with store._transaction() as conn:
            await store._acquire_transaction_key(conn, resolved_operation_id)
            replay = await store._classify_receipt(conn, receipt_spec, current_revision=None)
            if replay is not None:
                return replay
            await store._acquire_transaction_key(conn, receipt_target_id)
            await conn.fetchrow(
                "SELECT singleton_id FROM execution_gateway_state WHERE singleton_id=1 FOR UPDATE"
            )
            campaign_row = await conn.fetchrow(
                "SELECT id FROM campaigns WHERE id=$1 FOR UPDATE", campaign_id
            )
            if campaign_row is None:
                return OperationResult(FixedResult.NOT_FOUND_OR_PURGED, None)
            await conn.fetch(
                "SELECT campaign_id FROM campaign_execution_authority_revisions "
                "WHERE campaign_id=$1 FOR UPDATE",
                campaign_id,
            )
            actor_grant_rows = await conn.fetch(
                "SELECT actor_user_id AS id FROM campaign_execution_actor_grants "
                "WHERE campaign_id=$1 ORDER BY actor_user_id FOR UPDATE",
                campaign_id,
            )
            destination_authority_rows = await conn.fetch(
                "SELECT campaign_id AS id FROM campaign_execution_destination_authorities "
                "WHERE campaign_id=$1 FOR UPDATE",
                campaign_id,
            )
            approval_authority_rows = await conn.fetch(
                "SELECT id FROM execution_approval_authorities WHERE campaign_id=$1 "
                "ORDER BY id FOR UPDATE",
                campaign_id,
            )
            logical_rows = await conn.fetch(
                "SELECT id,closed_at FROM logical_executions WHERE campaign_id=$1 "
                "ORDER BY id FOR UPDATE",
                campaign_id,
            )
            attempt_rows = await conn.fetch(
                "SELECT id,state,ordinal FROM execution_attempts WHERE campaign_id=$1 "
                "ORDER BY ordinal,id FOR UPDATE",
                campaign_id,
            )
            destination_observation_rows = await conn.fetch(
                "SELECT attempt_id || ':' || ordinal::text AS id "
                "FROM execution_attempt_destination_observations WHERE campaign_id=$1 "
                "ORDER BY attempt_id,ordinal FOR UPDATE",
                campaign_id,
            )
            credential_observation_rows = await conn.fetch(
                "SELECT attempt_id || ':' || ordinal::text AS id "
                "FROM execution_attempt_credential_observations WHERE campaign_id=$1 "
                "ORDER BY attempt_id,ordinal FOR UPDATE",
                campaign_id,
            )
            budget_rows = await conn.fetch(
                "SELECT id,budget_kind FROM campaign_execution_budgets WHERE campaign_id=$1 "
                "ORDER BY budget_kind,id FOR UPDATE",
                campaign_id,
            )
            ledger_rows = await conn.fetch(
                "SELECT id,budget_kind FROM campaign_execution_budget_ledger "
                "WHERE campaign_id=$1 ORDER BY budget_kind,id FOR UPDATE",
                campaign_id,
            )
            await conn.fetch(
                "SELECT id FROM hosts WHERE campaign_id=$1 ORDER BY ip_address,id FOR UPDATE",
                campaign_id,
            )
            for table in ("findings", "credentials", "loot"):
                await conn.fetch(
                    f"SELECT id FROM {table} WHERE campaign_id=$1 ORDER BY id FOR UPDATE",
                    campaign_id,
                )
            link_rows = await conn.fetch(
                "SELECT id FROM execution_output_links WHERE campaign_id=$1 ORDER BY id FOR UPDATE",
                campaign_id,
            )
            outbox_rows = await conn.fetch(
                "SELECT id,publication_state FROM execution_publication_outbox "
                "WHERE campaign_id=$1 ORDER BY id FOR UPDATE",
                campaign_id,
            )
            approval_rows = await conn.fetch(
                "SELECT id FROM execution_attempt_approvals WHERE campaign_id=$1 "
                "ORDER BY id FOR UPDATE",
                campaign_id,
            )
            blocked = await conn.fetchval(
                "SELECT EXISTS(SELECT 1 FROM execution_attempts WHERE campaign_id=$1 "
                "AND state IN ('accepted','queued','dispatching','running','cancelling',"
                "'settlement_pending')) "
                "OR EXISTS(SELECT 1 FROM logical_executions WHERE campaign_id=$1 "
                "AND closed_at IS NULL) "
                "OR EXISTS(SELECT 1 FROM execution_publication_outbox WHERE campaign_id=$1 "
                "AND publication_state='claimed')",
                campaign_id,
            )
            if blocked:
                raise RuntimeError("Campaign lifecycle deletion is blocked")

            async def delete_exact(statement: str, expected: tuple[str, ...]) -> None:
                deleted = await conn.fetch(statement, campaign_id)
                observed = tuple(sorted(str(row["id"]) for row in deleted))
                if observed != tuple(sorted(expected)):
                    raise RuntimeError("Campaign lifecycle deletion invariant failed")

            await delete_exact(
                "DELETE FROM execution_output_links WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in link_rows),
            )
            await delete_exact(
                "DELETE FROM execution_publication_outbox WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in outbox_rows),
            )
            await delete_exact(
                "DELETE FROM execution_attempt_approvals WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in approval_rows),
            )
            await delete_exact(
                "DELETE FROM execution_approval_authorities WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in approval_authority_rows),
            )
            await delete_exact(
                "DELETE FROM execution_attempt_destination_observations WHERE campaign_id=$1 "
                "RETURNING attempt_id || ':' || ordinal::text AS id",
                tuple(str(row["id"]) for row in destination_observation_rows),
            )
            await delete_exact(
                "DELETE FROM execution_attempt_credential_observations WHERE campaign_id=$1 "
                "RETURNING attempt_id || ':' || ordinal::text AS id",
                tuple(str(row["id"]) for row in credential_observation_rows),
            )
            await delete_exact(
                "DELETE FROM campaign_execution_budget_ledger WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in ledger_rows),
            )
            for attempt in reversed(attempt_rows):
                deleted = await conn.fetchrow(
                    "DELETE FROM execution_attempts WHERE id=$1 RETURNING id",
                    attempt["id"],
                )
                if deleted is None or str(deleted["id"]) != str(attempt["id"]):
                    raise RuntimeError("Campaign lifecycle deletion invariant failed")
            await delete_exact(
                "DELETE FROM logical_executions WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in logical_rows),
            )
            await delete_exact(
                "DELETE FROM campaign_execution_budgets WHERE campaign_id=$1 RETURNING id",
                tuple(str(row["id"]) for row in budget_rows),
            )
            await delete_exact(
                "DELETE FROM campaign_execution_actor_grants WHERE campaign_id=$1 "
                "RETURNING actor_user_id AS id",
                tuple(str(row["id"]) for row in actor_grant_rows),
            )
            await delete_exact(
                "DELETE FROM campaign_execution_destination_authorities WHERE campaign_id=$1 "
                "RETURNING campaign_id AS id",
                tuple(str(row["id"]) for row in destination_authority_rows),
            )
            authority_deleted = await conn.fetch(
                "DELETE FROM campaign_execution_authority_revisions "
                "WHERE campaign_id=$1 RETURNING campaign_id AS id",
                campaign_id,
            )
            if len(authority_deleted) > 1:
                raise RuntimeError("Campaign lifecycle deletion invariant failed")
            for statement in (
                "DELETE FROM loot WHERE campaign_id=$1 RETURNING id",
                "DELETE FROM credentials WHERE campaign_id=$1 RETURNING id",
                "DELETE FROM hosts WHERE campaign_id=$1 RETURNING id",
                "DELETE FROM findings WHERE campaign_id=$1 RETURNING id",
            ):
                await conn.fetch(statement, campaign_id)
            await store._insert_receipt(
                conn,
                receipt_spec,
                result=FixedResult.APPLIED,
                exact_replay_code=FixedResult.REPLAYED,
                result_identity=receipt_target_id,
                result_revision=None,
                result_fields=(("deleted", True),),
            )
            deleted_campaign = await conn.fetchrow(
                "DELETE FROM campaigns WHERE id=$1 RETURNING id", campaign_id
            )
            if deleted_campaign is None or str(deleted_campaign["id"]) != campaign_id:
                raise RuntimeError("Campaign lifecycle deletion invariant failed")
            return OperationResult(FixedResult.APPLIED, None)

    # ── Findings ───────────────────────────────────────────────────────────────

    async def save_finding(self, campaign_id: str, f: Any, module_id: str = "") -> None:
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO findings
                (id,campaign_id,module_id,title,description,severity,cvss_score,cvss_vector,
                 confidence,mitre_technique,mitre_tactic,evidence_json,remediation,host,trace_id,
                 validated,false_positive)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
                ON CONFLICT(id) DO UPDATE SET
                  title=EXCLUDED.title, description=EXCLUDED.description,
                  severity=EXCLUDED.severity, evidence_json=EXCLUDED.evidence_json,
                  validated=EXCLUDED.validated, false_positive=EXCLUDED.false_positive
            """,
                f.id,
                campaign_id,
                module_id or getattr(f, "module_id", ""),
                f.title,
                f.description,
                f.severity.value if hasattr(f.severity, "value") else str(f.severity),
                getattr(f, "cvss_score", 0.0),
                getattr(f, "cvss_vector", ""),
                f.confidence,
                f.mitre_technique,
                f.mitre_tactic,
                json.dumps(f.evidence),
                f.remediation,
                f.host,
                getattr(f, "trace_id", ""),
                int(bool(getattr(f, "validated", False))),
                int(bool(getattr(f, "false_positive", False))),
            )

    async def list_findings(
        self,
        campaign_id: str,
        page: int = 1,
        per_page: int = 50,
        severity: str | None = None,
        false_positive: bool | None = None,
        validated: bool | None = None,
    ) -> tuple[list[dict[str, Any]], int]:
        conditions = ["campaign_id=$1"]
        params: list[Any] = [campaign_id]

        if severity:
            params.append(severity)
            conditions.append(f"severity=${len(params)}")
        if false_positive is not None:
            params.append(int(false_positive))
            conditions.append(f"false_positive=${len(params)}")
        if validated is not None:
            params.append(int(validated))
            conditions.append(f"validated=${len(params)}")

        where = " AND ".join(conditions)
        offset = (page - 1) * per_page

        async with self._pool.acquire() as conn:
            total = await conn.fetchval(f"SELECT COUNT(*) FROM findings WHERE {where}", *params)
            params_page = params + [per_page, offset]
            rows = await conn.fetch(
                f"SELECT * FROM findings WHERE {where} ORDER BY discovered_at DESC"
                f" LIMIT ${len(params) + 1} OFFSET ${len(params) + 2}",
                *params_page,
            )
        return [self._row_to_dict(r) for r in rows], total or 0

    async def get_findings(self, campaign_id: str, confirmed_only: bool = False) -> list[dict]:
        where = (
            "campaign_id=$1 AND validated=1 AND false_positive=0"
            if confirmed_only
            else "campaign_id=$1"
        )
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                f"SELECT * FROM findings WHERE {where} ORDER BY discovered_at DESC",
                campaign_id,
            )
        return [self._row_to_dict(r) for r in rows]

    async def get_finding_stats(self, campaign_id: str) -> dict[str, Any]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT severity, COUNT(*) as n FROM findings WHERE campaign_id=$1 GROUP BY severity",
                campaign_id,
            )
        stats: dict[str, Any] = {
            "total": 0,
            "critical": 0,
            "high": 0,
            "medium": 0,
            "low": 0,
            "info": 0,
        }
        for r in rows:
            stats[r["severity"]] = r["n"]
            stats["total"] += r["n"]
        return stats

    async def get_monthly_confirmed_finding_stats(self, campaign_id: str = "") -> dict[str, Any]:
        """Return confirmed findings grouped by day in the current UTC month."""
        period_start = datetime.now(UTC).replace(
            day=1, hour=0, minute=0, second=0, microsecond=0
        )
        if period_start.month == 12:
            next_month = period_start.replace(year=period_start.year + 1, month=1)
        else:
            next_month = period_start.replace(month=period_start.month + 1)
        period = period_start.strftime("%Y-%m")
        async with self._pool.acquire() as conn:
            if campaign_id:
                confirmed_findings = await conn.fetchval(
                    """
                    SELECT COUNT(*)
                    FROM findings
                    WHERE validated=1
                      AND false_positive=0
                      AND campaign_id=$1
                    """,
                    campaign_id,
                )
                rows = await conn.fetch(
                    """
                    SELECT (discovered_at AT TIME ZONE 'UTC')::date AS finding_date,
                           COUNT(*) AS n
                    FROM findings
                    WHERE validated=1
                      AND false_positive=0
                      AND discovered_at >= $1
                      AND discovered_at < $2
                      AND campaign_id=$3
                    GROUP BY 1
                    ORDER BY 1
                    """,
                    period_start,
                    next_month,
                    campaign_id,
                )
            else:
                confirmed_findings = await conn.fetchval(
                    """
                    SELECT COUNT(*)
                    FROM findings
                    WHERE validated=1
                      AND false_positive=0
                    """
                )
                rows = await conn.fetch(
                    """
                    SELECT (discovered_at AT TIME ZONE 'UTC')::date AS finding_date,
                           COUNT(*) AS n
                    FROM findings
                    WHERE validated=1
                      AND false_positive=0
                      AND discovered_at >= $1
                      AND discovered_at < $2
                    GROUP BY 1
                    ORDER BY 1
                    """,
                    period_start,
                    next_month,
                )
        series = [{"date": row["finding_date"].isoformat(), "count": int(row["n"])} for row in rows]
        return {
            "period": period,
            "label": "Security signals this cycle",
            "total": sum(item["count"] for item in series),
            "confirmed_findings": int(confirmed_findings or 0),
            "series": series,
        }

    # ── Hosts ──────────────────────────────────────────────────────────────────

    async def record_module_run(
        self,
        campaign_id: str,
        module_id: str,
        outcome: str,
        success: bool,
        duration_ms: float,
    ) -> None:
        """Persist non-sensitive execution metadata for restart-safe telemetry."""
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO module_runs
                    (id, campaign_id, module_id, outcome, success, duration_ms, completed_at)
                VALUES ($1, $2, $3, $4, $5, $6, $7)
                """,
                str(uuid.uuid4()),
                campaign_id,
                module_id,
                outcome,
                int(bool(success)),
                max(0.0, float(duration_ms or 0.0)),
                datetime.now(UTC),
            )
            await conn.execute(
                """
                UPDATE campaigns
                   SET status = 'running', updated_at = now()
                 WHERE id = $1 AND status = 'created'
                """,
                campaign_id,
            )

    async def get_telemetry_stats(self, campaign_id: str = "") -> dict[str, Any]:
        """Aggregate persisted execution, finding, and discovered-host telemetry."""
        async with self._pool.acquire() as conn:
            if campaign_id:
                run_rows = await conn.fetch(
                    "SELECT success, duration_ms, completed_at FROM module_runs WHERE campaign_id=$1 ORDER BY completed_at",
                    campaign_id,
                )
                confirmed_findings = int(
                    await conn.fetchval(
                        """
                        SELECT COUNT(*)
                        FROM findings
                        WHERE validated=1 AND false_positive=0 AND campaign_id=$1
                        """,
                        campaign_id,
                    )
                    or 0
                )
                discovered_hosts = int(await conn.fetchval("SELECT COUNT(*) FROM hosts WHERE campaign_id=$1", campaign_id) or 0)
            else:
                run_rows = await conn.fetch(
                    "SELECT success, duration_ms, completed_at FROM module_runs ORDER BY completed_at"
                )
                confirmed_findings = int(
                    await conn.fetchval(
                        """
                        SELECT COUNT(*)
                        FROM findings
                        WHERE validated=1 AND false_positive=0
                        """
                    )
                    or 0
                )
                discovered_hosts = int(await conn.fetchval("SELECT COUNT(*) FROM hosts") or 0)

        total = len(run_rows)
        success = sum(int(row["success"]) for row in run_rows)
        failed = total - success
        durations = sorted(float(row["duration_ms"] or 0.0) for row in run_rows)

        def percentile(fraction: float) -> float | None:
            if not durations:
                return None
            index = max(0, min(len(durations) - 1, int(len(durations) * fraction + 0.999999) - 1))
            return round(durations[index], 1)

        recent_cutoff = datetime.now(UTC) - timedelta(seconds=60)
        recent_runs = sum(
            1
            for row in run_rows
            if row["completed_at"] is not None and row["completed_at"] >= recent_cutoff
        )
        return {
            "campaign_id": campaign_id,
            "modules": {
                "total": total,
                "success": success,
                "failed": failed,
                "error_rate": failed / total if total else 0.0,
            },
            "findings": confirmed_findings,
            "latency_ms": {
                "p50": percentile(0.50),
                "p95": percentile(0.95),
                "p99": percentile(0.99),
            },
            "throughput": {
                "tasks_per_min": float(recent_runs) if recent_runs else None,
            },
            "hosts": {
                "available": False,
                "discovered": discovered_hosts,
                "owned": None,
            },
        }

    async def upsert_host(self, h: Any) -> str:
        async with self._pool.acquire() as conn:
            canonical_id = await conn.fetchval(
                """
                INSERT INTO hosts(id,campaign_id,ip_address,hostname,fqdn,os,os_version,
                    domain,is_dc,open_ports_json,tags_json)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
                ON CONFLICT(campaign_id,ip_address) DO UPDATE SET
                  hostname=EXCLUDED.hostname, os=EXCLUDED.os, is_dc=EXCLUDED.is_dc,
                  fqdn=EXCLUDED.fqdn, os=EXCLUDED.os,
                  os_version=EXCLUDED.os_version, domain=EXCLUDED.domain,
                  is_dc=EXCLUDED.is_dc, open_ports_json=EXCLUDED.open_ports_json,
                  tags_json=EXCLUDED.tags_json, last_seen=now()
                RETURNING id
            """,
                h.id,
                h.campaign_id,
                h.ip_address,
                h.hostname,
                h.fqdn,
                h.os,
                h.os_version,
                h.domain,
                int(h.is_dc),
                json.dumps(h.open_ports),
                json.dumps(h.tags),
            )
        if canonical_id is None:
            raise RuntimeError("Canonical host persistence failed")
        return str(canonical_id)

    async def get_hosts(self, campaign_id: str) -> list[dict]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT * FROM hosts WHERE campaign_id=$1 ORDER BY first_seen", campaign_id
            )
        return [self._row_to_dict(r) for r in rows]

    # ── Credentials ────────────────────────────────────────────────────────────

    async def save_credential(self, c: Any) -> None:
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO credentials
                (id,campaign_id,host_id,username,secret_enc,cred_type,domain,source_module,notes)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)
                ON CONFLICT(id) DO UPDATE SET
                  host_id=EXCLUDED.host_id,username=EXCLUDED.username,
                  secret_enc=EXCLUDED.secret_enc,cred_type=EXCLUDED.cred_type,
                  domain=EXCLUDED.domain,source_module=EXCLUDED.source_module,
                  notes=EXCLUDED.notes
            """,
                c.id,
                c.campaign_id,
                c.host_id,
                c.username,
                self._enc_val(c.secret),
                c.cred_type,
                c.domain,
                c.source_module,
                c.notes,
            )

    async def get_credentials(self, campaign_id: str, decrypt: bool = False) -> list[dict]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT * FROM credentials WHERE campaign_id=$1 ORDER BY captured_at", campaign_id
            )
        result = []
        for r in rows:
            d = self._row_to_dict(r)
            d["secret"] = self._dec_val(d.get("secret_enc")) if decrypt else None
            result.append(d)
        return result

    async def save_credential_preencrypted(self, cred: Any) -> None:
        """
        Persist a credential whose secret is ALREADY encrypted (AES-256-GCM v2) by
        CredentialVault. Skips _enc_val() to prevent double-encryption.
        Mirrors database.py implementation - required by engine._persist_vault_credentials().
        """
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO credentials
                    (id,campaign_id,host_id,username,secret_enc,cred_type,domain,source_module,notes)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)
                ON CONFLICT(id) DO UPDATE SET
                    secret_enc    = EXCLUDED.secret_enc,
                    source_module = EXCLUDED.source_module,
                    notes         = EXCLUDED.notes
            """,
                cred.id,
                cred.campaign_id,
                cred.host_id,
                cred.username,
                cred.secret,  # already vault-encrypted - store verbatim, no _enc_val()
                cred.cred_type,
                cred.domain,
                cred.source_module,
                cred.notes,
            )

    async def load_credentials_raw(self, campaign_id: str) -> list[dict]:
        """
        Load all credentials for a campaign as raw dicts.
        Secrets returned as-is (vault-encrypted: AES-256-GCM v2, with transparent legacy Fernet support) -
        use CredentialVault.restore_from_db_records() to re-hydrate.
        Required by server.py POST /campaigns/{id}/restore-vault endpoint.
        """
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT * FROM credentials WHERE campaign_id=$1 ORDER BY captured_at DESC",
                campaign_id,
            )
        return [self._row_to_dict(r) for r in rows]

    # ── Loot ───────────────────────────────────────────────────────────────────

    async def save_loot(self, l: Any) -> None:
        content_str = json.dumps(l.content) if isinstance(l.content, (dict, list)) else l.content
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO loot
                (id,campaign_id,host_id,loot_type,name,description,content_enc,
                 path_on_target,source_module,tags_json)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
                ON CONFLICT(id) DO UPDATE SET
                  host_id=EXCLUDED.host_id,loot_type=EXCLUDED.loot_type,
                  name=EXCLUDED.name,description=EXCLUDED.description,
                  content_enc=EXCLUDED.content_enc,
                  path_on_target=EXCLUDED.path_on_target,
                  source_module=EXCLUDED.source_module,tags_json=EXCLUDED.tags_json
            """,
                l.id,
                l.campaign_id,
                l.host_id,
                l.loot_type,
                l.name,
                l.description,
                self._enc_val(content_str),
                l.path_on_target,
                l.source_module,
                json.dumps(l.tags),
            )

    def execution_lifecycle_store(self) -> ExecutionLifecycleStore:
        """Return the additive internal lifecycle persistence primitive."""
        return ExecutionLifecycleStore(self._pool, "postgresql")

    async def get_loot(self, campaign_id: str, decrypt: bool = False) -> list[dict]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT * FROM loot WHERE campaign_id=$1 ORDER BY captured_at", campaign_id
            )
        result = []
        for r in rows:
            d = self._row_to_dict(r)
            d["content"] = self._dec_val(d.get("content_enc")) if decrypt else None
            result.append(d)
        return result

    async def save_campaign_graph(self, campaign_id: str, graph: dict[str, Any]) -> None:
        """Persist a sanitized graph snapshot in the existing encrypted loot store."""
        graph_id = f"campaign_graph:{campaign_id}"
        payload = json.dumps(graph, separators=(",", ":"))
        async with self._pool.acquire() as conn:
            await conn.execute(
                """
                INSERT INTO loot
                    (id,campaign_id,loot_type,name,description,content_enc,source_module,tags_json)
                VALUES($1,$2,$3,$4,$5,$6,$7,$8)
                ON CONFLICT(id) DO UPDATE SET
                  description=EXCLUDED.description,
                  content_enc=EXCLUDED.content_enc,
                  source_module=EXCLUDED.source_module,
                  tags_json=EXCLUDED.tags_json,
                  captured_at=now()
                """,
                graph_id,
                campaign_id,
                "campaign_graph",
                "durable_attack_graph",
                "Sanitized artifact and BloodHound graph snapshot",
                self._enc_val(payload),
                "core.graph",
                json.dumps(["runtime", "safe-metadata"]),
            )

    async def get_campaign_graph(self, campaign_id: str) -> dict[str, Any] | None:
        """Load the safe graph snapshot without returning general decrypted loot."""
        async with self._pool.acquire() as conn:
            row = await conn.fetchrow(
                """
                SELECT content_enc FROM loot
                WHERE id=$1 AND campaign_id=$2 AND loot_type='campaign_graph'
                """,
                f"campaign_graph:{campaign_id}",
                campaign_id,
            )
        if not row or not row["content_enc"]:
            return None
        try:
            raw_content = str(row["content_enc"])
            if raw_content.startswith("{") or raw_content.startswith("["):
                decoded = raw_content
            else:
                decoded = self._dec_val(raw_content)
            parsed = json.loads(decoded) if decoded else None
            return parsed if isinstance(parsed, dict) else None
        except (TypeError, ValueError):
            logger.warning("campaign_graph_snapshot_invalid", campaign_id=campaign_id[:8])
            return None

    async def campaign_summary(self, campaign_id: str) -> dict[str, Any]:
        findings = await self.get_findings(campaign_id)
        hosts = await self.get_hosts(campaign_id)
        creds = await self.get_credentials(campaign_id)
        loot = await self.get_loot(campaign_id)
        return {
            "campaign_id": campaign_id,
            "findings": findings,
            "finding_count": len(findings),
            "host_count": len(hosts),
            "credential_count": len(creds),
            "loot_count": len(loot),
        }

    # ── Audit ──────────────────────────────────────────────────────────────────

    async def audit(
        self,
        actor: str,
        action: str,
        detail: str = "",
        campaign_id: str | None = None,
        module_id: str | None = None,
    ) -> None:
        async with self._pool.acquire() as conn:
            await conn.execute(
                "INSERT INTO audit_log(campaign_id,actor,action,detail,module_id) VALUES($1,$2,$3,$4,$5)",
                campaign_id,
                actor,
                action,
                detail,
                module_id,
            )

    # ── Users ──────────────────────────────────────────────────────────────────

    async def create_user(
        self, username: str, password: str, role: str, created_by: str = "system"
    ) -> str:
        user_id = str(uuid.uuid4())
        async with self._pool.acquire() as conn:
            await conn.execute(
                "INSERT INTO users(id,username,hashed_password,role,created_by) VALUES($1,$2,$3,$4,$5)",
                user_id,
                username,
                hash_password(password),
                role,
                created_by,
            )
        logger.info("user_created", username=username, role=role)
        return user_id

    async def get_user(self, username: str) -> dict[str, Any] | None:
        async with self._pool.acquire() as conn:
            row = await conn.fetchrow(
                "SELECT * FROM users WHERE username=$1 AND is_active=1", username
            )
        return self._row_to_dict(row) if row else None

    async def get_user_by_id(self, user_id: str) -> dict[str, Any] | None:
        async with self._pool.acquire() as conn:
            row = await conn.fetchrow("SELECT * FROM users WHERE id=$1 AND is_active=1", user_id)
        return self._row_to_dict(row) if row else None

    async def resolve_access_token_principal(
        self,
        subject: str,
        jti: str,
        family_id: str,
        auth_epoch: int,
    ) -> dict[str, Any] | None:
        """Resolve current user eligibility and JTI status in one read snapshot."""
        pool = self._pool
        if pool is None:
            raise RuntimeError("Database not connected")
        is_closing = getattr(pool, "is_closing", None)
        if callable(is_closing) and is_closing():
            raise RuntimeError("Database pool is closing")
        async with pool.acquire() as conn:
            row = await conn.fetchrow(
                """SELECT u.id, u.username, u.role, u.auth_epoch
                   FROM users AS u
                   JOIN refresh_token_families AS f
                     ON f.user_id=u.id AND f.id=$3
                   WHERE u.username=$1
                     AND u.is_active=1
                     AND u.auth_epoch=$4
                     AND f.auth_epoch=u.auth_epoch
                     AND f.state='active'
                     AND f.revoked_at IS NULL
                     AND f.absolute_expires_at > now()
                     AND NOT EXISTS (
                         SELECT 1
                         FROM revoked_access_tokens AS rat
                         WHERE rat.jti=$2
                     )""",
                subject,
                jti,
                family_id,
                auth_epoch,
            )
        return self._row_to_dict(row) if row else None

    async def verify_user(self, username: str, password: str) -> dict[str, Any] | None:
        # Constant-time: ALWAYS run verify_password (even when the user is missing)
        # against a dummy hash so the response latency does not reveal whether the
        # username exists. The SQLite sibling (`database.py:2314-2320`) already does
        # this correctly; the previous short-circuit `not user or ...` here leaked
        # user existence via bcrypt timing. (AUDIT run-1 H2)
        user = await self.get_user(username)
        _DUMMY = "$2b$12$notarealthashXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
        candidate = user["hashed_password"] if user else _DUMMY
        password_ok = verify_password(password, candidate)
        if not user or not password_ok:
            return None
        async with self._pool.acquire() as conn:
            await conn.execute("UPDATE users SET last_login=now() WHERE id=$1", user["id"])
        return user

    async def user_exists(self, username: str) -> bool:
        async with self._pool.acquire() as conn:
            return bool(await conn.fetchval("SELECT 1 FROM users WHERE username=$1", username))

    async def update_password(self, user_id: str, new_hash: str) -> None:
        async with self._pool.acquire() as conn:
            await conn.execute("UPDATE users SET hashed_password=$1 WHERE id=$2", new_hash, user_id)

    async def list_users(self) -> list[dict[str, Any]]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT id,username,role,is_active,created_at,last_login FROM users ORDER BY created_at"
            )
        return [self._row_to_dict(r) for r in rows]

    async def ensure_default_admin(self, admin_password: str) -> bool:
        async with self._pool.acquire() as conn:
            n = await conn.fetchval("SELECT COUNT(*) FROM users")
        if n == 0:
            await self.create_user("admin", admin_password, "team_lead", "bootstrap")
            logger.warning(
                "default_admin_created",
                msg="CHANGE password immediately: POST /auth/change-password",
            )
            return True
        return False

    # ── Multi-Tenant & Enterprise SSO ─────────────────────────────────────────

    @staticmethod
    async def _ensure_sso_schema(connection: Any) -> None:
        """Ensure enterprise SSO and multi-tenant schema exists in PostgreSQL."""
        await connection.execute(
            """
            CREATE TABLE IF NOT EXISTS organizations (
                id          TEXT PRIMARY KEY,
                slug        TEXT NOT NULL UNIQUE,
                name        TEXT NOT NULL,
                is_active   INTEGER NOT NULL DEFAULT 1,
                created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            CREATE INDEX IF NOT EXISTS idx_organizations_slug ON organizations (slug);

            CREATE TABLE IF NOT EXISTS sso_configurations (
                id                  TEXT PRIMARY KEY,
                org_id              TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
                protocol            TEXT NOT NULL,
                is_enabled          INTEGER NOT NULL DEFAULT 1,
                issuer_or_entity_id TEXT NOT NULL DEFAULT '',
                sso_url             TEXT NOT NULL DEFAULT '',
                idp_certificate_enc TEXT DEFAULT '',
                sp_entity_id        TEXT DEFAULT '',
                acs_url             TEXT DEFAULT '',
                client_id           TEXT DEFAULT '',
                client_secret_enc   TEXT DEFAULT '',
                jwks_uri            TEXT DEFAULT '',
                default_role        TEXT NOT NULL DEFAULT 'reporter',
                role_mapping_json   TEXT NOT NULL DEFAULT '{}',
                created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                CONSTRAINT ck_sso_protocol CHECK (protocol IN ('saml', 'oidc')),
                CONSTRAINT uq_sso_org_protocol UNIQUE (org_id, protocol)
            );
            CREATE INDEX IF NOT EXISTS idx_sso_org ON sso_configurations (org_id);

            CREATE TABLE IF NOT EXISTS sso_flow_states (
                id          TEXT PRIMARY KEY,
                org_id      TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
                flow_type   TEXT NOT NULL,
                flow_id     TEXT NOT NULL UNIQUE,
                nonce       TEXT,
                created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                expires_at  TIMESTAMPTZ NOT NULL,
                is_consumed INTEGER NOT NULL DEFAULT 0,
                consumed_at TIMESTAMPTZ,
                CONSTRAINT ck_flow_type CHECK (flow_type IN ('saml', 'oidc'))
            );
            CREATE INDEX IF NOT EXISTS idx_sso_flow_lookup
                ON sso_flow_states (flow_id, is_consumed, expires_at);

            ALTER TABLE users ADD COLUMN IF NOT EXISTS org_id TEXT;
            ALTER TABLE users ADD COLUMN IF NOT EXISTS auth_provider TEXT NOT NULL DEFAULT 'local';
            ALTER TABLE users ADD COLUMN IF NOT EXISTS external_subject_id TEXT DEFAULT NULL;

            INSERT INTO organizations (id, slug, name, is_active)
            VALUES ('00000000-0000-0000-0000-000000000001', 'default', 'Default Organization', 1)
            ON CONFLICT (slug) DO NOTHING;
            """
        )

    async def _ensure_sso_ready(self) -> None:
        """Lazily ensure enterprise SSO schema exists on first SSO operation."""
        if getattr(self, "_sso_schema_ensured", False):
            return
        if self._pool is None:
            return
        try:
            async with self._pool.acquire() as conn:
                await self._ensure_sso_schema(conn)
            self._sso_schema_ensured = True
        except Exception as exc:
            logger.warning("pg_sso_schema_init_failed", error=str(exc))

    async def get_organization(self, slug_or_id: str) -> dict[str, Any] | None:
        await self._ensure_sso_ready()
        async with self._pool.acquire() as conn:
            row = await conn.fetchrow(
                "SELECT * FROM organizations WHERE slug=$1 OR id=$1",
                slug_or_id,
            )
            if row:
                return self._row_to_dict(row)
            if slug_or_id == "default":
                default_id = "00000000-0000-0000-0000-000000000001"
                await conn.execute(
                    """INSERT INTO organizations(id, slug, name, is_active)
                       VALUES($1, $2, $3, 1)
                       ON CONFLICT (slug) DO NOTHING""",
                    default_id,
                    "default",
                    "Default Organization",
                )
                row = await conn.fetchrow("SELECT * FROM organizations WHERE slug='default'")
                return self._row_to_dict(row) if row else None
        return None

    async def get_sso_config(
        self, org_id: str, protocol: str | None = None
    ) -> dict[str, Any] | None:
        await self._ensure_sso_ready()
        async with self._pool.acquire() as conn:
            if protocol:
                row = await conn.fetchrow(
                    "SELECT * FROM sso_configurations "
                    "WHERE org_id=$1 AND protocol=$2 AND is_enabled=1",
                    org_id,
                    protocol,
                )
            else:
                row = await conn.fetchrow(
                    "SELECT * FROM sso_configurations WHERE org_id=$1 AND is_enabled=1 LIMIT 1",
                    org_id,
                )
        return self._row_to_dict(row) if row else None

    async def save_sso_config(
        self,
        org_id: str,
        protocol: str,
        issuer_or_entity_id: str = "",
        sso_url: str = "",
        idp_certificate_enc: str = "",
        sp_entity_id: str = "",
        acs_url: str = "",
        client_id: str = "",
        client_secret_enc: str = "",
        jwks_uri: str = "",
        default_role: str = "reporter",
        role_mapping_json: str = "{}",
        is_enabled: bool = True,
    ) -> str:
        await self._ensure_sso_ready()
        sso_id = str(uuid.uuid4())
        async with self._pool.acquire() as conn:
            await conn.execute(
                """INSERT INTO sso_configurations(
                    id, org_id, protocol, is_enabled,
                    issuer_or_entity_id, sso_url, idp_certificate_enc,
                    sp_entity_id, acs_url, client_id, client_secret_enc,
                    jwks_uri, default_role, role_mapping_json, updated_at
                ) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, now())
                ON CONFLICT (org_id, protocol) DO UPDATE SET
                    is_enabled=EXCLUDED.is_enabled,
                    issuer_or_entity_id=EXCLUDED.issuer_or_entity_id,
                    sso_url=EXCLUDED.sso_url,
                    idp_certificate_enc=EXCLUDED.idp_certificate_enc,
                    sp_entity_id=EXCLUDED.sp_entity_id,
                    acs_url=EXCLUDED.acs_url,
                    client_id=EXCLUDED.client_id,
                    client_secret_enc=EXCLUDED.client_secret_enc,
                    jwks_uri=EXCLUDED.jwks_uri,
                    default_role=EXCLUDED.default_role,
                    role_mapping_json=EXCLUDED.role_mapping_json,
                    updated_at=now()""",
                sso_id,
                org_id,
                protocol,
                1 if is_enabled else 0,
                issuer_or_entity_id,
                sso_url,
                idp_certificate_enc,
                sp_entity_id,
                acs_url,
                client_id,
                client_secret_enc,
                jwks_uri,
                default_role,
                role_mapping_json,
            )
        return sso_id

    async def create_sso_flow_state(
        self,
        org_id: str,
        flow_type: str,
        flow_id: str,
        nonce: str | None = None,
        ttl_minutes: int = 10,
    ) -> str:
        await self._ensure_sso_ready()
        state_id = str(uuid.uuid4())
        async with self._pool.acquire() as conn:
            await conn.execute(
                """INSERT INTO sso_flow_states(
                    id, org_id, flow_type, flow_id, nonce, created_at, expires_at
                ) VALUES($1, $2, $3, $4, $5, now(), now() + ($6 || ' minutes')::interval)""",
                state_id,
                org_id,
                flow_type,
                flow_id,
                nonce,
                str(int(ttl_minutes)),
            )
        return state_id

    async def consume_sso_flow_state(
        self,
        flow_id: str,
        flow_type: str,
    ) -> dict[str, Any] | None:
        await self._ensure_sso_ready()
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    """SELECT * FROM sso_flow_states
                       WHERE flow_id=$1 AND flow_type=$2 AND is_consumed=0 AND expires_at > now()
                       FOR UPDATE""",
                    flow_id,
                    flow_type,
                )
                if not row:
                    return None
                record = self._row_to_dict(row)
                await conn.execute(
                    "UPDATE sso_flow_states SET is_consumed=1, consumed_at=now() WHERE id=$1",
                    record["id"],
                )
                return record

    async def provision_or_get_sso_user(
        self,
        org_id: str,
        username: str,
        role: str = "reporter",
        external_id: str = "",
        auth_provider: str = "sso",
    ) -> dict[str, Any]:
        """JIT provision or look up an SSO user adhering to the canonical schema."""
        await self._ensure_sso_ready()
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = None
                if external_id:
                    row = await conn.fetchrow(
                        """SELECT * FROM users
                           WHERE external_subject_id=$1
                             AND external_subject_id IS NOT NULL
                             AND external_subject_id != ''
                             AND auth_provider=$2
                           FOR UPDATE""",
                        external_id,
                        auth_provider,
                    )

                username_row = await conn.fetchrow(
                    "SELECT * FROM users WHERE username=$1 FOR UPDATE",
                    username,
                )

                if username_row:
                    existing_by_username = self._row_to_dict(username_row)
                    if row and row["id"] == existing_by_username["id"]:
                        pass
                    else:
                        existing_provider = existing_by_username.get("auth_provider")
                        if existing_provider == "local":
                            raise ValueError(
                                f"SSO identity conflict: username '{username}' collides with an existing local account"
                            )
                        if existing_provider and existing_provider != auth_provider:
                            raise ValueError(
                                f"SSO identity conflict: username '{username}' is already registered with provider '{existing_provider}'"
                            )
                        existing_org = existing_by_username.get("org_id")
                        if existing_org and existing_org != org_id:
                            raise ValueError(
                                f"SSO identity conflict: username '{username}' is registered under a different organization"
                            )
                        if not row:
                            row = username_row

                if row:
                    user = self._row_to_dict(row)
                    updates = []
                    params: list[Any] = []
                    idx = 1
                    if external_id and not user.get("external_subject_id"):
                        updates.append(f"external_subject_id=${idx}")
                        params.append(external_id)
                        idx += 1
                    if role and role != user.get("role"):
                        updates.append(f"role=${idx}")
                        params.append(role)
                        idx += 1
                    if updates:
                        params.append(user["id"])
                        await conn.execute(
                            f"UPDATE users SET {', '.join(updates)} WHERE id=${idx}",  # noqa: S608
                            *params,
                        )
                        updated_row = await conn.fetchrow(
                            "SELECT * FROM users WHERE id=$1", user["id"]
                        )
                        user = self._row_to_dict(updated_row) if updated_row else user
                    return user

                user_id = str(uuid.uuid4())
                dummy_hash = hash_password(secrets.token_urlsafe(32))
                await conn.execute(
                    """INSERT INTO users(
                        id, username, hashed_password, role, is_active,
                        created_by, org_id, auth_provider, external_subject_id
                    ) VALUES($1, $2, $3, $4, 1, $5, $6, $7, $8)""",
                    user_id,
                    username,
                    dummy_hash,
                    role,
                    f"sso:{auth_provider}",
                    org_id,
                    auth_provider,
                    external_id,
                )
                logger.info(
                    "sso_user_provisioned",
                    username=username,
                    role=role,
                    org_id=org_id,
                    provider=auth_provider,
                )
                new_row = await conn.fetchrow("SELECT * FROM users WHERE id=$1", user_id)
                return self._row_to_dict(new_row)

    async def create_sso_session(
        self,
        user_id: str,
        token_factory: AccessTokenFactory,
    ) -> SessionIssueResult:
        """Create authenticated session for an SSO-authenticated user."""
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT id,username,role,is_active,auth_epoch "
                    "FROM users WHERE id=$1 FOR UPDATE",
                    user_id,
                )
                if row is None or not bool(row["is_active"]):
                    return SessionIssueResult(SessionIssueStatus.INVALID)
                (
                    family_id,
                    raw_token,
                    absolute_expiry,
                ) = await self._insert_initial_family_token_with_expiry(
                    conn,
                    user_id=row["id"],
                    auth_epoch=int(row["auth_epoch"]),
                )
                access_token = token_factory(
                    {
                        "sub": row["username"],
                        "sid": family_id,
                        "ver": int(row["auth_epoch"]),
                    }
                )
                await conn.execute(
                    "UPDATE users SET last_login=now() WHERE id=$1",
                    row["id"],
                )
                await conn.execute(
                    "INSERT INTO audit_log(actor,action,detail) VALUES("
                    "'auth-system','sso_login_family_created','')"
                )
        return SessionIssueResult(
            SessionIssueStatus.ISSUED,
            IssuedTokenSession(
                access_token=access_token,
                refresh_token=raw_token,
                user_id=row["id"],
                subject=row["username"],
                family_id=family_id,
                auth_epoch=int(row["auth_epoch"]),
                absolute_expires_at=absolute_expiry,
                refresh_generation=0,
                role=row["role"],
            ),
        )

    # ── API Keys ───────────────────────────────────────────────────────────────

    async def create_api_key(
        self, user_id: str, name: str, scopes: str = "read", expires_days: int | None = None
    ) -> tuple[str, str]:
        raw_key = "ares_" + secrets.token_urlsafe(40)
        key_prefix = raw_key[:12]
        key_id = str(uuid.uuid4())
        expires_at = (
            datetime.now(UTC) + timedelta(days=expires_days) if expires_days else None
        )
        async with self._pool.acquire() as conn:
            await conn.execute(
                "INSERT INTO api_keys(id,user_id,name,key_hash,key_prefix,scopes,expires_at) "
                "VALUES($1,$2,$3,$4,$5,$6,$7)",
                key_id,
                user_id,
                name,
                hash_password(raw_key),
                key_prefix,
                scopes,
                expires_at,
            )
        return key_id, raw_key

    async def verify_api_key(self, raw_key: str) -> dict[str, Any] | None:
        if not raw_key.startswith("ares_"):
            return None
        prefix = raw_key[:12]
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                """SELECT ak.id, ak.key_hash
                   FROM api_keys ak JOIN users u ON ak.user_id=u.id
                   WHERE ak.key_prefix=$1 AND ak.is_active=1
                   AND u.is_active=1
                   AND (ak.expires_at IS NULL OR ak.expires_at > now())""",
                prefix,
            )
        for row in rows:
            d = self._row_to_dict(row)
            if verify_password(raw_key, d["key_hash"]):
                async with self._pool.acquire() as conn:
                    updated = await conn.fetchrow(
                        """UPDATE api_keys AS ak
                           SET last_used=now()
                           FROM users AS u
                           WHERE ak.id=$1
                             AND ak.is_active=1
                             AND (ak.expires_at IS NULL OR ak.expires_at > now())
                             AND u.id=ak.user_id
                             AND u.is_active=1
                           RETURNING ak.id, ak.scopes, u.username, u.role""",
                        d["id"],
                    )
                if updated is None:
                    return None
                return {
                    "username": updated["username"],
                    "role": updated["role"],
                    "auth_type": "api_key",
                    "key_id": updated["id"],
                    "scopes": [updated["scopes"]] if updated["scopes"] else [],
                }
        return None

    async def list_api_keys(self, user_id: str) -> list[dict]:
        async with self._pool.acquire() as conn:
            rows = await conn.fetch(
                "SELECT id,name,key_prefix,scopes,is_active,last_used,expires_at,created_at "
                "FROM api_keys WHERE user_id=$1 AND is_active=1 ORDER BY created_at DESC",
                user_id,
            )
        return [self._row_to_dict(r) for r in rows]

    async def revoke_api_key(self, key_id: str, user_id: str) -> bool:
        async with self._pool.acquire() as conn:
            await conn.execute(
                "UPDATE api_keys SET is_active=0 WHERE id=$1 AND user_id=$2", key_id, user_id
            )
        return True

    # ── Refresh Tokens ─────────────────────────────────────────────────────────

    # ── One-time WebSocket tickets ───────────────────────────────────────────

    def _require_websocket_ticket_pool(self) -> Any:
        if self._pool is None:
            raise RuntimeError("PostgreSQL database is not connected")
        return self._pool

    @staticmethod
    async def _purge_websocket_ticket_rows(connection: Any) -> None:
        await connection.execute("DELETE FROM websocket_tickets WHERE expires_at <= now()")

    async def issue_websocket_ticket(
        self,
        campaign_id: str,
        source: BearerTicketSource | ApiKeyTicketSource,
    ) -> tuple[str, int] | None:
        raw_ticket = generate_websocket_ticket()
        ticket_hash = hash_websocket_ticket(raw_ticket)
        pool = self._require_websocket_ticket_pool()
        issued = False

        async with pool.acquire() as connection:
            async with connection.transaction():
                await self._purge_websocket_ticket_rows(connection)
                if isinstance(source, BearerTicketSource):
                    row = await connection.fetchrow(
                        """
                        INSERT INTO websocket_tickets(
                            ticket_hash, campaign_id, user_id,
                            credential_kind, bearer_subject, bearer_jti,
                            bearer_expires_at, bearer_family_id,
                            bearer_auth_epoch, created_at, expires_at
                        )
                        SELECT $1, c.id, u.id, 'bearer', $4, $5, $6, $7, $8,
                               now(), now() + interval '30 seconds'
                        FROM users AS u
                        JOIN refresh_token_families AS f
                          ON f.id=$7 AND f.user_id=u.id
                        JOIN campaigns AS c ON c.id=$2
                        WHERE u.id=$3
                          AND u.username=$4
                          AND u.is_active=1
                          AND u.auth_epoch=$8
                          AND f.auth_epoch=u.auth_epoch
                          AND f.state='active'
                          AND f.revoked_at IS NULL
                          AND f.absolute_expires_at > now()
                          AND u.role IN ('team_lead','operator','recon','reporter')
                          AND $6 > now()
                          AND NOT EXISTS (
                              SELECT 1 FROM revoked_access_tokens AS rat
                              WHERE rat.jti=$5
                          )
                          AND (
                              u.role='team_lead'
                              OR c.operator=u.username
                          )
                        RETURNING ticket_hash
                        """,
                        ticket_hash,
                        campaign_id,
                        source.user_id,
                        source.subject,
                        source.jti,
                        source.expires_at,
                        source.family_id,
                        source.auth_epoch,
                    )
                else:
                    row = await connection.fetchrow(
                        """
                        INSERT INTO websocket_tickets(
                            ticket_hash, campaign_id, user_id,
                            credential_kind, api_key_id, required_scope,
                            created_at, expires_at
                        )
                        SELECT $1, c.id, u.id, 'api_key', ak.id, 'read',
                               now(), now() + interval '30 seconds'
                        FROM api_keys AS ak
                        JOIN users AS u ON u.id=ak.user_id
                        JOIN campaigns AS c ON c.id=$2
                        WHERE ak.id=$4
                          AND ak.user_id=$3
                          AND ak.is_active=1
                          AND (ak.expires_at IS NULL OR ak.expires_at > now())
                          AND u.is_active=1
                          AND u.role IN ('team_lead','operator','recon','reporter')
                          AND (
                              regexp_split_to_array(
                                  btrim(COALESCE(ak.scopes, '')),
                                  '[[:space:],]+'
                              ) && ARRAY['read','write','admin']
                          )
                          AND (
                              u.role='team_lead'
                              OR c.operator=u.username
                          )
                        RETURNING ticket_hash
                        """,
                        ticket_hash,
                        campaign_id,
                        source.user_id,
                        source.api_key_id,
                    )
                issued = row is not None

        if not issued:
            return None
        return raw_ticket, WEBSOCKET_TICKET_TTL_SECONDS

    async def consume_websocket_ticket(
        self,
        raw_ticket: str,
        campaign_id: str,
    ) -> ConsumedWebSocketTicket | None:
        if not is_canonical_websocket_ticket(raw_ticket):
            return None

        ticket_hash = hash_websocket_ticket(raw_ticket)
        pool = self._require_websocket_ticket_pool()
        consumed: ConsumedWebSocketTicket | None = None

        async with pool.acquire() as connection:
            async with connection.transaction():
                row = await connection.fetchrow(
                    """
                    UPDATE websocket_tickets
                    SET consumed_at=now()
                    WHERE ticket_hash=$1
                      AND campaign_id=$2
                      AND consumed_at IS NULL
                      AND expires_at > now()
                      AND (
                          credential_kind='api_key'
                          OR EXISTS (
                              SELECT 1
                              FROM users AS u
                              JOIN refresh_token_families AS f
                                ON f.id=websocket_tickets.bearer_family_id
                               AND f.user_id=u.id
                              WHERE u.id=websocket_tickets.user_id
                                AND u.username=websocket_tickets.bearer_subject
                                AND u.is_active=1
                                AND u.auth_epoch=
                                    websocket_tickets.bearer_auth_epoch
                                AND f.auth_epoch=u.auth_epoch
                                AND f.state='active'
                                AND f.revoked_at IS NULL
                                AND f.absolute_expires_at > now()
                                AND websocket_tickets.bearer_expires_at > now()
                                AND NOT EXISTS (
                                    SELECT 1
                                    FROM revoked_access_tokens AS rat
                                    WHERE rat.jti=websocket_tickets.bearer_jti
                                )
                          )
                      )
                    RETURNING campaign_id, user_id, credential_kind,
                              bearer_subject, bearer_jti,
                              bearer_expires_at, bearer_family_id,
                              bearer_auth_epoch, api_key_id,
                              required_scope
                    """,
                    ticket_hash,
                    campaign_id,
                )
                if row is not None:
                    consumed = ConsumedWebSocketTicket(
                        campaign_id=row["campaign_id"],
                        user_id=row["user_id"],
                        credential_kind=WebSocketTicketCredentialKind(row["credential_kind"]),
                        bearer_subject=row["bearer_subject"],
                        bearer_jti=row["bearer_jti"],
                        bearer_expires_at=row["bearer_expires_at"],
                        bearer_family_id=row["bearer_family_id"],
                        bearer_auth_epoch=row["bearer_auth_epoch"],
                        api_key_id=row["api_key_id"],
                        required_scope=row["required_scope"],
                    )

        return consumed

    async def resolve_websocket_ticket_principal(
        self,
        consumed: ConsumedWebSocketTicket,
    ) -> WebSocketTicketPrincipal | None:
        pool = self._require_websocket_ticket_pool()
        async with pool.acquire() as connection:
            if consumed.credential_kind is WebSocketTicketCredentialKind.BEARER:
                row = await connection.fetchrow(
                    """
                    SELECT u.id, u.username, u.role
                    FROM users AS u
                    JOIN refresh_token_families AS f
                      ON f.id=$5 AND f.user_id=u.id
                    JOIN campaigns AS c ON c.id=$1
                    WHERE u.id=$2
                      AND u.username=$3
                      AND u.is_active=1
                      AND u.auth_epoch=$6
                      AND f.auth_epoch=u.auth_epoch
                      AND f.state='active'
                      AND f.revoked_at IS NULL
                      AND f.absolute_expires_at > now()
                      AND u.role IN ('team_lead','operator','recon','reporter')
                      AND NOT EXISTS (
                          SELECT 1 FROM revoked_access_tokens AS rat
                          WHERE rat.jti=$4
                      )
                      AND (
                          u.role='team_lead'
                          OR c.operator=u.username
                      )
                    """,
                    consumed.campaign_id,
                    consumed.user_id,
                    consumed.bearer_subject,
                    consumed.bearer_jti,
                    consumed.bearer_family_id,
                    consumed.bearer_auth_epoch,
                )
            else:
                row = await connection.fetchrow(
                    """
                    SELECT u.id, u.username, u.role, ak.id AS api_key_id,
                           ak.scopes
                    FROM api_keys AS ak
                    JOIN users AS u ON u.id=ak.user_id
                    JOIN campaigns AS c ON c.id=$1
                    WHERE ak.id=$3
                      AND ak.user_id=$2
                      AND ak.is_active=1
                      AND (ak.expires_at IS NULL OR ak.expires_at > now())
                      AND u.is_active=1
                      AND u.role IN ('team_lead','operator','recon','reporter')
                      AND (
                          regexp_split_to_array(
                              btrim(COALESCE(ak.scopes, '')),
                              '[[:space:],]+'
                          ) && ARRAY['read','write','admin']
                      )
                      AND (
                          u.role='team_lead'
                          OR c.operator=u.username
                      )
                    """,
                    consumed.campaign_id,
                    consumed.user_id,
                    consumed.api_key_id,
                )

        if row is None:
            return None
        if not is_valid_websocket_principal_role(row["role"]):
            return None
        if consumed.credential_kind is WebSocketTicketCredentialKind.BEARER:
            return WebSocketTicketPrincipal(
                user_id=row["id"],
                username=row["username"],
                role=row["role"],
                credential_kind=WebSocketTicketCredentialKind.BEARER,
            )
        scopes = normalize_api_key_scopes(row["scopes"])
        if not set(scopes).intersection({"read", "write", "admin"}):
            return None
        return WebSocketTicketPrincipal(
            user_id=row["id"],
            username=row["username"],
            role=row["role"],
            credential_kind=WebSocketTicketCredentialKind.API_KEY,
            api_key_id=row["api_key_id"],
            api_key_scopes=scopes,
        )

    async def purge_expired_websocket_tickets(self) -> int:
        pool = self._require_websocket_ticket_pool()
        async with pool.acquire() as connection:
            async with connection.transaction():
                result = await connection.execute(
                    "DELETE FROM websocket_tickets WHERE expires_at <= now()"
                )
        try:
            return int(result.split()[-1])
        except (ValueError, IndexError):
            return 0

    # ── Refresh tokens ───────────────────────────────────────────────────────

    @staticmethod
    async def _insert_initial_family_token_with_expiry(
        conn: Any,
        *,
        user_id: str,
        auth_epoch: int,
    ) -> tuple[str, str, datetime]:
        family_id = generate_family_id()
        raw_token = generate_refresh_token()
        token_hash = hash_refresh_token(raw_token)
        now = datetime.now(UTC)
        absolute_expiry = now + timedelta(days=FAMILY_ABSOLUTE_LIFETIME_DAYS)
        retain_until = absolute_expiry + timedelta(days=FAMILY_RETENTION_DAYS)
        await conn.execute(
            "INSERT INTO refresh_token_families("
            "id,user_id,auth_epoch,state,created_at,absolute_expires_at,"
            "retain_until) VALUES($1,$2,$3,'active',$4,$5,$6)",
            family_id,
            user_id,
            auth_epoch,
            now,
            absolute_expiry,
            retain_until,
        )
        await conn.execute(
            "INSERT INTO refresh_tokens("
            "id,user_id,is_revoked,expires_at,created_at,family_id,parent_id,"
            "generation,state) VALUES($1,$2,0,$3,$4,$5,NULL,0,'active')",
            token_hash,
            user_id,
            absolute_expiry,
            now,
            family_id,
        )
        return family_id, raw_token, absolute_expiry

    @staticmethod
    async def _insert_initial_family_token(
        conn: Any,
        *,
        user_id: str,
        auth_epoch: int,
    ) -> tuple[str, str]:
        family_id, raw_token, _ = await PostgresDatabase._insert_initial_family_token_with_expiry(
            conn,
            user_id=user_id,
            auth_epoch=auth_epoch,
        )
        return family_id, raw_token

    async def create_refresh_token(
        self,
        user_id: str,
        expires_days: int = FAMILY_ABSOLUTE_LIFETIME_DAYS,
    ) -> str:
        if expires_days != FAMILY_ABSOLUTE_LIFETIME_DAYS:
            raise ValueError("refresh lifetime is fixed")
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT auth_epoch FROM users WHERE id=$1 AND is_active=1 FOR UPDATE",
                    user_id,
                )
                if row is None:
                    raise ValueError("invalid refresh owner")
                _, raw_token = await self._insert_initial_family_token(
                    conn,
                    user_id=user_id,
                    auth_epoch=int(row["auth_epoch"]),
                )
        return raw_token

    async def create_login_session(
        self,
        username: str,
        password: str,
        token_factory: AccessTokenFactory,
    ) -> SessionIssueResult:
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT id,username,hashed_password,role,is_active,auth_epoch "
                    "FROM users WHERE username=$1 FOR UPDATE",
                    username,
                )
                if (
                    row is None
                    or not bool(row["is_active"])
                    or not verify_password(
                        password,
                        row["hashed_password"],
                    )
                ):
                    return SessionIssueResult(SessionIssueStatus.INVALID)
                (
                    family_id,
                    raw_token,
                    absolute_expiry,
                ) = await self._insert_initial_family_token_with_expiry(
                    conn,
                    user_id=row["id"],
                    auth_epoch=int(row["auth_epoch"]),
                )
                access_token = token_factory(
                    {
                        "sub": row["username"],
                        "sid": family_id,
                        "ver": int(row["auth_epoch"]),
                    }
                )
                await conn.execute(
                    "UPDATE users SET last_login=now() WHERE id=$1",
                    row["id"],
                )
                await conn.execute(
                    "INSERT INTO audit_log(actor,action,detail) VALUES("
                    "'auth-system','login_family_created','')"
                )
        return SessionIssueResult(
            SessionIssueStatus.ISSUED,
            IssuedTokenSession(
                access_token=access_token,
                refresh_token=raw_token,
                user_id=row["id"],
                subject=row["username"],
                family_id=family_id,
                auth_epoch=int(row["auth_epoch"]),
                absolute_expires_at=absolute_expiry,
                refresh_generation=0,
                role=row["role"],
            ),
        )

    @staticmethod
    async def _revoke_family_rows(
        conn: Any,
        *,
        family_id: str,
        reason: str,
    ) -> bool:
        family = await conn.fetchrow(
            "SELECT absolute_expires_at,state FROM refresh_token_families WHERE id=$1 FOR UPDATE",
            family_id,
        )
        if family is None:
            return False
        if family["state"] == "active":
            now = datetime.now(UTC)
            retain_until = max(family["absolute_expires_at"], now) + timedelta(
                days=FAMILY_RETENTION_DAYS
            )
            await conn.execute(
                "UPDATE refresh_token_families SET state='revoked',revoked_at=$2,"
                "revoke_reason=$3,retain_until=$4 WHERE id=$1 AND state='active'",
                family_id,
                now,
                reason,
                retain_until,
            )
            await conn.execute(
                "UPDATE refresh_tokens SET state='retired',is_revoked=1,"
                "revoked_at=$2 WHERE family_id=$1 AND state='active'",
                family_id,
                now,
            )
            return True
        return False

    async def rotate_refresh_session(
        self,
        old_token: str,
        token_factory: AccessTokenFactory,
    ) -> RefreshRotationResult:
        old_hash = hash_refresh_token(old_token)
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                token = await conn.fetchrow(
                    "SELECT family_id FROM refresh_tokens WHERE id=$1",
                    old_hash,
                )
                if token is None:
                    return RefreshRotationResult(RefreshRotationStatus.INVALID)
                row = await conn.fetchrow(
                    "SELECT rt.user_id,rt.family_id,rt.generation,rt.state,"
                    "rt.expires_at,f.auth_epoch,f.state AS family_state,"
                    "f.absolute_expires_at,u.auth_epoch AS user_epoch,"
                    "u.username,u.role,u.is_active "
                    "FROM refresh_token_families AS f "
                    "JOIN refresh_tokens AS rt ON rt.family_id=f.id AND rt.id=$1 "
                    "JOIN users AS u ON u.id=f.user_id "
                    "WHERE f.id=$2 FOR UPDATE OF f,u,rt",
                    old_hash,
                    token["family_id"],
                )
                if row is None:
                    return RefreshRotationResult(RefreshRotationStatus.INVALID)
                if row["state"] != "active":
                    await self._revoke_family_rows(
                        conn,
                        family_id=row["family_id"],
                        reason="replay",
                    )
                    await conn.execute(
                        "INSERT INTO audit_log(actor,action,detail) VALUES("
                        "'auth-system','refresh_replay','')"
                    )
                    return RefreshRotationResult(RefreshRotationStatus.REPLAYED)
                if (
                    row["family_state"] != "active"
                    or not bool(row["is_active"])
                    or int(row["auth_epoch"]) != int(row["user_epoch"])
                    or row["expires_at"] <= datetime.now(UTC)
                    or row["absolute_expires_at"] <= datetime.now(UTC)
                ):
                    return RefreshRotationResult(RefreshRotationStatus.INVALID)
                updated = await conn.execute(
                    "UPDATE refresh_tokens SET state='consumed',is_revoked=1,"
                    "used_at=now() WHERE id=$1 AND state='active' AND is_revoked=0",
                    old_hash,
                )
                if updated != "UPDATE 1":
                    await self._revoke_family_rows(
                        conn,
                        family_id=row["family_id"],
                        reason="replay",
                    )
                    return RefreshRotationResult(RefreshRotationStatus.REPLAYED)
                raw_child = generate_refresh_token()
                child_hash = hash_refresh_token(raw_child)
                generation = int(row["generation"]) + 1
                await conn.execute(
                    "INSERT INTO refresh_tokens("
                    "id,user_id,is_revoked,expires_at,created_at,family_id,parent_id,"
                    "generation,state) VALUES($1,$2,0,$3,now(),$4,$5,$6,'active')",
                    child_hash,
                    row["user_id"],
                    row["absolute_expires_at"],
                    row["family_id"],
                    old_hash,
                    generation,
                )
                access_token = token_factory(
                    {
                        "sub": row["username"],
                        "sid": row["family_id"],
                        "ver": int(row["auth_epoch"]),
                    }
                )
                await conn.execute(
                    "INSERT INTO audit_log(actor,action,detail) VALUES("
                    "'auth-system','refresh_rotated','')"
                )
        return RefreshRotationResult(
            RefreshRotationStatus.ROTATED,
            IssuedTokenSession(
                access_token=access_token,
                refresh_token=raw_child,
                user_id=row["user_id"],
                subject=row["username"],
                family_id=row["family_id"],
                auth_epoch=int(row["auth_epoch"]),
                absolute_expires_at=row["absolute_expires_at"],
                refresh_generation=generation,
                role=row["role"],
            ),
        )

    async def rotate_refresh_token(
        self,
        old_token: str,
    ) -> tuple[dict[str, Any] | None, str | None]:
        result = await self.rotate_refresh_session(old_token, lambda _claims: "compat")
        if result.session is None:
            return None, None
        session = result.session
        return {
            "id": session.user_id,
            "username": session.subject,
            "role": session.role,
            "family_id": session.family_id,
            "auth_epoch": session.auth_epoch,
        }, session.refresh_token

    async def revoke_access_token(
        self,
        jti: str,
        user_id: str,
        expires_at: str | datetime,
    ) -> None:
        parsed_expires_at = (
            expires_at
            if isinstance(expires_at, datetime)
            else _parse_postgres_timestamptz(expires_at)
        )
        async with self._pool.acquire() as conn:
            await conn.execute(
                "INSERT INTO revoked_access_tokens(jti,user_id,expires_at) VALUES($1,$2,$3) "
                "ON CONFLICT(jti) DO NOTHING",
                jti,
                user_id,
                parsed_expires_at,
            )
            await conn.execute("DELETE FROM revoked_access_tokens WHERE expires_at < now()")

    async def is_access_token_revoked(self, jti: str) -> bool:
        async with self._pool.acquire() as conn:
            return bool(
                await conn.fetchval("SELECT 1 FROM revoked_access_tokens WHERE jti=$1", jti)
            )

    async def revoke_all_refresh_tokens(self, user_id: str) -> None:
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                rows = await conn.fetch(
                    "SELECT id FROM refresh_token_families "
                    "WHERE user_id=$1 AND state='active' FOR UPDATE",
                    user_id,
                )
                for row in rows:
                    await self._revoke_family_rows(
                        conn,
                        family_id=row["id"],
                        reason="operator_revoke",
                    )

    async def revoke_current_session(
        self,
        *,
        user_id: str,
        family_id: str,
        jti: str,
        expires_at: datetime,
    ) -> SessionRevocationResult:
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                owner = await conn.fetchval(
                    "SELECT 1 FROM refresh_token_families WHERE id=$1 AND user_id=$2 FOR UPDATE",
                    family_id,
                    user_id,
                )
                if owner is None:
                    return SessionRevocationResult(SessionRevocationStatus.INVALID)
                changed = await self._revoke_family_rows(
                    conn,
                    family_id=family_id,
                    reason="logout_current",
                )
                await conn.execute(
                    "INSERT INTO revoked_access_tokens(jti,user_id,expires_at) "
                    "VALUES($1,$2,$3) ON CONFLICT(jti) DO NOTHING",
                    jti,
                    user_id,
                    expires_at,
                )
                if changed:
                    await conn.execute(
                        "INSERT INTO audit_log(actor,action,detail) VALUES("
                        "'auth-system','logout_current','')"
                    )
        return SessionRevocationResult(
            SessionRevocationStatus.REVOKED if changed else SessionRevocationStatus.ALREADY_REVOKED
        )

    async def revoke_refresh_cookie_session(
        self,
        raw_token: str,
    ) -> SessionRevocationResult:
        """Revoke the family identified only by a browser refresh cookie."""
        token_hash = hash_refresh_token(raw_token)
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT rt.user_id,rt.family_id,rt.state,f.state AS family_state "
                    "FROM refresh_tokens AS rt JOIN refresh_token_families AS f "
                    "ON f.id=rt.family_id AND f.user_id=rt.user_id "
                    "WHERE rt.id=$1 FOR UPDATE OF rt,f",
                    token_hash,
                )
                if row is None:
                    return SessionRevocationResult(SessionRevocationStatus.INVALID)
                if row["family_state"] != "active":
                    return SessionRevocationResult(SessionRevocationStatus.ALREADY_REVOKED)
                reason = "logout_current" if row["state"] == "active" else "replay"
                changed = await self._revoke_family_rows(
                    conn,
                    family_id=row["family_id"],
                    reason=reason,
                )
                if changed:
                    action = (
                        "logout_cookie_family_revoked"
                        if reason == "logout_current"
                        else "logout_cookie_replay_family_revoked"
                    )
                    await conn.execute(
                        "INSERT INTO audit_log(actor,action,detail) VALUES('auth-system',$1,'')",
                        action,
                    )
                    return SessionRevocationResult(SessionRevocationStatus.REVOKED)
                return SessionRevocationResult(SessionRevocationStatus.ALREADY_REVOKED)

    async def revoke_all_sessions(
        self,
        *,
        user_id: str,
        jti: str,
        expires_at: datetime,
    ) -> SessionRevocationResult:
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                updated = await conn.fetchval(
                    "UPDATE users SET auth_epoch=auth_epoch+1 "
                    "WHERE id=$1 AND is_active=1 RETURNING auth_epoch",
                    user_id,
                )
                if updated is None:
                    return SessionRevocationResult(SessionRevocationStatus.INVALID)
                rows = await conn.fetch(
                    "SELECT id FROM refresh_token_families "
                    "WHERE user_id=$1 AND state='active' FOR UPDATE",
                    user_id,
                )
                for row in rows:
                    await self._revoke_family_rows(
                        conn,
                        family_id=row["id"],
                        reason="logout_all",
                    )
                await conn.execute(
                    "INSERT INTO revoked_access_tokens(jti,user_id,expires_at) "
                    "VALUES($1,$2,$3) ON CONFLICT(jti) DO NOTHING",
                    jti,
                    user_id,
                    expires_at,
                )
                await conn.execute(
                    "INSERT INTO audit_log(actor,action,detail) VALUES("
                    "'auth-system','logout_all','')"
                )
        return SessionRevocationResult(SessionRevocationStatus.REVOKED)

    async def apply_user_security_event(
        self,
        *,
        user_id: str,
        reason: str,
        new_password_hash: str | None = None,
        new_role: str | None = None,
        is_active: bool | None = None,
    ) -> bool:
        if reason not in {
            "password_change",
            "password_reset",
            "role_change",
            "user_status_change",
        }:
            raise ValueError("invalid security event")
        async with self._pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT id FROM users WHERE id=$1 FOR UPDATE",
                    user_id,
                )
                if row is None:
                    return False
                await conn.execute(
                    "UPDATE users SET auth_epoch=auth_epoch+1,"
                    "hashed_password=COALESCE($2,hashed_password),"
                    "role=COALESCE($3,role),"
                    "is_active=COALESCE($4,is_active) WHERE id=$1",
                    user_id,
                    new_password_hash,
                    new_role,
                    is_active,
                )
                rows = await conn.fetch(
                    "SELECT id FROM refresh_token_families "
                    "WHERE user_id=$1 AND state='active' FOR UPDATE",
                    user_id,
                )
                for family in rows:
                    await self._revoke_family_rows(
                        conn,
                        family_id=family["id"],
                        reason=reason,
                    )
                await conn.execute(
                    "INSERT INTO audit_log(actor,action,detail) VALUES('auth-system',$1,'')",
                    reason,
                )
        return True

    async def purge_expired_tokens(self) -> int:
        async with self._pool.acquire() as conn:
            result = await conn.execute(
                "DELETE FROM refresh_token_families WHERE retain_until <= now()"
            )
        await self.purge_expired_websocket_tickets()
        # asyncpg returns "DELETE N" as a string
        try:
            return int(result.split()[-1])
        except (ValueError, IndexError):
            return 0

    # ── Bypass outcome tracking (cross-session EDR learning) ──────────────

    async def save_bypass_outcome(
        self,
        technique_id: str,
        edr_vendor: str,
        edr_version: str,
        success: bool,
        campaign_id: str,
        notes: str = "",
    ) -> None:
        """Persist bypass technique outcome for cross-session learning."""
        import time as _time

        try:
            async with self._pool.acquire() as conn:
                await conn.execute(
                    """INSERT INTO bypass_outcomes
                       (technique_id, edr_vendor, edr_version, success, campaign_id, notes, ts)
                       VALUES ($1, $2, $3, $4, $5, $6, $7)""",
                    technique_id,
                    edr_vendor,
                    edr_version,
                    int(success),
                    campaign_id,
                    notes[:500],
                    _time.time(),
                )
        except Exception:
            await self._ensure_bypass_outcomes_table()
            async with self._pool.acquire() as conn:
                await conn.execute(
                    """INSERT INTO bypass_outcomes
                       (technique_id, edr_vendor, edr_version, success, campaign_id, notes, ts)
                       VALUES ($1, $2, $3, $4, $5, $6, $7)""",
                    technique_id,
                    edr_vendor,
                    edr_version,
                    int(success),
                    campaign_id,
                    notes[:500],
                    _time.time(),
                )

    async def get_bypass_success_rate(
        self,
        technique_id: str,
        edr_vendor: str,
        min_samples: int = 3,
    ) -> float | None:
        """Return historical success rate for a bypass technique against an EDR vendor."""
        import time as _time

        try:
            async with self._pool.acquire() as conn:
                row = await conn.fetchrow(
                    """SELECT COUNT(*) as total, COALESCE(SUM(success), 0) as successes
                       FROM bypass_outcomes
                       WHERE technique_id = $1 AND edr_vendor = $2
                       AND ts > $3""",
                    technique_id,
                    edr_vendor,
                    _time.time() - 7_776_000,
                )
            if not row or row["total"] < min_samples:
                return None
            return round(row["successes"] / row["total"], 3)
        except Exception:
            return None

    async def _ensure_bypass_outcomes_table(self) -> None:
        """Create bypass_outcomes table if it doesn't exist."""
        async with self._pool.acquire() as conn:
            await conn.execute(
                """CREATE TABLE IF NOT EXISTS bypass_outcomes (
                   id           SERIAL PRIMARY KEY,
                   technique_id TEXT    NOT NULL,
                   edr_vendor   TEXT    NOT NULL,
                   edr_version  TEXT    DEFAULT '',
                   success      INTEGER NOT NULL,
                   campaign_id  TEXT    DEFAULT '',
                   notes        TEXT    DEFAULT '',
                   ts           DOUBLE PRECISION NOT NULL
                )"""
            )

    async def checkpoint_wal(self) -> None:
        """No-op for PostgreSQL - WAL is managed by the server."""

    async def export_json(self, output_path: str | None = None) -> str:
        """Export all campaigns + findings to JSON (same interface as SQLite backend)."""
        import json as _json
        from pathlib import Path

        ts = datetime.now(UTC).strftime("%Y%m%d_%H%M%S")
        if not output_path:
            backup_dir = Path.home() / ".ares" / "backups"
            backup_dir.mkdir(parents=True, exist_ok=True)
            output_path = str(backup_dir / f"ares_export_{ts}.json")

        async with self._pool.acquire() as conn:
            campaigns = [
                self._row_to_dict(r)
                for r in await conn.fetch("SELECT * FROM campaigns ORDER BY created_at DESC")
            ]
        for c in campaigns:
            cid = c["id"]
            async with self._pool.acquire() as conn:
                c["_findings"] = [
                    self._row_to_dict(r)
                    for r in await conn.fetch(
                        "SELECT * FROM findings WHERE campaign_id=$1 ORDER BY discovered_at DESC",
                        cid,
                    )
                ]
                c["_hosts"] = [
                    self._row_to_dict(r)
                    for r in await conn.fetch(
                        "SELECT * FROM hosts WHERE campaign_id=$1 ORDER BY first_seen", cid
                    )
                ]

        export = {"export_version": "1.0", "exported_at": ts, "campaigns": campaigns}
        with open(output_path, "w") as fh:
            _json.dump(export, fh, indent=2, default=str)
        logger.info("pg_export_complete", path=output_path, campaigns=len(campaigns))
        return output_path