import { 
  users, 
  sessions, 
  threatEvents, 
  auditLogs, 
  systemMetrics, 
  aiThresholds,
  webhookConfigs,
  webhookLogs,
  ghostCoreEvents,
  soarExecutions,
  observerLogs,
  notificationLogs,
  exportJobs,
  apiKeys,
  tenants,
  backupRecords,
  aiThreatScores,
  ssoConfigs,
  i18nOverrides,
  type User, 
  type InsertUser, 
  type Session,
  type ThreatEvent,
  type InsertThreatEvent,
  type AuditLog,
  type InsertAuditLog,
  type SystemMetric,
  type InsertSystemMetric,
  type AIThreshold,
  type WebhookConfig,
  type InsertWebhookConfig,
  type WebhookLog,
  type WebhookEventType,
  type GhostCoreEvent,
  type InsertGhostCoreEvent,
  type SoarExecution,
  type InsertSoarExecution,
  type ObserverLog,
  type InsertObserverLog,
  type NotificationLog,
  type InsertNotificationLog,
  type ExportJob,
  type InsertExportJob,
  type ApiKey,
  type InsertApiKey,
  type Tenant,
  type InsertTenant,
  type BackupRecord,
  type InsertBackupRecord,
  type AiThreatScore,
  type InsertAiThreatScore,
  type SsoConfig,
  type InsertSsoConfig,
  type I18nOverride,
  type InsertI18nOverride,
  uboRegistrations,
  type UboRegistration,
  type UboErasureState,
  type UboErasureTrigger,
  kycCustomers,
  type KycCustomer,
  type KYCStatus,
  type KYCRiskTier,
  kycDecisionOutcomes,
  kycDecisionApprovals,
  type KycDecisionOutcome,
  type KycDecisionApproval,
  kycIdentityAttestations,
  type KycIdentityAttestation,
  kycScreeningResults,
  type InsertKycScreeningResult,
  fincrimeCases,
  fincrimeCaseEvents,
  fincrimeCaseEvidence,
  fincrimeStrReports,
  fincrimeStrApprovals,
  securityIncidents,
  securityIncidentEvents,
  securityIncidentActions,
  uebaUserBaselines,
  uebaAnomalies,
  type UebaUserBaseline,
  type UebaAnomaly,
  auditChainEntries,
  type FincrimeCase,
  type FincrimeCaseEvidence,
  type FincrimeStrReport,
  type FincrimeStrApproval,
  type SecurityIncident,
  type SecurityIncidentEvent,
  type SecurityIncidentAction,
} from "@shared/schema";
import type { ThreatsStats } from "@shared/api-types"; // #52 Tier-1: shared /api/threats/stats contract
import { db, getActiveTenantId, withBypassRls, type DrizzleDb } from "./db";
import { eq, desc, asc, and, gte, lte, isNull, ne } from "drizzle-orm";
import { createHash, randomUUID } from "crypto";
import { logger } from "./lib/logger";
import type { KycCustomerRefRow } from "./lib/kyc/examiner-evidence";
import type { RegulatorCaseRefRow } from "./lib/fincrime/regulator-surface";
import type { FincrimeCaseIntegrityInput } from "./lib/fincrime/case-integrity";
import { encryptPii, decryptPii, computeLookupHash } from "./lib/pii-encryption";
import {
  collectFreshSignals,
  deriveRiskTier,
  decide,
  buildSignalsSnapshot,
  computeSignalsHash,
  coerceRiskTier,
  assertFinalizePreconditions,
  FinalizeGuardError,
  type KycSubjectForDecision,
} from "./lib/kyc/decision-engine";
import {
  assertM2Transition,
  EVIDENCE_ADDED_EVENT,
} from "./lib/fincrime/workflow";
import {
  assertIrTransition,
  slaTargetForSeverity,
  INCIDENT_OPENED_EVENT,
  type IrQueue,
} from "./lib/security-ir/workflow";
import {
  assertM3FinalizeTransition,
  StrGuardError,
  StrWorkflowError,
  STR_FINALIZE_DISPOSITION,
  STR_PREPARED_EVENT,
  STR_APPROVED_EVENT,
} from "./lib/fincrime/str-workflow";
import type { FormBSource } from "./lib/fincrime/goaml-formb";

// ── KYC Decision (Feature 3) storage I/O types ───────────────────────────────
// The decide route computes disposition/state/snapshot/hash in the pure engine
// and hands the persisted fields here. tenantId is the authoritative param (not
// part of the input), mirroring CreateKycCustomerInput.
export interface CreateKycDecisionOutcomeInput {
  customerId: string;
  disposition: string;       // APPROVE | REJECT | ESCALATE_TO_EDD (engine + DB CHECK enforce)
  strFlag: boolean;
  strReason: string | null;
  state: string;             // PROPOSED | PENDING_SENIOR_APPROVAL | FINAL
  requiresSeniorApproval: boolean;
  riskTier: string;          // derived LOW|MEDIUM|HIGH|PEP|SANCTIONS
  signalsSnapshot: string;   // JSON (no raw PII)
  signalsHash: string;
  explanation: string;       // "<ruleId>: <text>"
  decidedByUserId: string;
  decidedByRole: string;
}

export interface RecordApprovalAndFinalizeArgs {
  decisionId: string;
  tenantId: string;
  approverUserId: string;
  approverRole: string;
  reason: string | null;
  correlationId: string;
}

export interface RecordApprovalAndFinalizeResult {
  outcome: KycDecisionOutcome;
  approval: KycDecisionApproval;
  customer: KycCustomer; // decrypted; now APPROVED
}

// T004a — Batch B3: users.full_name treat (i) platform-key
const PLATFORM_CTX = { type: "platform" as const };

// T004a — UBO Registrations PII decrypt helper (storage.ts read paths)
// treat (i) columns: ownerName, ownerNationality
// treat (ii) column: ownerNationalId — decrypt here (HMAC batch landed)
// decryptPii passthrough semantics handle three cases cleanly:
//   - ERASED rows: ownerNationalId = "ubo_hash:v1:..." (erasure sentinel) → passthrough (no ENC: prefix)
//   - Pre-T004a rows: ownerNationalId = plaintext → passthrough
//   - T004a rows: ownerNationalId = "ENC:v1:..." → decrypt to plaintext
async function decryptUboRow<T extends { ownerName: string; ownerNationalId: string; ownerNationality: string; tenantId: string | null }>(row: T): Promise<T> {
  const tenantId = row.tenantId ?? "";
  const ctx = { type: "tenant" as const, tenantId };
  return {
    ...row,
    ownerName:        await decryptPii(row.ownerName,        ctx),
    ownerNationalId:  await decryptPii(row.ownerNationalId,  ctx),
    ownerNationality: await decryptPii(row.ownerNationality, ctx),
  };
}

// ── KYC Customers — DB-primary CRUD (KYC v1 Feature 1, spec AS/KYC/2026/001) ──
// Tenant-scoped per kyc_customers' own RLS substrate (rls-init.ts TENANT_TABLES:
// USING/WITH CHECK tenant_id = current_tenant_id()). Inside a request these methods
// run on the per-request tenant transaction (tenantMiddleware ALS) so the GUC is set
// and RLS enforces; the explicit tenant_id predicate is belt-and-suspenders. PII
// columns are AES-GCM-encrypted via encryptPii; national_id has an HMAC lookup column.
// Decrypt on read. The encrypt/decrypt column set mirrors saveKycCustomer in
// kyc-agent-engine-db.ts exactly so both write paths interoperate.

// A non-empty tenantId is mandatory — KYC is tenant-scoped and there is no untenanted
// fallback (NO `tenantId ?? ""`; an empty tenant key would silently break HMAC lookups
// and PII key derivation).
function assertKycTenantId(tenantId: string, method: string): void {
  if (typeof tenantId !== "string" || tenantId.length === 0) {
    throw new Error(
      `${method}: a non-empty tenantId is required (KYC is tenant-scoped; no untenanted fallback).`,
    );
  }
}

// Binding exit-criterion guard (spec Part C): in Feature 1 NO storage method may write
// status "APPROVED". Verification alone never approves, and a high-risk/PEP subject can
// never be auto-approved. A legitimate approval is a separate, gated decisioning outcome
// landing in a later feature with its own PEP guard — not reachable through this CRUD.
function assertNotApprovedWrite(status: KYCStatus | undefined, method: string): void {
  if (status === "APPROVED") {
    throw new Error(
      `${method}: writing KYC status "APPROVED" is not permitted. KYC approval is a separate, ` +
        `gated decisioning outcome — verification alone never approves and high-risk/PEP subjects ` +
        `can never be auto-approved.`,
    );
  }
}

async function decryptKycRow(row: KycCustomer, tenantId: string): Promise<KycCustomer> {
  // RLS guarantees row.tenantId === the active tenant; decrypt under that tenant's key.
  const ctx = { type: "tenant" as const, tenantId };
  return {
    ...row,
    nationalId:  await decryptPii(row.nationalId,  ctx),
    fullName:    await decryptPii(row.fullName,    ctx),
    dateOfBirth: await decryptPii(row.dateOfBirth, ctx),
    phoneNumber: await decryptPii(row.phoneNumber, ctx),
    email:    row.email    != null ? await decryptPii(row.email,    ctx) : null,
    address:  row.address  != null ? await decryptPii(row.address,  ctx) : null,
    photoUrl: row.photoUrl != null ? await decryptPii(row.photoUrl, ctx) : null,
  };
}

export interface CreateKycCustomerInput {
  id: string;
  nationalId: string;
  fullName: string;
  dateOfBirth: string;
  phoneNumber: string;
  email?: string | null;
  address?: string | null;
  photoUrl?: string | null;
  status?: KYCStatus;
  riskTier?: KYCRiskTier;
  onboardedAt?: Date | null;
  lastVerifiedAt?: Date | null;
}

// AEGIS SOAR (AS/SOAR/2026/001) M1 Gate 2 — ingestion seam contract. tenant +
// actor come from the authenticated session (resolved in the route), never from
// the request payload; sourceSignalHash is computed server-side from the signal.
export interface IngestFincrimeSignalArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  sourceType: string;
  sourceRef: string | null;
  subjectRef: string;
  subjectRefKind: string;
  sourceSignalHash: string;
  signal: Record<string, unknown>;
}

export interface IngestFincrimeSignalResult {
  case: FincrimeCase;
  created: boolean; // true = a new OPEN case was created; false = idempotent replay returned the existing case
}

// AEGIS SOAR (AS/SOAR/2026/001) M2 Investigation — storage I/O contracts. tenant
// + actor always come from the authenticated session (resolved in the route),
// never from the request payload. Mirrors the M1 ingestion-seam pattern.
export type FincrimeQueueKind =
  | "open"
  | "mine"
  | "pending-mlco-review"
  | "all-active";

export interface ListFincrimeCasesArgs {
  tenantId: string;
  actorUserId: string;
  queue: FincrimeQueueKind;
}

export interface AssignFincrimeCaseArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  assigneeUserId: string;
}

export interface StartFincrimeInvestigationArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  note?: string | null;
}

export interface AddFincrimeCaseEvidenceArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  evidenceType: string;
  opaqueRef?: string | null;
  note?: string | null;
}

export interface EscalateFincrimeCaseArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  summary: string;
}

// A transition/evidence mutation result. `transitioned` is false ONLY when the
// CAS matched no row (state conflict / absent-in-tenant) → the route maps it to
// 409. `notFound`/`stateNotAllowed` are used by the evidence-add gate so the
// route can distinguish 404 (absent/cross-tenant) from 409 (wrong state).
export interface FincrimeMutationResult {
  ok: boolean;
  case?: FincrimeCase;
  evidenceId?: string;
  conflict?: boolean; // CAS matched 0 rows → 409
  notFound?: boolean; // case absent in tenant → 404
  stateNotAllowed?: boolean; // evidence-add against a non-eligible state → 409
}

// ── AEGIS SECURITY IR (SECURITY_IR_WORKFLOW_SCOPE.md) — storage I/O contracts ──
// tenant + actor always come from the authenticated session (resolved in the
// route), NEVER from the request payload. Mirrors the fincrime case-spine pattern.
export interface OpenSecurityIncidentArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  title: string;
  severity: string;
  note?: string | null;
  // Alert-in seam (Layer 2): set only when the incident is opened from an
  // (currently synthetic) EDR/SIEM alert. The alert DATA is synthetic until
  // APT-make-real; these wire the real INTERFACE (alert id + source).
  originatingAlertRef?: string | null;
  originatingAlertSource?: string | null;
}

export interface ListSecurityIncidentsArgs {
  tenantId: string;
  actorUserId: string;
  queue: IrQueue;
}

export interface GetSecurityIncidentArgs {
  tenantId: string;
  incidentId: string;
}

export interface TransitionSecurityIncidentArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  incidentId: string;
  action: string; // IR action from the route path; the workflow derives the target state
  note?: string | null;
}

export interface AssignSecurityIncidentArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  incidentId: string;
  assigneeUserId: string;
}

// An IR transition/assign mutation result. `conflict` is true ONLY when the CAS
// matched no row (state precondition / absent-in-tenant) → the route maps it to
// 409. `incident` carries the post-mutation row.
export interface SecurityIncidentMutationResult {
  ok: boolean;
  incident?: SecurityIncident;
  conflict?: boolean; // CAS matched 0 rows → 409
}

// ── AEGIS UEBA (UEBA_PRIVILEGED_SCOPE.md) — storage I/O types ─────────────────
// tenantId/actorUserId/actorRole are authoritative params resolved from the session
// at the route (NEVER the payload). An anomaly persists the REAL audit_logs ids that
// triggered it (triggeringAuditIds) and, when opened, the linked security_incident.
export interface InsertUebaAnomalyArgs {
  tenantId: string;
  userId: string;
  rule: string;                 // OFF_HOURS_PRIVILEGED_ACCESS | BULK_EXPORT (DB CHECK enforces)
  observed: string;
  expected: string;
  confidence: number;           // 0–1
  triggeringAuditIds: string[]; // REAL audit_logs ids — DB CHECK requires non-empty
  openedIncidentId?: string | null;
}

// ── AEGIS SOAR M3 CHUNK 2 — STR finalizer storage I/O types ──────────────────
// tenantId/actorUserId/actorRole are authoritative params resolved from the
// session at the route (NEVER the payload). The DTOs (str-workflow.ts) carry only
// narrative / reportHash / reason.
export interface PrepareStrArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  narrative: string;
}

export interface PrepareStrResult {
  strReportId: string;
  narrativeHash: string;
  status: string;
}

export interface FinalizeStrArgs {
  tenantId: string;
  actorUserId: string;
  actorRole: string;
  caseId: string;
  reportHash: string;
  reason: string;
}

export interface FinalizeStrResult {
  caseId: string;
  state: string;
  disposition: string;
  strReportId: string;
  approvalId: string;
}

export interface GetStrArgs {
  tenantId: string;
  caseId: string;
}

// AEGIS SOAR M3 CHUNK 3 — goAML Form-B export read args (read-only render source).
export interface GetFormBArgs {
  tenantId: string;
  caseId: string;
}

// PII-safe read view: subject_ref is OPAQUE; narrative is decrypted ONLY for an
// authorized reader (the route guard restricts to admin/risk_manager/auditor — the
// tipping-off control). No approval/maker fields beyond prepared_by are exposed.
export interface StrReadView {
  id: string;
  caseId: string;
  status: string;
  subjectRef: string;
  subjectRefKind: string;
  narrative: string | null;
  narrativeHash: string | null;
  fiaReference: string | null;
  filedAt: Date | null;
  acknowledgedAt: Date | null;
  preparedByUserId: string;
  preparedByRole: string;
  retentionUntil: Date;
  createdAt: Date;
}

export interface IStorage {
  // Users
  getUser(id: string): Promise<User | undefined>;
  getUserByUsername(username: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUserLastLogin(id: string): Promise<void>;
  updateUserPassword(id: string, hashedPassword: string): Promise<void>;

  // Sessions
  createSession(userId: string, token: string, expiresAt: Date): Promise<Session>;
  getSessionByToken(token: string): Promise<Session | undefined>;
  deleteSession(token: string): Promise<void>;
  updateSessionActivity(token: string): Promise<void>;
  deleteExpiredSessions(): Promise<void>;

  // Threats
  getThreats(limit?: number): Promise<ThreatEvent[]>;
  getThreatById(id: string): Promise<ThreatEvent | undefined>;
  createThreat(threat: InsertThreatEvent): Promise<ThreatEvent>;
  neutralizeThreat(id: string, userId: string): Promise<ThreatEvent | undefined>;
  getThreatsStats(): Promise<ThreatsStats>;

  // Audit Logs
  getAuditLogs(filters?: { action?: string; userId?: string; limit?: number }): Promise<AuditLog[]>;
  createAuditLog(log: InsertAuditLog): Promise<AuditLog>;
  // A (kyc_screening_results): record durable per-screen history (insert-only). MUST be called
  // inside the route's withTenantDbPhase (RLS WITH CHECK on the tenant GUC). Never writes customer state.
  recordKycScreening(rows: InsertKycScreeningResult[]): Promise<void>;
  // Latest SANCTIONS screening for a customer: reads the durable history TABLE first (status + score
  // + list version + matched-entry snapshot), falling back to the legacy audit-log parse for pre-A
  // records (status only). Read-only; tenant-scoped by explicit tenantId.
  getLatestKycScreening(
    customerId: string,
    tenantId: string,
  ): Promise<{
    source: "table" | "audit-legacy";
    sanctionsStatus: string;
    screenedAt: string;
    score: string | null;
    listSource: string | null;
    listVersionHash: string | null;
    listFetchedAt: string | null;
    matchedEntNum: string | null;
    matchedProgram: string | null;
    matchedName: string | null;
  } | null>;

  // System Metrics
  getLatestMetrics(): Promise<SystemMetric[]>;
  createMetric(metric: InsertSystemMetric): Promise<SystemMetric>;

  // AI Thresholds
  getThresholds(): Promise<AIThreshold[]>;
  updateThreshold(name: string, value: number, userId: string): Promise<AIThreshold | undefined>;

  // Webhooks
  getWebhooks(): Promise<WebhookConfig[]>;
  getWebhookById(id: string): Promise<WebhookConfig | undefined>;
  createWebhook(config: InsertWebhookConfig): Promise<WebhookConfig>;
  updateWebhook(id: string, updates: Partial<InsertWebhookConfig>): Promise<WebhookConfig | undefined>;
  deleteWebhook(id: string): Promise<void>;
  logWebhookExecution(log: {
    webhookId: string;
    eventType: WebhookEventType;
    payload: string;
    responseStatus?: number;
    responseBody?: string;
    executionTimeMs?: number;
    success: boolean;
    errorMessage?: string;
  }): Promise<WebhookLog>;
  getWebhookLogs(webhookId?: string, limit?: number): Promise<WebhookLog[]>;

  // Ghost Core Events
  getGhostCoreEvents(limit?: number, tenantId?: string): Promise<GhostCoreEvent[]>;
  createGhostCoreEvent(event: InsertGhostCoreEvent): Promise<GhostCoreEvent>;

  // SOAR Executions
  getSoarExecutions(limit?: number): Promise<SoarExecution[]>;
  createSoarExecution(exec: InsertSoarExecution): Promise<SoarExecution>;
  updateSoarExecution(id: string, updates: Partial<InsertSoarExecution>): Promise<SoarExecution | undefined>;

  // Observer Logs
  getObserverLogs(limit?: number): Promise<ObserverLog[]>;
  createObserverLog(log: InsertObserverLog): Promise<ObserverLog>;

  // Notification Logs
  getNotificationLogs(limit?: number, channel?: string): Promise<NotificationLog[]>;
  createNotificationLog(log: InsertNotificationLog): Promise<NotificationLog>;
  updateNotificationLog(id: string, updates: Partial<InsertNotificationLog>): Promise<NotificationLog | undefined>;

  // Export Jobs
  getExportJobs(limit?: number): Promise<ExportJob[]>;
  createExportJob(job: InsertExportJob): Promise<ExportJob>;
  updateExportJob(id: string, updates: Partial<InsertExportJob>): Promise<ExportJob | undefined>;

  // API Keys
  getApiKeys(tenantId?: string): Promise<ApiKey[]>;
  getApiKeyByHash(keyHash: string): Promise<ApiKey | undefined>;
  createApiKey(key: InsertApiKey): Promise<ApiKey>;
  updateApiKeyLastUsed(id: string): Promise<void>;
  deactivateApiKey(id: string): Promise<void>;

  // Tenants
  getTenants(): Promise<Tenant[]>;
  getTenantById(id: string): Promise<Tenant | undefined>;
  getTenantBySlug(slug: string): Promise<Tenant | undefined>;
  createTenant(tenant: InsertTenant): Promise<Tenant>;
  updateTenant(id: string, updates: Partial<InsertTenant>): Promise<Tenant | undefined>;

  // Backup Records
  getBackupRecords(limit?: number): Promise<BackupRecord[]>;
  createBackupRecord(record: InsertBackupRecord): Promise<BackupRecord>;
  updateBackupRecord(id: string, updates: Partial<InsertBackupRecord>): Promise<BackupRecord | undefined>;

  // AI Threat Scores
  getAiThreatScores(threatEventId?: string): Promise<AiThreatScore[]>;
  createAiThreatScore(score: InsertAiThreatScore): Promise<AiThreatScore>;

  // SSO Configs
  getSsoConfigs(tenantId?: string): Promise<SsoConfig[]>;
  createSsoConfig(config: InsertSsoConfig): Promise<SsoConfig>;
  updateSsoConfig(id: string, updates: Partial<InsertSsoConfig>): Promise<SsoConfig | undefined>;

  // I18n Overrides
  getI18nOverrides(locale: string, tenantId?: string): Promise<I18nOverride[]>;
  createI18nOverride(override: InsertI18nOverride): Promise<I18nOverride>;

  // UBO Registrations — erasure-path surface (FU-015 / P4.2 Phase 1, 2026-05-24)
  // Thin wrappers over schema columns; business logic (eligibility decisions,
  // PII-redaction-with-irrecoverable-salt) lives in server/lib/ubo-erasure.ts.
  //
  // FU-038 (P4.2 Phase 3 architect-review HIGH, 2026-05-24): all five UBO
  // storage methods accept an optional `tenantId` parameter that scopes the
  // SELECT/UPDATE to a specific tenant. When supplied as a non-empty string,
  // an `AND tenant_id = $tenantId` predicate is added. When omitted (undefined)
  // or passed as null, no tenant filter is added — preserving back-compat for
  // the only pre-existing callers (the Phase 2 ubo-erasure.ts module, which
  // was untenanted until Phase 3 wiring activated the first practical
  // multi-tenant exposure surface in the DSAR purge worker). Callers that
  // operate on tenant-scoped DSAR rows (Phase 3 dsar-purge-worker.ts) MUST
  // pass `dsarRow.tenantId` through to close the cross-tenant leak that
  // Rule 9 architect review flagged on 2026-05-24.
  getUboRegistrationsByCorporate(corporateCustomerRef: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration[]>;
  getUboRegistrationsByNationalId(ownerNationalId: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration[]>;
  // Phase 2 substrate extension (FU-037 / P4.2 Phase 2 architect-review MEDIUM
  // finding): single-row lookup by composite uboRef. Needed by the corporate-
  // sweep retry-on-race path in server/lib/ubo-erasure.ts processCorporateErasure
  // and by the Phase 4 admin endpoint's pre-CAS row validation. Returns
  // undefined when no row matches (rather than throwing) so callers can
  // distinguish "ref does not exist" from "lookup failed".
  getUboRegistrationByRef(uboRef: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration | undefined>;
  // Optional CAS guard via `expectedCurrentState`: the UPDATE matches only if
  // the row's current erasure_state equals the expected value. Returns
  // undefined on miss (already transitioned, lost the race). Phase 2 callers
  // in server/lib/ubo-erasure.ts pass this to close the LOW concurrency
  // finding from Phase 1's architect review.
  updateUboErasureState(uboRef: string, updates: {
    erasureState?: UboErasureState;
    supersededAt?: Date | null;
    supersededBy?: string | null;
    erasureMaturedAt?: Date | null;
    erasureTrigger?: UboErasureTrigger | null;
    erasureAuditRef?: string | null;
    individualErasurePending?: string | null;
  }, opts?: { expectedCurrentState?: UboErasureState; tenantId?: string | null; _db?: DrizzleDb }): Promise<UboRegistration | undefined>;
  // Caller must supply the redacted values explicitly — owner_nationality is
  // NOT NULL in schema, so the interface requires a string (typically
  // "[redacted]" sentinel matching the owner_name convention; or a region
  // code if partial-redaction is operator-policy). Architect-review MEDIUM
  // finding: prior interface allowed null which the impl silently coerced
  // to empty string — that drift is closed.
  //
  // Optional CAS guard via `expectedCurrentState`: see updateUboErasureState above.
  softEraseUboRow(uboRef: string, redactedFields: {
    ownerName: string;
    ownerNationalId: string;
    ownerNationality: string;
  }, trigger: UboErasureTrigger, auditRef: string,
     opts?: { expectedCurrentState?: UboErasureState; tenantId?: string | null; _db?: DrizzleDb }): Promise<UboRegistration | undefined>;

  // KYC Customers (DB-primary; tenant-scoped; PII-encrypted) — KYC v1 Feature 1
  createKycCustomer(input: CreateKycCustomerInput, tenantId: string, opts?: { _db?: DrizzleDb }): Promise<KycCustomer>;
  getKycCustomerById(id: string, tenantId: string, opts?: { _db?: DrizzleDb }): Promise<KycCustomer | undefined>;
  getKycCustomerByNationalId(nationalId: string, tenantId: string, opts?: { _db?: DrizzleDb }): Promise<KycCustomer | undefined>;
  listKycCustomers(tenantId: string, limit?: number, opts?: { _db?: DrizzleDb }): Promise<KycCustomer[]>;
  // KYC v1 Feature 4 (examiner evidence) — NON-DECRYPTING reads: SELECT only the
  // safe, non-PII columns. The decrypt path is never invoked, so no PII can leak.
  getKycCustomerRefForExaminer(id: string, tenantId: string, opts?: { _db?: DrizzleDb }): Promise<KycCustomerRefRow | undefined>;
  listKycCustomerRefsForExaminer(tenantId: string, opts?: { limit?: number; _db?: DrizzleDb }): Promise<KycCustomerRefRow[]>;
  updateKycCustomerStatusAndRisk(
    id: string,
    tenantId: string,
    updates: { status?: KYCStatus; riskTier?: KYCRiskTier; lastVerifiedAt?: Date | null },
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer | undefined>;

  // KYC Decisions (Feature 3, spec AS/KYC/2026/002) — tenant-scoped; no PII columns
  createKycDecisionOutcome(
    input: CreateKycDecisionOutcomeInput,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome>;
  getKycDecisionOutcomeById(
    id: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome | undefined>;
  listKycDecisionOutcomes(
    tenantId: string,
    opts?: { customerId?: string; limit?: number; _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome[]>;
  getKycDecisionApprovalByDecisionId(
    decisionId: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionApproval | undefined>;
  recordApprovalAndFinalize(args: RecordApprovalAndFinalizeArgs): Promise<RecordApprovalAndFinalizeResult>;

  // Fincrime (AEGIS SOAR AS/SOAR/2026/001) M1 Gate 2 — ingestion seam
  ingestFincrimeSignal(args: IngestFincrimeSignalArgs): Promise<IngestFincrimeSignalResult>;

  // Fincrime (AEGIS SOAR AS/SOAR/2026/001) M2 Investigation
  listFincrimeCasesForQueue(args: ListFincrimeCasesArgs): Promise<FincrimeCase[]>;
  assignFincrimeCase(args: AssignFincrimeCaseArgs): Promise<FincrimeMutationResult>;
  startFincrimeInvestigation(args: StartFincrimeInvestigationArgs): Promise<FincrimeMutationResult>;
  addFincrimeCaseEvidence(args: AddFincrimeCaseEvidenceArgs): Promise<FincrimeMutationResult>;
  escalateFincrimeCaseToMlcoReview(args: EscalateFincrimeCaseArgs): Promise<FincrimeMutationResult>;
  // AEGIS SOAR M3 CHUNK 2 — locked-door STR finalizer
  prepareStr(args: PrepareStrArgs): Promise<PrepareStrResult>;
  finalizeStr(args: FinalizeStrArgs): Promise<FinalizeStrResult>;
  getStrForCase(args: GetStrArgs): Promise<StrReadView | null>;
  // AEGIS SOAR M3 CHUNK 3 — goAML Form-B export (read-only render source)
  getFincrimeFormBSource(args: GetFormBArgs): Promise<FormBSource | null>;
  // AEGIS SOAR M4 — regulator-facing SOAR surface (read-only; non-PII)
  listFincrimeCasesForRegulator(tenantId: string, opts?: { limit?: number }): Promise<RegulatorCaseRefRow[]>;
  getFincrimeCaseForRegulator(caseId: string, tenantId: string): Promise<RegulatorCaseRefRow | undefined>;
  getFincrimeCaseIntegrityInputs(caseId: string, tenantId: string): Promise<FincrimeCaseIntegrityInput | null>;

  // AEGIS SECURITY IR (SECURITY_IR_WORKFLOW_SCOPE.md) — Layer 1 + Layer-2 seam
  openSecurityIncident(args: OpenSecurityIncidentArgs): Promise<SecurityIncident>;
  listSecurityIncidentsForQueue(args: ListSecurityIncidentsArgs): Promise<SecurityIncident[]>;
  getSecurityIncident(args: GetSecurityIncidentArgs): Promise<SecurityIncident | undefined>;
  transitionSecurityIncident(args: TransitionSecurityIncidentArgs): Promise<SecurityIncidentMutationResult>;
  assignSecurityIncident(args: AssignSecurityIncidentArgs): Promise<SecurityIncidentMutationResult>;

  // AEGIS UEBA (UEBA_PRIVILEGED_SCOPE.md) — baselines + anomalies (tenant-scoped)
  listUebaBaselines(tenantId: string): Promise<UebaUserBaseline[]>;
  insertUebaAnomaly(args: InsertUebaAnomalyArgs): Promise<UebaAnomaly>;
  setUebaAnomalyIncident(tenantId: string, anomalyId: string, incidentId: string): Promise<void>;
  listUebaAnomalies(tenantId: string, opts?: { limit?: number }): Promise<UebaAnomaly[]>;
}

export class DatabaseStorage implements IStorage {
  // Users
  async getUser(id: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    if (!user) return undefined;
    return { ...user, fullName: user.fullName ? await decryptPii(user.fullName, PLATFORM_CTX) : user.fullName };
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.username, username));
    if (!user) return undefined;
    return { ...user, fullName: user.fullName ? await decryptPii(user.fullName, PLATFORM_CTX) : user.fullName };
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const dbInsert = insertUser.fullName
      ? { ...insertUser, fullName: await encryptPii(insertUser.fullName, PLATFORM_CTX) }
      : insertUser;
    const [user] = await db.insert(users).values(dbInsert).returning();
    return { ...user, fullName: insertUser.fullName ?? user.fullName };
  }

  // Admin user directory — decrypts full_name (platform-key PII) for display; NEVER returns the
  // password hash. Read-only. (Also serves as the live decrypted-PII surface after the T2a async
  // migration — the names shown are decrypted via the async decryptPii v1/v2 read path.)
  async listUsers(): Promise<Array<{ id: string; username: string; fullName: string; role: string; secondaryRoles: string[]; department: string | null; isActive: boolean; lastLogin: Date | null; createdAt: Date }>> {
    const rows = await db.select().from(users).orderBy(users.username);
    return Promise.all(rows.map(async (u) => ({
      id: u.id,
      username: u.username,
      fullName: u.fullName ? await decryptPii(u.fullName, PLATFORM_CTX) : u.fullName,
      role: u.role,
      secondaryRoles: u.secondaryRoles ?? [],
      department: u.department,
      isActive: u.isActive,
      lastLogin: u.lastLogin,
      createdAt: u.createdAt,
    })));
  }

  async updateUserLastLogin(id: string): Promise<void> {
    await db.update(users)
      .set({ lastLogin: new Date() })
      .where(eq(users.id, id));
  }

  async updateUserPassword(id: string, hashedPassword: string): Promise<void> {
    await db.update(users)
      .set({ password: hashedPassword })
      .where(eq(users.id, id));
  }

  // Sessions
  async createSession(userId: string, token: string, expiresAt: Date): Promise<Session> {
    const [session] = await db.insert(sessions)
      .values({ userId, token, expiresAt })
      .returning();
    return session;
  }

  async getSessionByToken(token: string): Promise<Session | undefined> {
    const [session] = await db.select().from(sessions).where(eq(sessions.token, token));
    return session || undefined;
  }

  async deleteSession(token: string): Promise<void> {
    await db.delete(sessions).where(eq(sessions.token, token));
  }

  async updateSessionActivity(token: string): Promise<void> {
    await db.update(sessions)
      .set({ lastActivity: new Date() })
      .where(eq(sessions.token, token));
  }

  async deleteExpiredSessions(): Promise<void> {
    await db.delete(sessions).where(lte(sessions.expiresAt, new Date()));
  }

  // Threats
  async getThreats(limit = 50): Promise<ThreatEvent[]> {
    return db.select()
      .from(threatEvents)
      .orderBy(desc(threatEvents.timestamp))
      .limit(limit);
  }

  async getThreatById(id: string): Promise<ThreatEvent | undefined> {
    const [threat] = await db.select().from(threatEvents).where(eq(threatEvents.id, id));
    return threat || undefined;
  }

  async createThreat(threat: InsertThreatEvent): Promise<ThreatEvent> {
    const [newThreat] = await db.insert(threatEvents).values(threat).returning();
    return newThreat;
  }

  async neutralizeThreat(id: string, userId: string): Promise<ThreatEvent | undefined> {
    const [updated] = await db.update(threatEvents)
      .set({ 
        isNeutralized: true, 
        neutralizedBy: userId, 
        neutralizedAt: new Date() 
      })
      .where(eq(threatEvents.id, id))
      .returning();
    return updated || undefined;
  }

  async getThreatsStats(): Promise<ThreatsStats> {
    const allThreats = await db.select().from(threatEvents);
    
    const totalNeutralized = allThreats.filter(t => t.isNeutralized).length;
    const criticalAnomalies = allThreats.filter(t => t.riskLevel === "CRITICAL" && !t.isNeutralized).length;
    const activeThreats = allThreats.filter(t => !t.isNeutralized).length;
    
    return {
      totalNeutralized,
      criticalAnomalies,
      // HONESTY 2026-07-05: was hardcoded 99.8 (fabricated) — no real identity-vault
      // telemetry is wired, so no bio-vault score is measured. Null; the dashboard renders
      // "—" / "not measured" and CSV/JSON exports render "not measured" instead of a fake score.
      bioVaultScore: null,
      activeThreats,
    };
  }

  // Audit Logs
  async getAuditLogs(filters?: { action?: string; userId?: string; limit?: number }): Promise<AuditLog[]> {
    let query = db.select().from(auditLogs).orderBy(desc(auditLogs.timestamp));
    
    if (filters?.limit) {
      query = query.limit(filters.limit) as any;
    }
    
    return query;
  }

  // A (kyc_screening_results): record the durable per-screen history rows. INSERT-ONLY (immutable).
  // Called INSIDE the route's withTenantDbPhase so the tenant GUC is set on the connection — RLS
  // WITH CHECK then enforces that each row's tenant_id equals the GUC tenant. records-never-decides:
  // this writes screening history ONLY; it never touches customer status / risk_tier.
  async recordKycScreening(rows: InsertKycScreeningResult[]): Promise<void> {
    if (rows.length === 0) return;
    await db.insert(kycScreeningResults).values(rows);
  }

  // A: latest SANCTIONS screening SIGNAL for a customer. Reads the durable history TABLE first
  // (structured — status, score, list version, matched-entry snapshot), which is the live path.
  // Falls back to the legacy KYC_SCREENING audit-log parse ONLY for pre-A customers (screened before
  // this table existed): those have status + timestamp but no score/version/matched-entry, so the
  // freetext regex is now a legacy-only path, not the live one. Tenant-scoped by explicit tenantId
  // (mirrors getKycCustomerById); the route also tenant-gates on customer ownership first.
  async getLatestKycScreening(
    customerId: string,
    tenantId: string,
  ): Promise<{
    source: "table" | "audit-legacy";
    sanctionsStatus: string;
    screenedAt: string;
    score: string | null;
    listSource: string | null;
    listVersionHash: string | null;
    listFetchedAt: string | null;
    matchedEntNum: string | null;
    matchedProgram: string | null;
    matchedName: string | null;
  } | null> {
    const [row] = await db
      .select()
      .from(kycScreeningResults)
      .where(
        and(
          eq(kycScreeningResults.customerId, customerId),
          eq(kycScreeningResults.tenantId, tenantId),
          eq(kycScreeningResults.screenType, "SANCTIONS"),
        ),
      )
      .orderBy(desc(kycScreeningResults.screenedAt))
      .limit(1);
    if (row) {
      return {
        source: "table",
        sanctionsStatus: row.status,
        screenedAt: row.screenedAt instanceof Date ? row.screenedAt.toISOString() : String(row.screenedAt),
        score: row.score ?? null, // numeric → exact string ("0.941"), never a float
        listSource: row.listSource ?? null,
        listVersionHash: row.listVersionHash ?? null,
        listFetchedAt: row.listFetchedAt
          ? (row.listFetchedAt instanceof Date ? row.listFetchedAt.toISOString() : String(row.listFetchedAt))
          : null,
        matchedEntNum: row.matchedEntNum ?? null,
        matchedProgram: row.matchedProgram ?? null,
        matchedName: row.matchedName ?? null,
      };
    }
    // LEGACY FALLBACK — pre-A record: only the KYC_SCREENING audit line exists (status only; the
    // audit detail never carried score/version/matched-entry). audit_logs is global (no tenant_id) —
    // safe here because the route already confirmed this customer belongs to the tenant.
    const resource = `kyc_customers/${customerId}`;
    const [aud] = await db
      .select({ details: auditLogs.details, timestamp: auditLogs.timestamp })
      .from(auditLogs)
      .where(and(eq(auditLogs.action, "KYC_SCREENING"), eq(auditLogs.resource, resource)))
      .orderBy(desc(auditLogs.timestamp))
      .limit(1);
    if (!aud) return null;
    const m = /\bsanctions=([A-Z_]+)/.exec(aud.details ?? "");
    return {
      source: "audit-legacy",
      sanctionsStatus: m?.[1] ?? "UNKNOWN",
      screenedAt: aud.timestamp instanceof Date ? aud.timestamp.toISOString() : String(aud.timestamp),
      score: null, listSource: null, listVersionHash: null, listFetchedAt: null,
      matchedEntNum: null, matchedProgram: null, matchedName: null,
    };
  }

  async createAuditLog(log: InsertAuditLog): Promise<AuditLog> {
    const [newLog] = await db.insert(auditLogs).values(log).returning();

    // Hash-chain the audit entry. Best-effort: chain-write failure must NOT break
    // the audit-log write itself (availability of audit > chain coverage). The
    // chain becomes valid going forward from the wiring-deploy timestamp; pre-
    // existing audit_logs rows remain unchained per the immutability rule.
    // Dynamic import sidesteps any circular dependency between storage.ts and
    // lib/audit-chain.ts (both pull from @shared/schema).
    try {
      const { chainAuditLog } = await import("./lib/audit-chain");
      await chainAuditLog({
        id: newLog.id,
        action: newLog.action,
        userId: newLog.userId,
        resource: newLog.resource,
        details: newLog.details ?? "",
        timestamp: newLog.timestamp instanceof Date
          ? newLog.timestamp.toISOString()
          : String(newLog.timestamp),
      });
    } catch (chainErr: any) {
      logger.child('audit-chain').error('chain-write failed; audit log persisted but unchained', {
        metadata: { auditLogId: newLog.id },
        error: chainErr instanceof Error ? chainErr : new Error(String(chainErr?.message || chainErr)),
      });
    }

    // ── SIEM tap (the missing producer for the forwarder's eventQueue) ─────────
    // Feed the REAL outbound SIEM forwarder from this single chokepoint. The event
    // is normalized + PII-redacted, then enqueued. This is the HOT PATH, so it is:
    //   • fire-and-forget — never awaited on the request path;
    //   • try/catch wrapped — a forwarding failure NEVER breaks the audit write;
    //   • a clean no-op when no SIEM endpoint is configured (enqueueSecurityEvent
    //     returns queued:false and pushes nothing — the honest default).
    // Every forwarded event traces back to this exact audit row + its hash-chain
    // entry (auditChainRef = newLog.id). No seeding, no synthetic throughput.
    try {
      void import("./lib/siem-forwarder").then(({ normalizeAuditLog, enqueueSecurityEvent }) => {
        try {
          const normalized = normalizeAuditLog({
            id: newLog.id,
            action: newLog.action,
            userId: newLog.userId,
            resource: newLog.resource,
            details: newLog.details ?? "",
            ipAddress: newLog.ipAddress ?? null,
            timestamp: newLog.timestamp,
          });
          enqueueSecurityEvent(normalized);
        } catch { /* best-effort: forwarding must never affect the audit write */ }
      }).catch(() => { /* dynamic import failure is non-fatal */ });
    } catch { /* defensive: never throw on the audit hot path */ }

    return newLog;
  }

  // ── Fincrime (AEGIS SOAR AS/SOAR/2026/001) M1 Gate 2: ingestion seam ───────
  // Source-agnostic SIGNAL → one OPEN case. MUST run inside the caller's bounded
  // tenant transaction (the route wraps this in withTenantDbPhase) so the GUC is
  // set on the querying connection (RLS) and the COMMIT happens BEFORE the HTTP
  // response — no respond-before-commit false-green. RLS WITH CHECK enforces that
  // the inserted tenant_id equals the GUC tenant; tenant_id here originates from
  // the authenticated actor, never the request payload.
  async ingestFincrimeSignal(
    args: IngestFincrimeSignalArgs,
  ): Promise<IngestFincrimeSignalResult> {
    // (1) Idempotency fast-path: a genuine replay returns the EXISTING case
    // without writing a spurious audit attempt or event. RLS + the explicit
    // tenant predicate scope this read; UNIQUE(tenant_id, source_signal_hash)
    // guarantees at most one row.
    const existingBefore = await db
      .select()
      .from(fincrimeCases)
      .where(
        and(
          eq(fincrimeCases.tenantId, args.tenantId),
          eq(fincrimeCases.sourceSignalHash, args.sourceSignalHash),
        ),
      )
      .limit(1);
    if (existingBefore.length > 0) {
      return { case: existingBefore[0], created: false };
    }

    // (2) Audit ATTEMPT-record BEFORE the case insert (operator-mandated
    // tamper-evident ordering): the hash-chain entry is written via the
    // independent auditDb path inside chainAuditLog and commits independently of
    // this tenant tx. If the case insert below rolls back, this remains an honest
    // ATTEMPT with no case; a committed case can therefore never exist without a
    // preceding audit record. Named "…_ATTEMPT" so the chain never over-claims a
    // creation. Dynamic import mirrors createAuditLog (avoids a circular dep).
    const caseId = randomUUID();
    const nowIso = new Date().toISOString();
    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-ingest:${caseId}`,
      action: "FINCRIME_CASE_OPEN_ATTEMPT",
      userId: args.actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId: args.tenantId,
        sourceType: args.sourceType,
        sourceRef: args.sourceRef,
        subjectRef: args.subjectRef,
        subjectRefKind: args.subjectRefKind,
        sourceSignalHash: args.sourceSignalHash,
        actorRole: args.actorRole,
      }),
      timestamp: nowIso,
    });
    // Resolve the chain row's integer PK by its unique hash for the case→audit
    // linkage. aegis_app holds SELECT on audit_chain_entries and the row is
    // already committed (auditDb), so the request runner can read it.
    const [chainRow] = await db
      .select({ id: auditChainEntries.id })
      .from(auditChainEntries)
      .where(eq(auditChainEntries.hash, chained.hash))
      .limit(1);
    const auditChainEntryId = chainRow?.id ?? null;

    // (3) Case insert with DB-enforced idempotency. ON CONFLICT(tenant_id,
    // source_signal_hash) DO NOTHING closes the concurrent-creator race the
    // pre-SELECT cannot: a row back => we created it; nothing back => a
    // concurrent request won, so re-read and return that case (idempotent).
    const insertedRows = await db
      .insert(fincrimeCases)
      .values({
        id: caseId,
        tenantId: args.tenantId,
        sourceType: args.sourceType,
        sourceRef: args.sourceRef,
        sourceSignalHash: args.sourceSignalHash,
        subjectRef: args.subjectRef,
        subjectRefKind: args.subjectRefKind,
        state: "OPEN",
        auditChainEntryId,
      })
      .onConflictDoNothing({
        target: [fincrimeCases.tenantId, fincrimeCases.sourceSignalHash],
      })
      .returning();

    if (insertedRows.length === 0) {
      // Lost the race — the winning case already exists. Return it; do NOT write
      // a CASE_OPENED event (the winner wrote its own).
      const [winner] = await db
        .select()
        .from(fincrimeCases)
        .where(
          and(
            eq(fincrimeCases.tenantId, args.tenantId),
            eq(fincrimeCases.sourceSignalHash, args.sourceSignalHash),
          ),
        )
        .limit(1);
      return { case: winner, created: false };
    }

    const newCase = insertedRows[0];

    // (4) CASE_OPENED event in the SAME tenant tx (exercises the tenant-bound
    // composite FK). from_state null (creation) → to_state OPEN; tamper-evident
    // payload_hash. No raw PII (subject_ref is opaque).
    const eventPayload = JSON.stringify({
      caseId: newCase.id,
      sourceType: newCase.sourceType,
      sourceRef: newCase.sourceRef,
      subjectRef: newCase.subjectRef,
      subjectRefKind: newCase.subjectRefKind,
      sourceSignalHash: newCase.sourceSignalHash,
      openedBy: args.actorUserId,
      openedRole: args.actorRole,
      openedAt: nowIso,
    });
    const payloadHash = createHash("sha256").update(eventPayload).digest("hex");
    await db.insert(fincrimeCaseEvents).values({
      id: randomUUID(),
      tenantId: args.tenantId,
      caseId: newCase.id,
      eventType: "CASE_OPENED",
      fromState: null,
      toState: "OPEN",
      actorUserId: args.actorUserId,
      actorRole: args.actorRole,
      eventPayload,
      payloadHash,
      auditChainEntryId,
    });

    return { case: newCase, created: true };
  }

  // ── Fincrime (AEGIS SOAR AS/SOAR/2026/001) M2 Investigation ────────────────
  //
  // Shared invariants across the four mutators (mirrors the M1 ingestion seam):
  //   (1) Audit ATTEMPT is chained via auditDb (BYPASSRLS, commits independently)
  //       BEFORE the durable write, so a rolled-back tx still leaves an honest
  //       attempt record. Audit `details` carry ONLY non-PII structural fields
  //       (tenant, case id, action, from/to state, role, presence flags) — never
  //       raw note/summary text.
  //   (2) Transitions use a compare-and-set UPDATE … WHERE tenant_id+id+state=
  //       expectedFrom RETURNING. 0 rows ⇒ conflict (absent / wrong-state /
  //       cross-tenant collapse uniformly to 409 — no oracle).
  //   (3) Transition + evidence + event rows all execute inside the one
  //       withTenantDbPhase tenant tx (RLS-scoped), so they commit atomically;
  //       a failed evidence insert rolls back the transition.
  //   (4) Free text (start.note / escalate.summary / evidence.note) → AES-256-GCM
  //       ciphertext in fincrime_case_evidence.encrypted_note. payload_hash hashes
  //       the CIPHERTEXT / structural envelope, never raw plaintext (architect
  //       ruling, T001).
  // Honest caveat: because the attempt is chained before the effect, a 409/retry
  // or a concurrent race can leave >1 ATTEMPT row per successful effect. The
  // effect itself (case state, evidence row) is exactly-once via the CAS.

  // Resolve the integer PK of the just-chained audit entry by its hash. The
  // entry was committed by auditDb (independent pool); aegis_app has SELECT on
  // audit_chain_entries, so this read works from within the tenant tx.
  private async resolveAuditChainEntryId(hash: string): Promise<number | null> {
    const [row] = await db
      .select({ id: auditChainEntries.id })
      .from(auditChainEntries)
      .where(eq(auditChainEntries.hash, hash))
      .limit(1);
    return row?.id ?? null;
  }

  // Insert a fincrime_case_events row. eventPayload + payloadHash carry only
  // non-PII structural fields (caseId, eventType, states, actor, presence flags,
  // evidence id/hash). Raw note/summary text is NEVER placed here.
  private async insertCaseEvent(p: {
    tenantId: string;
    caseId: string;
    eventType: string;
    fromState: string | null;
    toState: string | null;
    actorUserId: string;
    actorRole: string;
    payloadExtra?: Record<string, unknown>;
    nowIso: string;
    auditChainEntryId: number | null;
  }): Promise<void> {
    const eventPayload = JSON.stringify({
      caseId: p.caseId,
      eventType: p.eventType,
      fromState: p.fromState,
      toState: p.toState,
      actorUserId: p.actorUserId,
      actorRole: p.actorRole,
      at: p.nowIso,
      ...(p.payloadExtra ?? {}),
    });
    const payloadHash = createHash("sha256").update(eventPayload).digest("hex");
    await db.insert(fincrimeCaseEvents).values({
      id: randomUUID(),
      tenantId: p.tenantId,
      caseId: p.caseId,
      eventType: p.eventType,
      fromState: p.fromState,
      toState: p.toState,
      actorUserId: p.actorUserId,
      actorRole: p.actorRole,
      eventPayload,
      payloadHash,
      auditChainEntryId: p.auditChainEntryId,
    });
  }

  // Insert an encrypted evidence row. Free text → AES-256-GCM ciphertext
  // (tenant-scoped key). payload_hash hashes the CIPHERTEXT / structural
  // envelope, never the raw plaintext. Returns the new id + payloadHash so the
  // caller can reference them in the paired EVIDENCE_ADDED event (non-PII).
  private async insertEvidence(p: {
    tenantId: string;
    caseId: string;
    evidenceType: string;
    opaqueRef: string | null;
    note: string | null;
    actorUserId: string;
    actorRole: string;
    nowIso: string;
    auditChainEntryId: number | null;
  }): Promise<{ evidenceId: string; payloadHash: string }> {
    const evidenceId = randomUUID();
    const encryptedNote = p.note
      ? await encryptPii(p.note, { type: "tenant", tenantId: p.tenantId })
      : null;
    const canonical = JSON.stringify({
      evidenceId,
      caseId: p.caseId,
      evidenceType: p.evidenceType,
      opaqueRef: p.opaqueRef ?? null,
      encryptedNote: encryptedNote ?? null,
      addedByUserId: p.actorUserId,
      addedByRole: p.actorRole,
      at: p.nowIso,
    });
    const payloadHash = createHash("sha256").update(canonical).digest("hex");
    await db.insert(fincrimeCaseEvidence).values({
      id: evidenceId,
      tenantId: p.tenantId,
      caseId: p.caseId,
      evidenceType: p.evidenceType,
      opaqueRef: p.opaqueRef ?? null,
      encryptedNote: encryptedNote ?? null,
      addedByUserId: p.actorUserId,
      addedByRole: p.actorRole,
      payloadHash,
      auditChainEntryId: p.auditChainEntryId,
    });
    return { evidenceId, payloadHash };
  }

  async listFincrimeCasesForQueue(
    args: ListFincrimeCasesArgs,
  ): Promise<FincrimeCase[]> {
    const { tenantId, actorUserId, queue } = args;
    // RLS already scopes to the tenant; the explicit tenant predicate is
    // belt-and-suspenders, matching the M1 read pattern.
    const base = eq(fincrimeCases.tenantId, tenantId);
    let predicate;
    switch (queue) {
      case "open":
        predicate = and(base, eq(fincrimeCases.state, "OPEN"));
        break;
      case "mine":
        predicate = and(
          base,
          eq(fincrimeCases.assignedInvestigatorId, actorUserId),
          ne(fincrimeCases.state, "DISPOSITIONED"),
        );
        break;
      case "pending-mlco-review":
        predicate = and(base, eq(fincrimeCases.state, "PENDING_MLCO_REVIEW"));
        break;
      case "all-active":
      default:
        predicate = and(base, ne(fincrimeCases.state, "DISPOSITIONED"));
        break;
    }
    return db
      .select()
      .from(fincrimeCases)
      .where(predicate)
      .orderBy(desc(fincrimeCases.openedAt));
  }

  async assignFincrimeCase(
    args: AssignFincrimeCaseArgs,
  ): Promise<FincrimeMutationResult> {
    const { tenantId, actorUserId, actorRole, caseId, assigneeUserId } = args;
    const edge = assertM2Transition("ASSIGN_CASE", actorRole);
    const nowIso = new Date().toISOString();

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-assign:${caseId}:${randomUUID()}`,
      action: "FINCRIME_CASE_ASSIGN_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        action: edge.action,
        fromState: edge.from,
        toState: edge.to,
        assigneeUserId, // internal user id (non-PII)
        actorRole,
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    const updated = await db
      .update(fincrimeCases)
      .set({
        state: edge.to,
        assignedInvestigatorId: assigneeUserId,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(fincrimeCases.tenantId, tenantId),
          eq(fincrimeCases.id, caseId),
          eq(fincrimeCases.state, edge.from),
        ),
      )
      .returning();
    if (updated.length === 0) {
      return { ok: false, conflict: true };
    }
    const newCase = updated[0];

    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: edge.eventType,
      fromState: edge.from,
      toState: edge.to,
      actorUserId,
      actorRole,
      payloadExtra: { assigneeUserId },
      nowIso,
      auditChainEntryId,
    });

    return { ok: true, case: newCase };
  }

  async startFincrimeInvestigation(
    args: StartFincrimeInvestigationArgs,
  ): Promise<FincrimeMutationResult> {
    const { tenantId, actorUserId, actorRole, caseId, note } = args;
    const edge = assertM2Transition("START_INVESTIGATION", actorRole);
    const nowIso = new Date().toISOString();
    const createsEvidence = Boolean(note);

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-start:${caseId}:${randomUUID()}`,
      action: "FINCRIME_INVESTIGATION_START_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        action: edge.action,
        fromState: edge.from,
        toState: edge.to,
        actorRole,
        createsEvidence, // presence flag only — NO raw note text
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    const updated = await db
      .update(fincrimeCases)
      .set({ state: edge.to, updatedAt: new Date() })
      .where(
        and(
          eq(fincrimeCases.tenantId, tenantId),
          eq(fincrimeCases.id, caseId),
          eq(fincrimeCases.state, edge.from),
        ),
      )
      .returning();
    if (updated.length === 0) {
      return { ok: false, conflict: true };
    }
    const newCase = updated[0];

    let evidenceId: string | undefined;
    if (note) {
      const ev = await this.insertEvidence({
        tenantId,
        caseId,
        evidenceType: "INVESTIGATION_NOTE",
        opaqueRef: null,
        note,
        actorUserId,
        actorRole,
        nowIso,
        auditChainEntryId,
      });
      evidenceId = ev.evidenceId;
      // EVIDENCE_ADDED event for transition-created evidence (shares the audit
      // entry with the transition; carries only non-PII references).
      await this.insertCaseEvent({
        tenantId,
        caseId,
        eventType: EVIDENCE_ADDED_EVENT,
        fromState: null,
        toState: null,
        actorUserId,
        actorRole,
        payloadExtra: {
          evidenceType: "INVESTIGATION_NOTE",
          evidenceId,
          evidencePayloadHash: ev.payloadHash,
          viaAction: edge.action,
        },
        nowIso,
        auditChainEntryId,
      });
    }

    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: edge.eventType,
      fromState: edge.from,
      toState: edge.to,
      actorUserId,
      actorRole,
      payloadExtra: {
        createsEvidence,
        ...(evidenceId ? { evidenceId } : {}),
      },
      nowIso,
      auditChainEntryId,
    });

    return { ok: true, case: newCase, evidenceId };
  }

  async addFincrimeCaseEvidence(
    args: AddFincrimeCaseEvidenceArgs,
  ): Promise<FincrimeMutationResult> {
    const {
      tenantId,
      actorUserId,
      actorRole,
      caseId,
      evidenceType,
      opaqueRef,
      note,
    } = args;
    const nowIso = new Date().toISOString();

    // State gate (§B), ATOMIC: case must exist in this tenant AND be OPEN or
    // UNDER_INVESTIGATION. SELECT ... FOR UPDATE takes a row lock held for the
    // life of the request transaction (this method always runs inside
    // withTenantDbPhase, which BEGINs a pinned-client tx that COMMITs before the
    // route responds). That lock serializes this evidence-add against a
    // concurrent escalate's CAS UPDATE on the SAME case row:
    //   • evidence-tx commits first ⇒ case still UNDER_INVESTIGATION, the
    //     escalate's CAS then proceeds (evidence landed legitimately, pre-escalate);
    //   • escalate-tx commits first ⇒ this FOR UPDATE read blocks until it does,
    //     then observes PENDING_MLCO_REVIEW and returns 409 — evidence can NEVER
    //     land on an already-escalated (terminal) case.
    // This closes the S6-flagged TOCTOU; without the lock, READ COMMITTED could
    // let a stale UNDER_INVESTIGATION read insert evidence after escalation.
    // The pre-SELECT also yields the clean 404 (absent / cross-tenant) vs 409
    // (wrong state) split the route needs; the composite (tenant_id,id) FK on the
    // insert remains the backstop against an absent/cross-tenant case.
    const [existing] = await db
      .select()
      .from(fincrimeCases)
      .where(
        and(
          eq(fincrimeCases.tenantId, tenantId),
          eq(fincrimeCases.id, caseId),
        ),
      )
      .limit(1)
      .for("update");
    if (!existing) {
      return { ok: false, notFound: true };
    }
    if (
      existing.state !== "OPEN" &&
      existing.state !== "UNDER_INVESTIGATION"
    ) {
      return { ok: false, stateNotAllowed: true };
    }

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-evidence:${caseId}:${randomUUID()}`,
      action: "FINCRIME_EVIDENCE_ADD_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        evidenceType,
        opaqueRef: opaqueRef ?? null, // opaque ref (no PII)
        hasNote: Boolean(note), // presence flag only — NO raw note text
        actorRole,
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    const ev = await this.insertEvidence({
      tenantId,
      caseId,
      evidenceType,
      opaqueRef: opaqueRef ?? null,
      note: note ?? null,
      actorUserId,
      actorRole,
      nowIso,
      auditChainEntryId,
    });
    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: EVIDENCE_ADDED_EVENT,
      fromState: null,
      toState: null,
      actorUserId,
      actorRole,
      payloadExtra: {
        evidenceType,
        evidenceId: ev.evidenceId,
        evidencePayloadHash: ev.payloadHash,
        opaqueRef: opaqueRef ?? null,
      },
      nowIso,
      auditChainEntryId,
    });

    return { ok: true, case: existing, evidenceId: ev.evidenceId };
  }

  async escalateFincrimeCaseToMlcoReview(
    args: EscalateFincrimeCaseArgs,
  ): Promise<FincrimeMutationResult> {
    const { tenantId, actorUserId, actorRole, caseId, summary } = args;
    const edge = assertM2Transition("ESCALATE_TO_MLCO_REVIEW", actorRole);
    const nowIso = new Date().toISOString();

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-escalate:${caseId}:${randomUUID()}`,
      action: "FINCRIME_ESCALATE_MLCO_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        action: edge.action,
        fromState: edge.from,
        toState: edge.to,
        actorRole,
        createsEvidence: true, // required summary → encrypted evidence; NO raw summary here
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    const updated = await db
      .update(fincrimeCases)
      .set({ state: edge.to, updatedAt: new Date() })
      .where(
        and(
          eq(fincrimeCases.tenantId, tenantId),
          eq(fincrimeCases.id, caseId),
          eq(fincrimeCases.state, edge.from),
        ),
      )
      .returning();
    if (updated.length === 0) {
      return { ok: false, conflict: true };
    }
    const newCase = updated[0];

    // Required summary → encrypted INVESTIGATION_NOTE evidence in the SAME tenant
    // tx, so a failed evidence insert rolls back the transition — an escalation
    // never lands without its rationale.
    const ev = await this.insertEvidence({
      tenantId,
      caseId,
      evidenceType: "INVESTIGATION_NOTE",
      opaqueRef: null,
      note: summary,
      actorUserId,
      actorRole,
      nowIso,
      auditChainEntryId,
    });

    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: edge.eventType,
      fromState: edge.from,
      toState: edge.to,
      actorUserId,
      actorRole,
      payloadExtra: { createsEvidence: true, evidenceId: ev.evidenceId },
      nowIso,
      auditChainEntryId,
    });
    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: EVIDENCE_ADDED_EVENT,
      fromState: null,
      toState: null,
      actorUserId,
      actorRole,
      payloadExtra: {
        evidenceType: "INVESTIGATION_NOTE",
        evidenceId: ev.evidenceId,
        evidencePayloadHash: ev.payloadHash,
        viaAction: edge.action,
      },
      nowIso,
      auditChainEntryId,
    });

    return { ok: true, case: newCase, evidenceId: ev.evidenceId };
  }

  // ── AEGIS SOAR M3 CHUNK 2 — LOCKED-DOOR STR FINALIZER ──────────────────────
  // R1 (maker): prepare an STR for a case in PENDING_MLCO_REVIEW. The case row is
  // locked (FOR UPDATE) for the life of the request tx so concurrent prepares (and
  // a concurrent finalize) serialize on the SAME row — exactly one active STR can
  // exist per case without a new unique index. The narrative is encrypted at rest
  // (AES-256-GCM, tenant key); only narrative_hash (SHA-256 of the PLAINTEXT — the
  // value the checker re-derives from the decrypted narrative and echoes back at
  // finalize) is stored in the clear as tamper-evidence. No FIA ack fields are set:
  // status PREPARED ⇒ fsr_no_fabricated_ack keeps fia_reference/filed_at/
  // acknowledged_at NULL (criterion 1). Attempt-before-effect audit precedes the writes.
  async prepareStr(args: PrepareStrArgs): Promise<PrepareStrResult> {
    const { tenantId, actorUserId, actorRole, caseId, narrative } = args;

    // Invariant: must run INSIDE the request tenant tx (RLS scoping + the FOR
    // UPDATE lock both depend on it). A breach is server-side → plain Error (500),
    // never the happy path.
    const activeTenantId = getActiveTenantId();
    if (!activeTenantId) {
      throw new Error(
        "prepareStr invariant: no active request tenant transaction.",
      );
    }
    if (activeTenantId !== tenantId) {
      throw new Error(
        `prepareStr invariant: tenant context mismatch — active "${activeTenantId}" vs "${tenantId}".`,
      );
    }

    // Defence-in-depth role gate (requireRole gates first at the route). A breach
    // here means the route guard was bypassed → workflow invariant (500).
    if (actorRole !== "admin" && actorRole !== "risk_manager") {
      throw new StrWorkflowError(
        "ROLE_NOT_AUTHORISED",
        `role '${actorRole}' not authorised to prepare an STR`,
      );
    }

    const nowIso = new Date().toISOString();

    // Lock the case row; require PENDING_MLCO_REVIEW. Absent/cross-tenant ⇒ 404
    // (RLS hides a sibling-tenant case, so it reads as absent).
    const [existing] = await db
      .select()
      .from(fincrimeCases)
      .where(
        and(eq(fincrimeCases.tenantId, tenantId), eq(fincrimeCases.id, caseId)),
      )
      .limit(1)
      .for("update");
    if (!existing) {
      throw new StrGuardError("NOT_FOUND", `case ${caseId} not found`);
    }
    if (existing.state !== "PENDING_MLCO_REVIEW") {
      throw new StrGuardError(
        "STATE_CONFLICT",
        `case ${caseId} is ${existing.state}, not PENDING_MLCO_REVIEW`,
      );
    }

    // One active STR per case. A NOT_FILED STR is not "active" and does not block a
    // fresh prepare; any other status (PREPARED/FILED/ACKNOWLEDGED) does. Race-free
    // because we hold the case row lock.
    const reports = await db
      .select()
      .from(fincrimeStrReports)
      .where(
        and(
          eq(fincrimeStrReports.tenantId, tenantId),
          eq(fincrimeStrReports.caseId, caseId),
        ),
      );
    if (reports.some((r) => r.status !== "NOT_FILED")) {
      throw new StrGuardError(
        "ALREADY_PREPARED",
        `case ${caseId} already has an active STR`,
      );
    }

    const narrativeHash = createHash("sha256")
      .update(narrative, "utf8")
      .digest("hex");

    // Attempt-before-effect audit — no raw narrative, only the hash + presence flag.
    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-str-prepare:${caseId}:${randomUUID()}`,
      action: "FINCRIME_STR_PREPARE_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        action: "PREPARE_STR",
        narrativeHash,
        actorRole,
        hasNarrative: true,
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    // Writes. 10-year minimum retention; retention_until far in the future
    // (fsr_retention_min_years + fsr_retention_future).
    const strReportId = randomUUID();
    const retentionUntil = new Date();
    retentionUntil.setFullYear(retentionUntil.getFullYear() + 10);
    const encryptedNarrative = await encryptPii(narrative, {
      type: "tenant",
      tenantId,
    });

    await db.insert(fincrimeStrReports).values({
      id: strReportId,
      tenantId,
      caseId,
      status: "PREPARED",
      subjectRef: existing.subjectRef, // COPIED from the case (opaque; no raw PII)
      subjectRefKind: existing.subjectRefKind,
      encryptedNarrative,
      narrativeHash,
      fiaReference: null,
      filedAt: null,
      acknowledgedAt: null,
      preparedByUserId: actorUserId,
      preparedByRole: actorRole,
      retentionYears: 10,
      retentionUntil,
      auditChainEntryId,
    });

    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: STR_PREPARED_EVENT,
      fromState: null,
      toState: null,
      actorUserId,
      actorRole,
      payloadExtra: { strReportId, narrativeHash, status: "PREPARED" },
      nowIso,
      auditChainEntryId,
    });

    return { strReportId, narrativeHash, status: "PREPARED" };
  }

  // R2 (checker): THE LOCKED DOOR. Approve the prepared STR (two-eyes) AND dispose
  // the case PENDING_MLCO_REVIEW → DISPOSITIONED in ONE request tenant tx. Mirrors
  // recordApprovalAndFinalize: every read + guard runs FIRST; the writes (evidence
  // → approval → case CAS → events) run LAST with NO 4xx branch between them, so any
  // throw aborts the per-request tx and rolls ALL of them back. The fsa
  // (tenant_id, str_report_id) UNIQUE is the concurrency backstop: a second
  // concurrent finalize fails the approval insert and rolls back.
  async finalizeStr(args: FinalizeStrArgs): Promise<FinalizeStrResult> {
    const { tenantId, actorUserId, actorRole, caseId, reportHash, reason } =
      args;

    // Invariant: must run INSIDE the request tenant tx (atomicity of the
    // writes-last block + RLS both depend on it). Server-side breach → Error (500).
    const activeTenantId = getActiveTenantId();
    if (!activeTenantId) {
      throw new Error(
        "finalizeStr invariant: no active request tenant transaction — the locked-door " +
          "finalize must run inside the request tenant tx (atomicity + RLS).",
      );
    }
    if (activeTenantId !== tenantId) {
      throw new Error(
        `finalizeStr invariant: tenant context mismatch — active "${activeTenantId}" vs "${tenantId}".`,
      );
    }

    // The M3 chokepoint: derives the target state from the action (never the
    // payload) + a defence-in-depth role check behind requireRole. Throws
    // StrWorkflowError (→ 500) on a breach the route guard should have stopped.
    const edge = assertM3FinalizeTransition("FINALIZE_STR", actorRole);

    const nowIso = new Date().toISOString();

    // 1. Lock the case row; require PENDING_MLCO_REVIEW. The lock also closes the
    //    finalize/finalize TOCTOU: a second finalize blocks here until the first
    //    commits, then observes DISPOSITIONED → STATE_CONFLICT (409).
    const [existing] = await db
      .select()
      .from(fincrimeCases)
      .where(
        and(eq(fincrimeCases.tenantId, tenantId), eq(fincrimeCases.id, caseId)),
      )
      .limit(1)
      .for("update");
    if (!existing) {
      throw new StrGuardError("NOT_FOUND", `case ${caseId} not found`);
    }
    if (existing.state !== edge.from) {
      throw new StrGuardError(
        "STATE_CONFLICT",
        `case ${caseId} is ${existing.state}, not ${edge.from}`,
      );
    }

    // 2. Load the PREPARED STR for this case. None ⇒ a case cannot be disposed
    //    STR_RECOMMENDED without a prepared report (409).
    const [report] = await db
      .select()
      .from(fincrimeStrReports)
      .where(
        and(
          eq(fincrimeStrReports.tenantId, tenantId),
          eq(fincrimeStrReports.caseId, caseId),
          eq(fincrimeStrReports.status, "PREPARED"),
        ),
      )
      .orderBy(desc(fincrimeStrReports.createdAt))
      .limit(1);
    if (!report) {
      throw new StrGuardError(
        "NO_PREPARED_STR",
        `case ${caseId} has no PREPARED STR to finalize`,
      );
    }

    // 3. GUARDS (all before any write).
    //    (a) Two-eyes: the checker must be a DIFFERENT actor than the preparer.
    if (report.preparedByUserId === actorUserId) {
      throw new StrGuardError(
        "SELF_APPROVAL",
        "the STR preparer may not also approve it (two-eyes)",
      );
    }
    //    (b) Anti-stale: the checker must echo the hash of the narrative they
    //        reviewed; it must equal the report's stored narrative_hash.
    if (!report.narrativeHash || report.narrativeHash !== reportHash) {
      throw new StrGuardError(
        "STALE_HASH",
        "reportHash does not match the report's current narrative_hash",
      );
    }
    //    (c) Existing approval ⇒ already finalized (friendly 409; the unique index
    //        is the structural guarantee for the concurrent race at write time).
    const [priorApproval] = await db
      .select()
      .from(fincrimeStrApprovals)
      .where(
        and(
          eq(fincrimeStrApprovals.tenantId, tenantId),
          eq(fincrimeStrApprovals.strReportId, report.id),
        ),
      )
      .limit(1);
    if (priorApproval) {
      throw new StrGuardError(
        "ALREADY_APPROVED",
        `STR ${report.id} already has a recorded approval`,
      );
    }

    // 4. Attempt-before-effect audit — no raw narrative/reason; only hashes + flags.
    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `fincrime-str-finalize:${caseId}:${randomUUID()}`,
      action: "FINCRIME_STR_FINALIZE_ATTEMPT",
      userId: actorUserId,
      resource: `fincrime_case:${caseId}`,
      details: JSON.stringify({
        tenantId,
        caseId,
        strReportId: report.id,
        action: edge.action,
        fromState: edge.from,
        toState: edge.to,
        disposition: STR_FINALIZE_DISPOSITION,
        reportHash,
        actorRole,
        hasReason: true, // presence flag only — NO raw reason text
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    // 5. WRITES LAST — in order, NO 4xx branch between them (all-or-nothing on the
    //    request tx). (i) the rationale is encrypted at rest as an evidence row
    //    (never raw, never in audit/event payloads); (ii) the approval row records
    //    two-eyes + the validated report hash; (iii) the case CAS flips to
    //    DISPOSITIONED with the HARD-DERIVED disposition; (iv) the events.
    const ev = await this.insertEvidence({
      tenantId,
      caseId,
      evidenceType: "INVESTIGATION_NOTE",
      opaqueRef: null,
      note: reason, // encrypted inside insertEvidence (AES-256-GCM, tenant key)
      actorUserId,
      actorRole,
      nowIso,
      auditChainEntryId,
    });

    const approvalId = randomUUID();
    await db.insert(fincrimeStrApprovals).values({
      id: approvalId,
      tenantId,
      strReportId: report.id,
      caseId,
      approverUserId: actorUserId,
      approverRole: actorRole,
      approvalType: "MLCO_AUTHORITY",
      reportHash, // the validated arg (== report.narrativeHash, checked above)
      reason: `evidence:${ev.evidenceId}`, // non-PII pointer to the encrypted rationale
      auditChainEntryId,
    });

    const [updatedCase] = await db
      .update(fincrimeCases)
      .set({
        state: edge.to,
        disposition: STR_FINALIZE_DISPOSITION, // hard-derived, NEVER from payload
        dispositionReason: `evidence:${ev.evidenceId}`, // non-PII pointer
        dispositionedAt: new Date(),
        strFiled: false, // RECOMMENDED is NOT FILED — co-occurring flag stays false
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(fincrimeCases.tenantId, tenantId),
          eq(fincrimeCases.id, caseId),
          eq(fincrimeCases.state, edge.from),
        ),
      )
      .returning();
    if (!updatedCase) {
      // We hold the FOR UPDATE lock and verified state == edge.from, so the CAS
      // MUST match. 0 rows here is a server-side invariant breach → 500 (rolls the
      // whole writes-last block back), never a client 4xx.
      throw new Error(
        `finalizeStr invariant: CAS matched 0 rows for case ${caseId} despite a held row lock.`,
      );
    }

    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: edge.eventType, // CASE_DISPOSITIONED
      fromState: edge.from,
      toState: edge.to,
      actorUserId,
      actorRole,
      payloadExtra: {
        strReportId: report.id,
        approvalId,
        disposition: STR_FINALIZE_DISPOSITION,
      },
      nowIso,
      auditChainEntryId,
    });
    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: STR_APPROVED_EVENT,
      fromState: null,
      toState: null,
      actorUserId,
      actorRole,
      payloadExtra: { strReportId: report.id, approvalId, reportHash },
      nowIso,
      auditChainEntryId,
    });
    await this.insertCaseEvent({
      tenantId,
      caseId,
      eventType: EVIDENCE_ADDED_EVENT,
      fromState: null,
      toState: null,
      actorUserId,
      actorRole,
      payloadExtra: {
        evidenceType: "INVESTIGATION_NOTE",
        evidenceId: ev.evidenceId,
        evidencePayloadHash: ev.payloadHash,
        viaAction: edge.action,
      },
      nowIso,
      auditChainEntryId,
    });

    return {
      caseId,
      state: edge.to,
      disposition: STR_FINALIZE_DISPOSITION,
      strReportId: report.id,
      approvalId,
    };
  }

  // R3 (scoped read): return the case's STR with the narrative DECRYPTED for an
  // authorized reader (the route restricts to admin/risk_manager/auditor — the
  // tipping-off control). Tenant-scoped (RLS + explicit predicate); a sibling
  // tenant reads 0 rows ⇒ null ⇒ 404 (cross-tenant invisibility).
  async getStrForCase(args: GetStrArgs): Promise<StrReadView | null> {
    const { tenantId, caseId } = args;
    const [report] = await db
      .select()
      .from(fincrimeStrReports)
      .where(
        and(
          eq(fincrimeStrReports.tenantId, tenantId),
          eq(fincrimeStrReports.caseId, caseId),
        ),
      )
      .orderBy(desc(fincrimeStrReports.createdAt))
      .limit(1);
    if (!report) {
      return null;
    }
    const narrative = report.encryptedNarrative
      ? await decryptPii(report.encryptedNarrative, { type: "tenant", tenantId })
      : null;
    return {
      id: report.id,
      caseId: report.caseId,
      status: report.status,
      subjectRef: report.subjectRef,
      subjectRefKind: report.subjectRefKind,
      narrative,
      narrativeHash: report.narrativeHash,
      fiaReference: report.fiaReference,
      filedAt: report.filedAt,
      acknowledgedAt: report.acknowledgedAt,
      preparedByUserId: report.preparedByUserId,
      preparedByRole: report.preparedByRole,
      retentionUntil: report.retentionUntil,
      createdAt: report.createdAt,
    };
  }

  // ── AEGIS SOAR M3 CHUNK 3 — goAML Form-B export SOURCE (read-only) ───────────
  // Reads ONLY the form-relevant columns from the disposed case + its PREPARED STR +
  // the MLCO approval, decrypts ONLY the narrative, and returns them as FormBSource.
  // The select lists are explicit (no internal user ids / hashes / ciphertext / audit
  // ids ever leave storage) — criterion 3 ("only form-required PII") by construction.
  // Returns null UNLESS the case is DISPOSITIONED with disposition STR_RECOMMENDED and
  // carries a PREPARED STR with a decryptable narrative — a read-side precondition
  // (refuse-to-render, never a write). Tenant-scoped (RLS + explicit predicate); a
  // sibling tenant reads 0 rows ⇒ null ⇒ 404 (no cross-tenant oracle).
  async getFincrimeFormBSource(args: GetFormBArgs): Promise<FormBSource | null> {
    const { tenantId, caseId } = args;
    const [c] = await db
      .select({
        subjectRef: fincrimeCases.subjectRef,
        subjectRefKind: fincrimeCases.subjectRefKind,
        sourceType: fincrimeCases.sourceType,
        state: fincrimeCases.state,
        disposition: fincrimeCases.disposition,
        dispositionedAt: fincrimeCases.dispositionedAt,
        strFiled: fincrimeCases.strFiled,
      })
      .from(fincrimeCases)
      .where(and(eq(fincrimeCases.tenantId, tenantId), eq(fincrimeCases.id, caseId)))
      .limit(1);
    if (!c || c.state !== "DISPOSITIONED" || c.disposition !== "STR_RECOMMENDED" || !c.dispositionedAt) {
      return null;
    }

    const [r] = await db
      .select({
        id: fincrimeStrReports.id,
        status: fincrimeStrReports.status,
        encryptedNarrative: fincrimeStrReports.encryptedNarrative,
        createdAt: fincrimeStrReports.createdAt,
        fiaReference: fincrimeStrReports.fiaReference,
        filedAt: fincrimeStrReports.filedAt,
        acknowledgedAt: fincrimeStrReports.acknowledgedAt,
        preparedByRole: fincrimeStrReports.preparedByRole,
      })
      .from(fincrimeStrReports)
      .where(and(eq(fincrimeStrReports.tenantId, tenantId), eq(fincrimeStrReports.caseId, caseId)))
      .orderBy(desc(fincrimeStrReports.createdAt))
      .limit(1);
    if (!r || r.status !== "PREPARED" || !r.encryptedNarrative) {
      return null;
    }
    const narrative = await decryptPii(r.encryptedNarrative, { type: "tenant", tenantId });
    if (!narrative) {
      return null;
    }

    const [a] = await db
      .select({
        approverRole: fincrimeStrApprovals.approverRole,
        approvedAt: fincrimeStrApprovals.approvedAt,
      })
      .from(fincrimeStrApprovals)
      // Bind the approval to THE selected STR report (str_report_id = r.id), not merely
      // the case, so the rendered two-eyes provenance is exact: the approval shown is the
      // one for the report being rendered. (Today one STR per case makes case-scope
      // sufficient, but binding to the report id keeps it correct if that ever changes.)
      .where(
        and(
          eq(fincrimeStrApprovals.tenantId, tenantId),
          eq(fincrimeStrApprovals.caseId, caseId),
          eq(fincrimeStrApprovals.strReportId, r.id),
        ),
      )
      .orderBy(desc(fincrimeStrApprovals.approvedAt))
      .limit(1);
    if (!a) {
      return null;
    }

    return {
      subjectRef: c.subjectRef,
      subjectRefKind: c.subjectRefKind,
      sourceType: c.sourceType,
      disposition: c.disposition,
      dispositionedAt: c.dispositionedAt,
      strFiled: c.strFiled,
      strReportId: r.id,
      strStatus: r.status,
      narrative,
      strCreatedAt: r.createdAt,
      fiaReference: r.fiaReference,
      filedAt: r.filedAt,
      acknowledgedAt: r.acknowledgedAt,
      preparedByRole: r.preparedByRole,
      approverRole: a.approverRole,
      approvedAt: a.approvedAt,
    };
  }

  // ── AEGIS SOAR M4 — regulator-facing SOAR surface (read-only) ────────────────
  // Safe-columns case list/detail (non-PII; no actor / source internals) + the
  // integrity inputs for the per-case tamper-evidence check. ALL read under the
  // request tenant context (RLS) via the global `db` proxy — the route wraps these
  // in runWithTenantContext(tenantId), exactly like the KYC examiner surface. No
  // write, no decrypt. The explicit tenant predicate is defence-in-depth atop RLS.
  async listFincrimeCasesForRegulator(
    tenantId: string,
    opts?: { limit?: number },
  ): Promise<RegulatorCaseRefRow[]> {
    if (!tenantId) throw new Error("listFincrimeCasesForRegulator: tenantId required");
    const base = db
      .select({
        id: fincrimeCases.id,
        subjectRefKind: fincrimeCases.subjectRefKind,
        sourceType: fincrimeCases.sourceType,
        state: fincrimeCases.state,
        disposition: fincrimeCases.disposition,
        strFiled: fincrimeCases.strFiled,
        openedAt: fincrimeCases.openedAt,
        dispositionedAt: fincrimeCases.dispositionedAt,
      })
      .from(fincrimeCases)
      .where(eq(fincrimeCases.tenantId, tenantId))
      .orderBy(desc(fincrimeCases.openedAt));
    return typeof opts?.limit === "number" && opts.limit > 0
      ? await base.limit(opts.limit)
      : await base;
  }

  async getFincrimeCaseForRegulator(
    caseId: string,
    tenantId: string,
  ): Promise<RegulatorCaseRefRow | undefined> {
    if (!tenantId) throw new Error("getFincrimeCaseForRegulator: tenantId required");
    const [row] = await db
      .select({
        id: fincrimeCases.id,
        subjectRefKind: fincrimeCases.subjectRefKind,
        sourceType: fincrimeCases.sourceType,
        state: fincrimeCases.state,
        disposition: fincrimeCases.disposition,
        strFiled: fincrimeCases.strFiled,
        openedAt: fincrimeCases.openedAt,
        dispositionedAt: fincrimeCases.dispositionedAt,
      })
      .from(fincrimeCases)
      .where(and(eq(fincrimeCases.tenantId, tenantId), eq(fincrimeCases.id, caseId)))
      .limit(1);
    return row ?? undefined;
  }

  async getFincrimeCaseIntegrityInputs(
    caseId: string,
    tenantId: string,
  ): Promise<FincrimeCaseIntegrityInput | null> {
    if (!tenantId) throw new Error("getFincrimeCaseIntegrityInputs: tenantId required");
    const [c] = await db
      .select({
        id: fincrimeCases.id,
        state: fincrimeCases.state,
        disposition: fincrimeCases.disposition,
        auditChainEntryId: fincrimeCases.auditChainEntryId,
      })
      .from(fincrimeCases)
      .where(and(eq(fincrimeCases.tenantId, tenantId), eq(fincrimeCases.id, caseId)))
      .limit(1);
    if (!c) return null;

    const events = await db
      .select({
        id: fincrimeCaseEvents.id,
        eventType: fincrimeCaseEvents.eventType,
        auditChainEntryId: fincrimeCaseEvents.auditChainEntryId,
      })
      .from(fincrimeCaseEvents)
      .where(and(eq(fincrimeCaseEvents.tenantId, tenantId), eq(fincrimeCaseEvents.caseId, caseId)))
      .orderBy(asc(fincrimeCaseEvents.createdAt), asc(fincrimeCaseEvents.id));

    const [strRow] = await db
      .select({
        id: fincrimeStrReports.id,
        status: fincrimeStrReports.status,
        narrativeHash: fincrimeStrReports.narrativeHash,
        auditChainEntryId: fincrimeStrReports.auditChainEntryId,
        tenantId: fincrimeStrReports.tenantId,
        caseId: fincrimeStrReports.caseId,
        fiaReference: fincrimeStrReports.fiaReference,
        filedAt: fincrimeStrReports.filedAt,
        acknowledgedAt: fincrimeStrReports.acknowledgedAt,
        preparedByRole: fincrimeStrReports.preparedByRole,
      })
      .from(fincrimeStrReports)
      .where(and(eq(fincrimeStrReports.tenantId, tenantId), eq(fincrimeStrReports.caseId, caseId)))
      .orderBy(desc(fincrimeStrReports.createdAt))
      .limit(1);

    let approval: FincrimeCaseIntegrityInput["approval"] = null;
    if (strRow) {
      const [appRow] = await db
        .select({
          id: fincrimeStrApprovals.id,
          strReportId: fincrimeStrApprovals.strReportId,
          caseId: fincrimeStrApprovals.caseId,
          tenantId: fincrimeStrApprovals.tenantId,
          reportHash: fincrimeStrApprovals.reportHash,
          auditChainEntryId: fincrimeStrApprovals.auditChainEntryId,
          approverRole: fincrimeStrApprovals.approverRole,
          approvedAt: fincrimeStrApprovals.approvedAt,
        })
        .from(fincrimeStrApprovals)
        .where(
          and(
            eq(fincrimeStrApprovals.tenantId, tenantId),
            eq(fincrimeStrApprovals.caseId, caseId),
            eq(fincrimeStrApprovals.strReportId, strRow.id),
          ),
        )
        .orderBy(desc(fincrimeStrApprovals.approvedAt))
        .limit(1);
      approval = appRow ?? null;
    }

    return {
      caseId: c.id,
      tenantId,
      caseState: c.state,
      disposition: c.disposition,
      caseAuditChainEntryId: c.auditChainEntryId,
      events,
      str: strRow ?? null,
      approval,
    };
  }

  // ── AEGIS SECURITY IR (SECURITY_IR_WORKFLOW_SCOPE.md) — Layer 1 + Layer-2 seam ─
  //
  // Modelled on the fincrime case-spine mutators above. Shared invariants:
  //   (1) Audit ATTEMPT is chained via auditDb (independent pool) BEFORE the
  //       durable write, so a rolled-back tx still leaves an honest attempt record.
  //       Audit `details` carry ONLY non-PII structural fields (tenant, incident id,
  //       action, from/to state, severity, role) — never raw note text.
  //   (2) Transitions use a compare-and-set UPDATE … WHERE tenant_id+id+status=
  //       expectedFrom RETURNING. 0 rows ⇒ conflict (absent / wrong-state /
  //       cross-tenant collapse uniformly to a 409 — no oracle).
  //   (3) The transition + the timeline event row execute inside the one
  //       withTenantDbPhase tenant tx (RLS-scoped) the route wraps the call in, so
  //       they commit atomically; tenant_id originates from the authenticated actor,
  //       never the request payload (RLS WITH CHECK enforces GUC == row tenant).
  // NOTE: storage methods use the module-level `db` and rely on the ambient GUC set
  // by the route's withTenantDbPhase wrapper, mirroring the fincrime mutators.

  // Insert a security_incident_events timeline row (append-only). The note is
  // operator free-text (no subject PII); fromState/toState capture the transition.
  private async insertSecurityIncidentEvent(p: {
    tenantId: string;
    incidentId: string;
    eventType: string;
    fromState: string | null;
    toState: string | null;
    actorUserId: string | null;
    note: string | null;
    auditChainEntryId: number | null;
  }): Promise<void> {
    await db.insert(securityIncidentEvents).values({
      id: randomUUID(),
      tenantId: p.tenantId,
      incidentId: p.incidentId,
      eventType: p.eventType,
      fromState: p.fromState,
      toState: p.toState,
      actorUserId: p.actorUserId,
      note: p.note,
      auditChainEntryId: p.auditChainEntryId,
    });
  }

  // Open a security incident — manual OR from an (currently synthetic) EDR/SIEM
  // alert. The originatingAlert* fields wire the real Layer-2 INTERFACE (alert id +
  // source); the alert DATA is synthetic until APT-make-real, so nothing here
  // changes when the feed becomes real. Audit-chained CASE create + INCIDENT_OPENED
  // event, mirroring fincrime ingestFincrimeSignal.
  async openSecurityIncident(
    args: OpenSecurityIncidentArgs,
  ): Promise<SecurityIncident> {
    const incidentId = randomUUID();
    const nowIso = new Date().toISOString();
    const slaTargetMinutes = slaTargetForSeverity(args.severity);

    // (1) Audit ATTEMPT BEFORE the insert (tamper-evident ordering): committed via
    // the independent auditDb path inside chainAuditLog. Dynamic import mirrors the
    // fincrime mutators (avoids a circular dep). details carry NO raw note text.
    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `security-ir-open:${incidentId}`,
      action: "SECURITY_INCIDENT_OPEN_ATTEMPT",
      userId: args.actorUserId,
      resource: `security_incident:${incidentId}`,
      details: JSON.stringify({
        tenantId: args.tenantId,
        incidentId,
        severity: args.severity,
        slaTargetMinutes,
        originatingAlertRef: args.originatingAlertRef ?? null,
        originatingAlertSource: args.originatingAlertSource ?? null,
        actorRole: args.actorRole,
        hasNote: Boolean(args.note), // presence flag only — NO raw note text
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    // (2) Case insert. tenant_id from the authenticated actor (RLS WITH CHECK
    // enforces it equals the GUC tenant).
    const [incident] = await db
      .insert(securityIncidents)
      .values({
        id: incidentId,
        tenantId: args.tenantId,
        title: args.title,
        severity: args.severity,
        status: "OPEN",
        originatingAlertRef: args.originatingAlertRef ?? null,
        originatingAlertSource: args.originatingAlertSource ?? null,
        slaTargetMinutes,
        auditChainEntryId,
      })
      .returning();

    // (3) INCIDENT_OPENED event in the SAME tenant tx (exercises the tenant-bound
    // composite FK). from_state null (creation) → to_state OPEN.
    await this.insertSecurityIncidentEvent({
      tenantId: args.tenantId,
      incidentId: incident.id,
      eventType: INCIDENT_OPENED_EVENT,
      fromState: null,
      toState: "OPEN",
      actorUserId: args.actorUserId,
      note: args.note ?? null,
      auditChainEntryId,
    });

    return incident;
  }

  async listSecurityIncidentsForQueue(
    args: ListSecurityIncidentsArgs,
  ): Promise<SecurityIncident[]> {
    const { tenantId, actorUserId, queue } = args;
    // RLS already scopes to the tenant; the explicit tenant predicate is
    // belt-and-suspenders, matching the fincrime read pattern.
    const base = eq(securityIncidents.tenantId, tenantId);
    let predicate;
    switch (queue) {
      case "open":
        predicate = and(base, eq(securityIncidents.status, "OPEN"));
        break;
      case "mine":
        predicate = and(
          base,
          eq(securityIncidents.assignedTo, actorUserId),
          ne(securityIncidents.status, "CLOSED"),
        );
        break;
      case "active":
        predicate = and(base, ne(securityIncidents.status, "CLOSED"));
        break;
      case "all":
      default:
        predicate = base;
        break;
    }
    return db
      .select()
      .from(securityIncidents)
      .where(predicate)
      .orderBy(desc(securityIncidents.openedAt));
  }

  async getSecurityIncident(
    args: GetSecurityIncidentArgs,
  ): Promise<SecurityIncident | undefined> {
    const { tenantId, incidentId } = args;
    if (!tenantId) throw new Error("getSecurityIncident: tenantId required");
    const [incident] = await db
      .select()
      .from(securityIncidents)
      .where(
        and(
          eq(securityIncidents.tenantId, tenantId),
          eq(securityIncidents.id, incidentId),
        ),
      )
      .limit(1);
    return incident;
  }

  // Tenant-scoped, append-only event timeline for an incident (ascending = chronological).
  // Read for the IR case-detail UI. Composite-FK guarantees events belong to a tenant's incident.
  async listSecurityIncidentEvents(args: { tenantId: string; incidentId: string }): Promise<SecurityIncidentEvent[]> {
    const { tenantId, incidentId } = args;
    if (!tenantId) throw new Error("listSecurityIncidentEvents: tenantId required");
    return db
      .select()
      .from(securityIncidentEvents)
      .where(and(eq(securityIncidentEvents.tenantId, tenantId), eq(securityIncidentEvents.incidentId, incidentId)))
      .orderBy(securityIncidentEvents.createdAt);
  }

  // Tenant-scoped containment actions for an incident. Each carries a real `status`
  // (defaults 'SIMULATED' until a real effector lands) — the UI renders that honestly.
  async listSecurityIncidentActions(args: { tenantId: string; incidentId: string }): Promise<SecurityIncidentAction[]> {
    const { tenantId, incidentId } = args;
    if (!tenantId) throw new Error("listSecurityIncidentActions: tenantId required");
    return db
      .select()
      .from(securityIncidentActions)
      .where(and(eq(securityIncidentActions.tenantId, tenantId), eq(securityIncidentActions.incidentId, incidentId)))
      .orderBy(securityIncidentActions.createdAt);
  }

  // Advance an incident through the OPEN → TRIAGED → CONTAINED → ERADICATED →
  // RECOVERED → CLOSED lifecycle. The action (and thus the target state) is derived
  // by assertIrTransition from the route-path action — NEVER named by the client.
  // CAS on (tenant_id, id, status) so a 0-row match is a 409 state-precondition.
  async transitionSecurityIncident(
    args: TransitionSecurityIncidentArgs,
  ): Promise<SecurityIncidentMutationResult> {
    const { tenantId, actorUserId, actorRole, incidentId, action, note } = args;
    const edge = assertIrTransition(action, actorRole);
    const nowIso = new Date().toISOString();

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `security-ir-transition:${incidentId}:${randomUUID()}`,
      action: "SECURITY_INCIDENT_TRANSITION_ATTEMPT",
      userId: actorUserId,
      resource: `security_incident:${incidentId}`,
      details: JSON.stringify({
        tenantId,
        incidentId,
        action: edge.action,
        fromState: edge.from,
        toState: edge.to,
        actorRole,
        hasNote: Boolean(note), // presence flag only — NO raw note text
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    // Folded SLA timestamps advance with the lifecycle (one system of record):
    //   TRIAGED  → acknowledgedAt (MTTA basis)
    //   RECOVERED→ resolvedAt (MTTR basis)
    //   CLOSED   → closedAt (the sec_inc_closed_at_valid CHECK requires it).
    const now = new Date();
    const setFields: Record<string, unknown> = {
      status: edge.to,
      updatedAt: now,
    };
    if (edge.to === "TRIAGED") setFields.acknowledgedAt = now;
    if (edge.to === "RECOVERED") setFields.resolvedAt = now;
    if (edge.to === "CLOSED") setFields.closedAt = now;

    const updated = await db
      .update(securityIncidents)
      .set(setFields)
      .where(
        and(
          eq(securityIncidents.tenantId, tenantId),
          eq(securityIncidents.id, incidentId),
          eq(securityIncidents.status, edge.from),
        ),
      )
      .returning();
    if (updated.length === 0) {
      return { ok: false, conflict: true };
    }

    await this.insertSecurityIncidentEvent({
      tenantId,
      incidentId,
      eventType: edge.eventType,
      fromState: edge.from,
      toState: edge.to,
      actorUserId,
      note: note ?? null,
      auditChainEntryId,
    });

    return { ok: true, incident: updated[0] };
  }

  // Assign an incident to a responder (no state change). Audit-chained; the
  // assignment is recorded as an INCIDENT_ASSIGNED timeline event.
  async assignSecurityIncident(
    args: AssignSecurityIncidentArgs,
  ): Promise<SecurityIncidentMutationResult> {
    const { tenantId, actorUserId, actorRole, incidentId, assigneeUserId } = args;
    const nowIso = new Date().toISOString();

    const { chainAuditLog } = await import("./lib/audit-chain");
    const chained = await chainAuditLog({
      id: `security-ir-assign:${incidentId}:${randomUUID()}`,
      action: "SECURITY_INCIDENT_ASSIGN_ATTEMPT",
      userId: actorUserId,
      resource: `security_incident:${incidentId}`,
      details: JSON.stringify({
        tenantId,
        incidentId,
        assigneeUserId, // internal user id (non-PII)
        actorRole,
      }),
      timestamp: nowIso,
    });
    const auditChainEntryId = await this.resolveAuditChainEntryId(chained.hash);

    // Assignment is allowed in any non-CLOSED state; the CAS excludes CLOSED so a
    // closed incident cannot be reassigned (0 rows → 409).
    const updated = await db
      .update(securityIncidents)
      .set({ assignedTo: assigneeUserId, updatedAt: new Date() })
      .where(
        and(
          eq(securityIncidents.tenantId, tenantId),
          eq(securityIncidents.id, incidentId),
          ne(securityIncidents.status, "CLOSED"),
        ),
      )
      .returning();
    if (updated.length === 0) {
      return { ok: false, conflict: true };
    }

    await this.insertSecurityIncidentEvent({
      tenantId,
      incidentId,
      eventType: "INCIDENT_ASSIGNED",
      fromState: updated[0].status,
      toState: updated[0].status,
      actorUserId,
      note: null,
      auditChainEntryId,
    });

    return { ok: true, incident: updated[0] };
  }

  // ── AEGIS UEBA (UEBA_PRIVILEGED_SCOPE.md) — baselines + anomalies ────────────
  // Tenant-scoped RLS CRUD. These run inside the route's withTenantDbPhase tenant tx
  // so the GUC is set and RLS enforces; the explicit tenant_id predicate is
  // belt-and-suspenders (mirrors the IR + fincrime read pattern). The baseline
  // BUILD itself (computing profiles from real audit_logs) lives in
  // server/lib/ueba/baseline.ts; this layer is durable read/write of the two tables.

  async listUebaBaselines(tenantId: string): Promise<UebaUserBaseline[]> {
    return db
      .select()
      .from(uebaUserBaselines)
      .where(eq(uebaUserBaselines.tenantId, tenantId))
      .orderBy(desc(uebaUserBaselines.lastRebuiltAt));
  }

  // Persist ONE anomaly. The anomaly MUST cite at least one REAL audit_logs id
  // (triggeringAuditIds) — the DB CHECK (ueba_anomaly_cites_audit) refuses an
  // un-cited anomaly, so a fabricated "computed-from-nothing" anomaly is structurally
  // unstorable. tenant_id from the authenticated actor (RLS WITH CHECK enforces it
  // equals the GUC tenant).
  async insertUebaAnomaly(args: InsertUebaAnomalyArgs): Promise<UebaAnomaly> {
    if (!args.triggeringAuditIds || args.triggeringAuditIds.length === 0) {
      // Defence-in-depth above the DB CHECK: never attempt to store an un-cited
      // anomaly. Every anomaly must trace to a real audit row a human can inspect.
      throw new Error(
        "insertUebaAnomaly: an anomaly MUST cite at least one real audit_logs id " +
          "(triggeringAuditIds was empty) — refusing to store an un-cited anomaly.",
      );
    }
    const [row] = await db
      .insert(uebaAnomalies)
      .values({
        id: randomUUID(),
        tenantId: args.tenantId,
        userId: args.userId,
        rule: args.rule,
        observed: args.observed,
        expected: args.expected,
        confidence: args.confidence.toFixed(3),
        triggeringAuditIds: JSON.stringify(args.triggeringAuditIds),
        openedIncidentId: args.openedIncidentId ?? null,
      })
      .returning();
    return row;
  }

  // Stamp the opened security_incident id onto an anomaly (Layer 3 link).
  async setUebaAnomalyIncident(tenantId: string, anomalyId: string, incidentId: string): Promise<void> {
    await db
      .update(uebaAnomalies)
      .set({ openedIncidentId: incidentId })
      .where(and(eq(uebaAnomalies.tenantId, tenantId), eq(uebaAnomalies.id, anomalyId)));
  }

  async listUebaAnomalies(tenantId: string, opts?: { limit?: number }): Promise<UebaAnomaly[]> {
    const q = db
      .select()
      .from(uebaAnomalies)
      .where(eq(uebaAnomalies.tenantId, tenantId))
      .orderBy(desc(uebaAnomalies.detectedAt));
    return opts?.limit ? q.limit(opts.limit) : q;
  }

  // System Metrics
  async getLatestMetrics(): Promise<SystemMetric[]> {
    return db.select()
      .from(systemMetrics)
      .orderBy(desc(systemMetrics.timestamp))
      .limit(10);
  }

  async createMetric(metric: InsertSystemMetric): Promise<SystemMetric> {
    const [newMetric] = await db.insert(systemMetrics).values(metric).returning();
    return newMetric;
  }

  // AI Thresholds
  async getThresholds(): Promise<AIThreshold[]> {
    return db.select().from(aiThresholds);
  }

  async updateThreshold(name: string, value: number, userId: string): Promise<AIThreshold | undefined> {
    const [updated] = await db.update(aiThresholds)
      .set({ 
        thresholdValue: String(value), 
        updatedBy: userId, 
        updatedAt: new Date() 
      })
      .where(eq(aiThresholds.thresholdName, name))
      .returning();
    return updated || undefined;
  }

  // Webhooks
  async getWebhooks(): Promise<WebhookConfig[]> {
    return db.select().from(webhookConfigs).orderBy(desc(webhookConfigs.createdAt));
  }

  async getWebhookById(id: string): Promise<WebhookConfig | undefined> {
    const [webhook] = await db.select().from(webhookConfigs).where(eq(webhookConfigs.id, id));
    return webhook || undefined;
  }

  async createWebhook(config: InsertWebhookConfig): Promise<WebhookConfig> {
    // AS/PLATFORM/2026/008 Tenant-Sep Chunk B — fail-closed tenant binding.
    // tenant_id MUST come from request ctx (the GUC-matching (req).tenantId the
    // route injects), NEVER the client body; refuse an unbound write so a NULL
    // tenant can never be persisted (column is RLS-enrolled + NOT NULL in DB).
    if (!config.tenantId) {
      throw new Error("createWebhook: tenantId is required (tenant-isolation, fail-closed)");
    }
    const [webhook] = await db.insert(webhookConfigs).values(config).returning();
    return webhook;
  }

  async updateWebhook(id: string, updates: Partial<InsertWebhookConfig>): Promise<WebhookConfig | undefined> {
    // AS/PLATFORM/2026/008 Tenant-Sep Chunk B — tenant_id is immutable after
    // creation; never let an update reassign a webhook to another tenant. The
    // request-proxy RLS UPDATE is already tenant-scoped, but strip defensively
    // so a stray body field can never reach WITH CHECK.
    const { tenantId: _ignoredTenantId, ...safeUpdates } = updates;
    const [updated] = await db.update(webhookConfigs)
      .set(safeUpdates)
      .where(eq(webhookConfigs.id, id))
      .returning();
    return updated || undefined;
  }

  async deleteWebhook(id: string): Promise<void> {
    await db.delete(webhookConfigs).where(eq(webhookConfigs.id, id));
  }

  async logWebhookExecution(log: {
    tenantId: string;
    webhookId: string;
    eventType: WebhookEventType;
    payload: string;
    responseStatus?: number;
    responseBody?: string;
    executionTimeMs?: number;
    success: boolean;
    errorMessage?: string;
  }): Promise<WebhookLog> {
    // AS/PLATFORM/2026/008 Tenant-Sep Chunk B — fail-closed tenant binding.
    // tenant_id is derived from the PARENT webhook_configs row (caller passes
    // webhookConfig.tenantId), which under RLS equals current_tenant_id() on
    // the delivery request; refuse an unbound write (column NOT NULL + RLS).
    if (!log.tenantId) {
      throw new Error("logWebhookExecution: tenantId is required (tenant-isolation, fail-closed)");
    }
    const [newLog] = await db.insert(webhookLogs).values(log).returning();
    return newLog;
  }

  async getWebhookLogs(webhookId?: string, limit = 50): Promise<WebhookLog[]> {
    if (webhookId) {
      return db.select()
        .from(webhookLogs)
        .where(eq(webhookLogs.webhookId, webhookId))
        .orderBy(desc(webhookLogs.triggeredAt))
        .limit(limit);
    }
    return db.select()
      .from(webhookLogs)
      .orderBy(desc(webhookLogs.triggeredAt))
      .limit(limit);
  }

  // Ghost Core Events
  async getGhostCoreEvents(limit = 50, tenantId?: string): Promise<GhostCoreEvent[]> {
    if (tenantId) {
      return db.select()
        .from(ghostCoreEvents)
        .where(eq(ghostCoreEvents.tenantId, tenantId))
        .orderBy(desc(ghostCoreEvents.createdAt))
        .limit(limit);
    }
    return db.select()
      .from(ghostCoreEvents)
      .orderBy(desc(ghostCoreEvents.createdAt))
      .limit(limit);
  }

  async createGhostCoreEvent(event: InsertGhostCoreEvent): Promise<GhostCoreEvent> {
    const [newEvent] = await db.insert(ghostCoreEvents).values(event).returning();
    return newEvent;
  }

  // SOAR Executions
  async getSoarExecutions(limit = 50): Promise<SoarExecution[]> {
    return db.select()
      .from(soarExecutions)
      .orderBy(desc(soarExecutions.startedAt))
      .limit(limit);
  }

  async createSoarExecution(exec: InsertSoarExecution): Promise<SoarExecution> {
    const [newExec] = await db.insert(soarExecutions).values(exec).returning();
    return newExec;
  }

  async updateSoarExecution(id: string, updates: Partial<InsertSoarExecution>): Promise<SoarExecution | undefined> {
    const [updated] = await db.update(soarExecutions)
      .set(updates)
      .where(eq(soarExecutions.id, id))
      .returning();
    return updated || undefined;
  }

  // Observer Logs
  async getObserverLogs(limit = 50): Promise<ObserverLog[]> {
    return db.select()
      .from(observerLogs)
      .orderBy(desc(observerLogs.createdAt))
      .limit(limit);
  }

  async createObserverLog(log: InsertObserverLog): Promise<ObserverLog> {
    const [newLog] = await db.insert(observerLogs).values(log).returning();
    return newLog;
  }

  // Notification Logs
  async getNotificationLogs(limit = 50, channel?: string): Promise<NotificationLog[]> {
    if (channel) {
      return db.select()
        .from(notificationLogs)
        .where(eq(notificationLogs.channel, channel))
        .orderBy(desc(notificationLogs.createdAt))
        .limit(limit);
    }
    return db.select()
      .from(notificationLogs)
      .orderBy(desc(notificationLogs.createdAt))
      .limit(limit);
  }

  /**
   * @deprecated FU-107 — DO NOT use for notification side-writes. This inserts
   * notification_logs (an RLS (T) table) via the request `db` proxy: a null/
   * foreign tenant_id trips RLS WITH CHECK (42501) and silently poisons the
   * caller's per-request transaction (rolling back its durable writes). Use
   * NotificationService.writeNotificationLog (routes through auditDb, BYPASSRLS,
   * separate connection) instead. Currently unused by the notification path.
   */
  async createNotificationLog(log: InsertNotificationLog): Promise<NotificationLog> {
    const [newLog] = await db.insert(notificationLogs).values(log).returning();
    return newLog;
  }

  async updateNotificationLog(id: string, updates: Partial<InsertNotificationLog>): Promise<NotificationLog | undefined> {
    const [updated] = await db.update(notificationLogs)
      .set(updates)
      .where(eq(notificationLogs.id, id))
      .returning();
    return updated || undefined;
  }

  // Export Jobs
  async getExportJobs(limit = 50): Promise<ExportJob[]> {
    return db.select()
      .from(exportJobs)
      .orderBy(desc(exportJobs.createdAt))
      .limit(limit);
  }

  async createExportJob(job: InsertExportJob): Promise<ExportJob> {
    const [newJob] = await db.insert(exportJobs).values(job).returning();
    return newJob;
  }

  async updateExportJob(id: string, updates: Partial<InsertExportJob>): Promise<ExportJob | undefined> {
    const [updated] = await db.update(exportJobs)
      .set(updates)
      .where(eq(exportJobs.id, id))
      .returning();
    return updated || undefined;
  }

  // API Keys
  async getApiKeys(tenantId?: string): Promise<ApiKey[]> {
    if (tenantId) {
      return db.select()
        .from(apiKeys)
        .where(eq(apiKeys.tenantId, tenantId))
        .orderBy(desc(apiKeys.createdAt));
    }
    return db.select()
      .from(apiKeys)
      .orderBy(desc(apiKeys.createdAt));
  }

  async getApiKeyByHash(keyHash: string): Promise<ApiKey | undefined> {
    // D-B=B-3 (rls-init.ts §8.1; bypass register server/db.ts): api_keys is
    // RLS-enrolled, but apiKeyAuth's hash lookup runs PRE-tenant — it is the
    // first /api middleware, BEFORE tenantMiddleware sets app.current_tenant_id
    // — so under the null GUC the tenant_isolation policy hides EVERY row and
    // the plain `db` path returns undefined => every key 401s (dead-but-safe).
    // The mandated pattern is withBypassRls (COMMITs before return). Single
    // caller (apiKeyAuth); the downstream tenant context-set is already wired
    // via tenantMiddleware Source 3 (req.apiKeyAuth.tenantId), so no further
    // change is needed for the request to scope to the key's tenant.
    const key = await withBypassRls((tx) =>
      tx.select().from(apiKeys).where(eq(apiKeys.keyHash, keyHash)).then((r) => r[0]),
    );
    return key || undefined;
  }

  async createApiKey(key: InsertApiKey): Promise<ApiKey> {
    const [newKey] = await db.insert(apiKeys).values(key).returning();
    return newKey;
  }

  async updateApiKeyLastUsed(id: string): Promise<void> {
    // D-B=B-3 (same pre-tenant path as getApiKeyByHash): apiKeyAuth records
    // last-used BEFORE tenantMiddleware sets the GUC, so a plain RLS `db.update`
    // matches zero rows for a tenant-scoped key and the timestamp silently never
    // advances (false-green telemetry, Rule 15). Route the by-id update through
    // withBypassRls so the metadata write actually lands. Tightly scoped to the
    // exact primary key already resolved by the preceding hash lookup.
    await withBypassRls((tx) =>
      tx.update(apiKeys).set({ lastUsedAt: new Date() }).where(eq(apiKeys.id, id)),
    );
  }

  async deactivateApiKey(id: string): Promise<void> {
    await db.update(apiKeys)
      .set({ isActive: false })
      .where(eq(apiKeys.id, id));
  }

  // Tenants
  async getTenants(): Promise<Tenant[]> {
    return db.select().from(tenants).orderBy(desc(tenants.createdAt));
  }

  async getTenantById(id: string): Promise<Tenant | undefined> {
    const [tenant] = await db.select().from(tenants).where(eq(tenants.id, id));
    return tenant || undefined;
  }

  async getTenantBySlug(slug: string): Promise<Tenant | undefined> {
    const [tenant] = await db.select().from(tenants).where(eq(tenants.slug, slug));
    return tenant || undefined;
  }

  async createTenant(tenant: InsertTenant): Promise<Tenant> {
    const [newTenant] = await db.insert(tenants).values(tenant).returning();
    return newTenant;
  }

  async updateTenant(id: string, updates: Partial<InsertTenant>): Promise<Tenant | undefined> {
    const [updated] = await db.update(tenants)
      .set(updates)
      .where(eq(tenants.id, id))
      .returning();
    return updated || undefined;
  }

  // Backup Records
  async getBackupRecords(limit = 50): Promise<BackupRecord[]> {
    return db.select()
      .from(backupRecords)
      .orderBy(desc(backupRecords.createdAt))
      .limit(limit);
  }

  async createBackupRecord(record: InsertBackupRecord): Promise<BackupRecord> {
    const [newRecord] = await db.insert(backupRecords).values(record).returning();
    return newRecord;
  }

  async updateBackupRecord(id: string, updates: Partial<InsertBackupRecord>): Promise<BackupRecord | undefined> {
    const [updated] = await db.update(backupRecords)
      .set(updates)
      .where(eq(backupRecords.id, id))
      .returning();
    return updated || undefined;
  }

  // AI Threat Scores
  async getAiThreatScores(threatEventId?: string): Promise<AiThreatScore[]> {
    if (threatEventId) {
      return db.select()
        .from(aiThreatScores)
        .where(eq(aiThreatScores.threatEventId, threatEventId))
        .orderBy(desc(aiThreatScores.createdAt));
    }
    return db.select()
      .from(aiThreatScores)
      .orderBy(desc(aiThreatScores.createdAt));
  }

  async createAiThreatScore(score: InsertAiThreatScore): Promise<AiThreatScore> {
    // AS/PLATFORM/2026/008 Tenant-Sep Chunk A — fail-closed tenant binding.
    // tenant_id MUST come from request ctx (the GUC-matching (req).tenantId the
    // caller injects), NEVER the client body; refuse an unbound write so a NULL
    // tenant can never be persisted (column is RLS-enrolled + NOT NULL in DB).
    if (!score.tenantId) {
      throw new Error("createAiThreatScore: tenantId is required (tenant-isolation, fail-closed)");
    }
    const [newScore] = await db.insert(aiThreatScores).values(score).returning();
    return newScore;
  }

  // SSO Configs
  async getSsoConfigs(tenantId?: string): Promise<SsoConfig[]> {
    if (tenantId) {
      return db.select()
        .from(ssoConfigs)
        .where(eq(ssoConfigs.tenantId, tenantId))
        .orderBy(desc(ssoConfigs.createdAt));
    }
    return db.select()
      .from(ssoConfigs)
      .orderBy(desc(ssoConfigs.createdAt));
  }

  // Pre-auth SSO config resolution (false-green fix, 2026-06-25). The SSO /login//callback flow is
  // PRE-AUTHENTICATION — no session, no tenant — so tenantMiddleware runs it as the 'default' tenant.
  // sso_configs is an RLS-enrolled TENANT_TABLE, so a normal scoped read sees only 'default' rows and
  // CANNOT find a real tenant's config: the lookup silently returns nothing and the flow fails "config
  // not found" when the config exists (the Finding #2 false-green class, here fail-closed = SSO broken).
  // The configId in the pre-auth URL is the pre-tenant credential, exactly like an API key — so resolve
  // it cross-tenant via withBypassRls (the mandated pre-tenant-lookup pattern; downstream the request
  // scopes to the resolved config.tenantId). Read-only, single PK lookup.
  async getSsoConfigByIdUnscoped(configId: string): Promise<SsoConfig | undefined> {
    return withBypassRls((tx) =>
      tx.select().from(ssoConfigs).where(eq(ssoConfigs.id, configId)).then((r) => r[0]),
    );
  }

  async createSsoConfig(config: InsertSsoConfig): Promise<SsoConfig> {
    const [newConfig] = await db.insert(ssoConfigs).values(config).returning();
    return newConfig;
  }

  async updateSsoConfig(id: string, updates: Partial<InsertSsoConfig>): Promise<SsoConfig | undefined> {
    const [updated] = await db.update(ssoConfigs)
      .set(updates)
      .where(eq(ssoConfigs.id, id))
      .returning();
    return updated || undefined;
  }

  // I18n Overrides
  async getI18nOverrides(locale: string, tenantId?: string): Promise<I18nOverride[]> {
    if (tenantId) {
      return db.select()
        .from(i18nOverrides)
        .where(and(eq(i18nOverrides.locale, locale), eq(i18nOverrides.tenantId, tenantId)))
        .orderBy(desc(i18nOverrides.createdAt));
    }
    return db.select()
      .from(i18nOverrides)
      .where(eq(i18nOverrides.locale, locale))
      .orderBy(desc(i18nOverrides.createdAt));
  }

  // Pre-auth i18n override resolution (false-green fix, Gate-B re-run 2026-06-25). GET /api/i18n/
  // overrides/:locale is PRE-AUTH (login-page localization) -> tenantMiddleware runs it as the
  // 'default' tenant, so a normal scoped read of i18n_overrides (an RLS TENANT_TABLE) sees only
  // 'default' rows and silently returns empty for a real tenant's locale. Same class as the SSO fix:
  // resolve via withBypassRls, filtered to the requested locale+tenant. Display strings only
  // (non-sensitive, intentionally per-tenant-public for the login UI).
  async getI18nOverridesUnscoped(locale: string, tenantId: string): Promise<I18nOverride[]> {
    return withBypassRls((tx) =>
      tx.select().from(i18nOverrides)
        .where(and(eq(i18nOverrides.locale, locale), eq(i18nOverrides.tenantId, tenantId)))
        .orderBy(desc(i18nOverrides.createdAt)),
    );
  }

  async createI18nOverride(override: InsertI18nOverride): Promise<I18nOverride> {
    const [newOverride] = await db.insert(i18nOverrides).values(override).returning();
    return newOverride;
  }

  // UBO Registrations — erasure-path surface (FU-015 / P4.2 Phase 1, 2026-05-24)
  // FU-038 tenantId scoping (P4.2 Phase 3, 2026-05-24): when tenantId is a
  // non-empty string, `AND tenant_id = $tenantId` is added to the predicate.
  // When undefined or null, no tenant filter is added (back-compat for any
  // pre-existing or test-fixture global caller).
  async getUboRegistrationsByCorporate(corporateCustomerRef: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration[]> {
    const qdb = opts?._db ?? db;
    const predicates = [eq(uboRegistrations.corporateCustomerRef, corporateCustomerRef)];
    if (typeof tenantId === "string" && tenantId.length > 0) {
      predicates.push(eq(uboRegistrations.tenantId, tenantId));
    }
    const rows = await qdb
      .select()
      .from(uboRegistrations)
      .where(predicates.length === 1 ? predicates[0] : and(...predicates))
      .orderBy(desc(uboRegistrations.registeredAt));
    return Promise.all(rows.map(decryptUboRow));
  }

  async getUboRegistrationsByNationalId(ownerNationalId: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration[]> {
    // T004a (ii) — lookup via HMAC column; raw ownerNationalId column is now encrypted.
    // A tenantId is required to compute the tenant-keyed HMAC. If tenantId is absent
    // (legacy untenanted DSAR or misconfigured caller), return empty rather than scanning:
    //   (a) HMAC derivation without tenantId would use "" as the tenant key — a different
    //       derivation from any real tenant key, producing no valid matches anyway.
    //   (b) Safe-no-op for the DSAR worker null-tenant path (non-blocking pre-pass).
    if (!tenantId || tenantId.length === 0) {
      return [];
    }
    const qdb = opts?._db ?? db;
    const hmac = computeLookupHash(ownerNationalId, tenantId, "ubo_registrations", "owner_national_id");
    const rows = await qdb
      .select()
      .from(uboRegistrations)
      .where(eq(uboRegistrations.ownerNationalIdHmac, hmac))
      .orderBy(desc(uboRegistrations.registeredAt));
    return Promise.all(rows.map(decryptUboRow));
  }

  async getUboRegistrationByRef(uboRef: string, tenantId?: string | null, opts?: { _db?: DrizzleDb }): Promise<UboRegistration | undefined> {
    const qdb = opts?._db ?? db;
    const predicates = [eq(uboRegistrations.uboRef, uboRef)];
    if (typeof tenantId === "string" && tenantId.length > 0) {
      predicates.push(eq(uboRegistrations.tenantId, tenantId));
    }
    const [row] = await qdb
      .select()
      .from(uboRegistrations)
      .where(predicates.length === 1 ? predicates[0] : and(...predicates))
      .limit(1);
    return row != null ? decryptUboRow(row) : undefined;
  }

  async updateUboErasureState(uboRef: string, updates: {
    erasureState?: UboErasureState;
    supersededAt?: Date | null;
    supersededBy?: string | null;
    erasureMaturedAt?: Date | null;
    erasureTrigger?: UboErasureTrigger | null;
    erasureAuditRef?: string | null;
    individualErasurePending?: string | null;
  }, opts?: { expectedCurrentState?: UboErasureState; tenantId?: string | null; _db?: DrizzleDb }): Promise<UboRegistration | undefined> {
    const qdb = opts?._db ?? db;
    const predicates = [eq(uboRegistrations.uboRef, uboRef)];
    if (opts?.expectedCurrentState !== undefined) {
      predicates.push(eq(uboRegistrations.erasureState, opts.expectedCurrentState));
    }
    if (typeof opts?.tenantId === "string" && opts.tenantId.length > 0) {
      predicates.push(eq(uboRegistrations.tenantId, opts.tenantId));
    }
    const [row] = await qdb
      .update(uboRegistrations)
      .set(updates)
      .where(predicates.length === 1 ? predicates[0] : and(...predicates))
      .returning();
    return row != null ? decryptUboRow(row) : undefined;
  }

  async softEraseUboRow(uboRef: string, redactedFields: {
    ownerName: string;
    ownerNationalId: string;
    ownerNationality: string;
  }, trigger: UboErasureTrigger, auditRef: string,
     opts?: { expectedCurrentState?: UboErasureState; tenantId?: string | null; _db?: DrizzleDb }): Promise<UboRegistration | undefined> {
    // Atomic state transition: PII fields redacted (caller supplies the
    // irrecoverable-salt-hashed national ID and the [redacted] markers per §4
    // of UBO_ERASURE_DESIGN_PROPOSAL.md), erasure_state set to ERASED,
    // erasure_matured_at + erasure_trigger + erasure_audit_ref populated.
    // Structural fields (corporate_*, ownership_pct, control_type, registered_*)
    // are deliberately NOT touched here — they survive erasure per §4 principle 1.
    // Concurrency: when `opts.expectedCurrentState` is supplied, the UPDATE
    // matches only if the row's current erasure_state equals the expected
    // value. Two concurrent triggers (e.g., corporate erasure + regulator
    // order) racing on the same uboRef will both attempt the CAS; whichever
    // arrives second sees a no-row return and the caller treats it as a
    // lost race, audited explicitly. Without the opts, behaviour falls
    // back to the unguarded UPDATE for callers that don't care.
    const predicates = [eq(uboRegistrations.uboRef, uboRef)];
    if (opts?.expectedCurrentState !== undefined) {
      predicates.push(eq(uboRegistrations.erasureState, opts.expectedCurrentState));
    }
    if (typeof opts?.tenantId === "string" && opts.tenantId.length > 0) {
      predicates.push(eq(uboRegistrations.tenantId, opts.tenantId));
    }
    // T004a (i): encrypt the redaction sentinels so every row's ownerName/ownerNationality
    // columns remain in the encrypted-at-rest state post-erasure. The tenantId used for
    // key derivation is taken from opts.tenantId (always populated by erasure callers per
    // the CAS comment at L194). Sentinels are not PII; encryption here preserves schema
    // consistency (all rows encrypted) rather than providing additional PII protection.
    const eraseCtx = { type: "tenant" as const, tenantId: opts?.tenantId ?? "" };
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .update(uboRegistrations)
      .set({
        ownerName:       await encryptPii(redactedFields.ownerName,       eraseCtx), // T004a (i) — sentinel
        ownerNationalId: redactedFields.ownerNationalId,                        // T004a (ii) — caller supplies hashed value
        ownerNationality: await encryptPii(redactedFields.ownerNationality, eraseCtx), // T004a (i) — sentinel
        erasureState: "ERASED",
        erasureMaturedAt: new Date(),
        erasureTrigger: trigger,
        erasureAuditRef: auditRef,
        // Clear the pending flag if this row had one — it's now matured past pending.
        individualErasurePending: null,
      })
      .where(predicates.length === 1 ? predicates[0] : and(...predicates))
      .returning();
    return row != null ? decryptUboRow(row) : undefined;
  }

  // ── KYC Customers — DB-primary CRUD (KYC v1 Feature 1) ─────────────────────
  async createKycCustomer(
    input: CreateKycCustomerInput,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer> {
    assertKycTenantId(tenantId, "createKycCustomer");
    assertNotApprovedWrite(input.status, "createKycCustomer");
    const piiCtx = { type: "tenant" as const, tenantId };
    const qdb = opts?._db ?? db;
    // Pure insert (create semantics): a duplicate national_id_hmac collides on the
    // unique index and THROWS — fail-loud, never silently swallowed.
    const [row] = await qdb
      .insert(kycCustomers)
      .values({
        id: input.id,
        nationalId:     await encryptPii(input.nationalId, piiCtx),
        nationalIdHmac: computeLookupHash(input.nationalId, tenantId, "kyc_customers", "national_id"),
        fullName:    await encryptPii(input.fullName,    piiCtx),
        dateOfBirth: await encryptPii(input.dateOfBirth, piiCtx),
        phoneNumber: await encryptPii(input.phoneNumber, piiCtx),
        email:    input.email    != null ? await encryptPii(input.email,    piiCtx) : undefined,
        address:  input.address  != null ? await encryptPii(input.address,  piiCtx) : undefined,
        photoUrl: input.photoUrl != null ? await encryptPii(input.photoUrl, piiCtx) : undefined,
        status:   input.status   ?? "PENDING",
        riskTier: input.riskTier ?? "LOW",
        onboardedAt:    input.onboardedAt    ?? undefined,
        lastVerifiedAt: input.lastVerifiedAt ?? undefined,
        tenantId,
      })
      .returning();
    if (row == null) {
      throw new Error("createKycCustomer: INSERT returned no row (write not durable)");
    }
    return decryptKycRow(row, tenantId);
  }

  async getKycCustomerById(
    id: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer | undefined> {
    assertKycTenantId(tenantId, "getKycCustomerById");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .select()
      .from(kycCustomers)
      .where(and(eq(kycCustomers.id, id), eq(kycCustomers.tenantId, tenantId)))
      .limit(1);
    return row != null ? decryptKycRow(row, tenantId) : undefined;
  }

  async getKycCustomerByNationalId(
    nationalId: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer | undefined> {
    assertKycTenantId(tenantId, "getKycCustomerByNationalId");
    const qdb = opts?._db ?? db;
    // HMAC is tenant-keyed (computeLookupHash folds in tenantId) so the lookup is
    // tenant-correct by construction; the explicit tenant_id predicate is additive.
    const hmac = computeLookupHash(nationalId, tenantId, "kyc_customers", "national_id");
    const [row] = await qdb
      .select()
      .from(kycCustomers)
      .where(and(eq(kycCustomers.nationalIdHmac, hmac), eq(kycCustomers.tenantId, tenantId)))
      .limit(1);
    return row != null ? decryptKycRow(row, tenantId) : undefined;
  }

  async listKycCustomers(
    tenantId: string,
    limit?: number,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer[]> {
    assertKycTenantId(tenantId, "listKycCustomers");
    const qdb = opts?._db ?? db;
    const base = qdb
      .select()
      .from(kycCustomers)
      .where(eq(kycCustomers.tenantId, tenantId))
      .orderBy(desc(kycCustomers.createdAt));
    const rows =
      typeof limit === "number" && limit > 0 ? await base.limit(limit) : await base;
    return Promise.all(rows.map((r) => decryptKycRow(r, tenantId)));
  }

  // ── KYC v1 Feature 4 — examiner NON-DECRYPTING customer reads (E1 guarantee) ──
  // These SELECT only the safe, non-PII columns (id, status, risk_tier, and the
  // timestamps). decryptKycRow is NEVER called here, so the decrypt path is not
  // on the wire — no decrypted PII can reach an examiner response by construction.
  // PII columns (national_id, national_id_hmac, full_name, date_of_birth,
  // phone_number, email, address, photo_url) are not selected.
  async getKycCustomerRefForExaminer(
    id: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomerRefRow | undefined> {
    assertKycTenantId(tenantId, "getKycCustomerRefForExaminer");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .select({
        id: kycCustomers.id,
        status: kycCustomers.status,
        riskTier: kycCustomers.riskTier,
        onboardedAt: kycCustomers.onboardedAt,
        lastVerifiedAt: kycCustomers.lastVerifiedAt,
        createdAt: kycCustomers.createdAt,
      })
      .from(kycCustomers)
      .where(and(eq(kycCustomers.id, id), eq(kycCustomers.tenantId, tenantId)))
      .limit(1);
    return row ?? undefined;
  }

  async listKycCustomerRefsForExaminer(
    tenantId: string,
    opts?: { limit?: number; _db?: DrizzleDb },
  ): Promise<KycCustomerRefRow[]> {
    assertKycTenantId(tenantId, "listKycCustomerRefsForExaminer");
    const qdb = opts?._db ?? db;
    const base = qdb
      .select({
        id: kycCustomers.id,
        status: kycCustomers.status,
        riskTier: kycCustomers.riskTier,
        onboardedAt: kycCustomers.onboardedAt,
        lastVerifiedAt: kycCustomers.lastVerifiedAt,
        createdAt: kycCustomers.createdAt,
      })
      .from(kycCustomers)
      .where(eq(kycCustomers.tenantId, tenantId))
      .orderBy(desc(kycCustomers.createdAt));
    const rows =
      typeof opts?.limit === "number" && opts.limit > 0 ? await base.limit(opts.limit) : await base;
    return rows;
  }

  async updateKycCustomerStatusAndRisk(
    id: string,
    tenantId: string,
    updates: { status?: KYCStatus; riskTier?: KYCRiskTier; lastVerifiedAt?: Date | null },
    opts?: { _db?: DrizzleDb },
  ): Promise<KycCustomer | undefined> {
    assertKycTenantId(tenantId, "updateKycCustomerStatusAndRisk");
    assertNotApprovedWrite(updates.status, "updateKycCustomerStatusAndRisk");
    const qdb = opts?._db ?? db;
    const set: { status?: string; riskTier?: string; lastVerifiedAt?: Date | null; updatedAt: Date } = {
      updatedAt: new Date(),
    };
    if (updates.status !== undefined) set.status = updates.status;
    if (updates.riskTier !== undefined) set.riskTier = updates.riskTier;
    if (updates.lastVerifiedAt !== undefined) set.lastVerifiedAt = updates.lastVerifiedAt;
    const [row] = await qdb
      .update(kycCustomers)
      .set(set)
      .where(and(eq(kycCustomers.id, id), eq(kycCustomers.tenantId, tenantId)))
      .returning();
    return row != null ? decryptKycRow(row, tenantId) : undefined;
  }

  // ── KYC Decisions (Feature 3) — tenant-scoped; no PII columns in these tables ──
  // All writes run on the per-request tenant transaction (tenantMiddleware ALS) so
  // the GUC is set and RLS enforces; the explicit tenant_id predicate is belt-and-
  // suspenders. These tables hold provider statuses, derived tiers, a non-PII
  // signals snapshot, and a signals hash — never raw customer PII.
  async createKycDecisionOutcome(
    input: CreateKycDecisionOutcomeInput,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome> {
    assertKycTenantId(tenantId, "createKycDecisionOutcome");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .insert(kycDecisionOutcomes)
      .values({ ...input, tenantId })
      .returning();
    if (!row) throw new Error("createKycDecisionOutcome: INSERT returned no row (write not durable)");
    return row;
  }

  async getKycDecisionOutcomeById(
    id: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome | undefined> {
    assertKycTenantId(tenantId, "getKycDecisionOutcomeById");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .select()
      .from(kycDecisionOutcomes)
      .where(and(eq(kycDecisionOutcomes.id, id), eq(kycDecisionOutcomes.tenantId, tenantId)))
      .limit(1);
    return row ?? undefined;
  }

  async listKycDecisionOutcomes(
    tenantId: string,
    opts?: { customerId?: string; limit?: number; _db?: DrizzleDb },
  ): Promise<KycDecisionOutcome[]> {
    assertKycTenantId(tenantId, "listKycDecisionOutcomes");
    const qdb = opts?._db ?? db;
    const where = opts?.customerId
      ? and(
          eq(kycDecisionOutcomes.tenantId, tenantId),
          eq(kycDecisionOutcomes.customerId, opts.customerId),
        )
      : eq(kycDecisionOutcomes.tenantId, tenantId);
    const base = qdb
      .select()
      .from(kycDecisionOutcomes)
      .where(where)
      .orderBy(desc(kycDecisionOutcomes.decidedAt));
    const rows =
      typeof opts?.limit === "number" && opts.limit > 0 ? await base.limit(opts.limit) : await base;
    return rows;
  }

  async getKycDecisionApprovalByDecisionId(
    decisionId: string,
    tenantId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycDecisionApproval | undefined> {
    assertKycTenantId(tenantId, "getKycDecisionApprovalByDecisionId");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .select()
      .from(kycDecisionApprovals)
      .where(
        and(
          eq(kycDecisionApprovals.decisionId, decisionId),
          eq(kycDecisionApprovals.tenantId, tenantId),
        ),
      )
      .limit(1);
    return row ?? undefined;
  }

  // ── Manual identity attestation (Build B: manual-identity path) ──────────────
  // Record a compliance officer's manual document-review determination. Tenant-scoped.
  async createKycIdentityAttestation(input: {
    tenantId: string;
    customerId: string;
    status: "VERIFIED" | "NOT_VERIFIED" | "REVIEW_REQUIRED";
    evidenceRef?: string | null;
    notes?: string | null;
    attestedByUserId: string;
    attestedByRole: string;
  }): Promise<KycIdentityAttestation> {
    assertKycTenantId(input.tenantId, "createKycIdentityAttestation");
    const [row] = await db
      .insert(kycIdentityAttestations)
      .values({
        tenantId: input.tenantId,
        customerId: input.customerId,
        status: input.status,
        evidenceRef: input.evidenceRef ?? null,
        notes: input.notes ?? null,
        attestedByUserId: input.attestedByUserId,
        attestedByRole: input.attestedByRole,
      })
      .returning();
    if (!row) throw new Error("createKycIdentityAttestation: insert returned no row (write not durable)");
    return row;
  }

  // Read the LATEST attestation per (tenant, customer) — the identity adapter's source of truth.
  async getLatestKycIdentityAttestation(
    tenantId: string,
    customerId: string,
    opts?: { _db?: DrizzleDb },
  ): Promise<KycIdentityAttestation | undefined> {
    assertKycTenantId(tenantId, "getLatestKycIdentityAttestation");
    const qdb = opts?._db ?? db;
    const [row] = await qdb
      .select()
      .from(kycIdentityAttestations)
      .where(
        and(
          eq(kycIdentityAttestations.tenantId, tenantId),
          eq(kycIdentityAttestations.customerId, customerId),
        ),
      )
      .orderBy(desc(kycIdentityAttestations.attestedAt))
      .limit(1);
    return row ?? undefined;
  }

  // ── THE LOCKED DOOR ─────────────────────────────────────────────────────────
  // The SOLE path that can set a kyc_customers row to APPROVED. It bypasses
  // assertNotApprovedWrite deliberately (direct kycCustomers.update) — and that
  // bypass is safe ONLY because assertFinalizePreconditions() runs immediately
  // before it and throws (fail-loud) unless EVERY condition holds:
  //   • the outcome is an APPROVE awaiting senior sign-off,
  //   • fresh, re-collected signals STILL decide APPROVE (composition re-checked),
  //   • the fresh signals hash equals the decided-on hash (no stale/changed signals),
  //   • the approver is a senior authority (risk_manager/admin), and
  //   • the approver is a DIFFERENT person than the decider (two-eyes).
  // Atomicity: all reads + provider re-collection + every guard run FIRST; the
  // three writes (approval insert → customer APPROVED → outcome FINAL) run LAST,
  // in order, with NO 4xx branch between them, so any throw aborts the per-request
  // tenant transaction and rolls ALL of them back (codebase idiom — same request
  // tx, no explicit nested transaction). The unique index kda_tenant_decision_unique
  // is the concurrency guard: a second concurrent finalize fails the approval insert
  // and rolls back, so a customer is finalized at most once.
  async recordApprovalAndFinalize(
    args: RecordApprovalAndFinalizeArgs,
  ): Promise<RecordApprovalAndFinalizeResult> {
    const { decisionId, tenantId, approverUserId, approverRole, reason, correlationId } = args;
    assertKycTenantId(tenantId, "recordApprovalAndFinalize");

    // 0. Defense-in-depth (architect Rule-9 hardening): the locked door's atomicity
    //    AND tenant-correctness both depend on running INSIDE the request tenant
    //    transaction — all three writes (approval → customer APPROVED → outcome
    //    FINAL) share that one tx and roll back together, and RLS scopes every
    //    read/write to the active tenant. Assert that invariant LOCALLY instead of
    //    trusting the caller: refuse if no tenant context is active, or if the
    //    active context's tenant differs from the finalize tenant (a cross-tenant
    //    call). This is a server-side invariant breach, never the happy path, so it
    //    throws a plain Error (→ 500), not a FinalizeGuardError client 4xx. NOTE:
    //    deliberately NOT wrapped in withTenantRls — that would open a SEPARATE tx
    //    and break the "a failed audit_logs write rolls the finalize back" atomicity
    //    (the route writes the audit on this same request tx after this returns).
    const activeTenantId = getActiveTenantId();
    if (!activeTenantId) {
      throw new Error(
        "recordApprovalAndFinalize invariant: no active request tenant transaction — the " +
          "locked-door finalize must run inside the request tenant tx (atomicity + RLS).",
      );
    }
    if (activeTenantId !== tenantId) {
      throw new Error(
        `recordApprovalAndFinalize invariant: tenant context mismatch — active "${activeTenantId}" ` +
          `vs finalize tenant "${tenantId}".`,
      );
    }

    // 1. Load the outcome (tenant-scoped). Missing ⇒ NOT_FOUND.
    const outcome = await this.getKycDecisionOutcomeById(decisionId, tenantId);
    if (!outcome) {
      throw new FinalizeGuardError(`decision outcome ${decisionId} not found`, "NOT_FOUND");
    }

    // 2. Reject a double approval early (friendly 409). The unique index is the
    //    structural guarantee for the concurrent-race case at write time.
    const existingApproval = await this.getKycDecisionApprovalByDecisionId(decisionId, tenantId);
    if (existingApproval) {
      throw new FinalizeGuardError("decision already has a recorded senior approval");
    }

    // 3. Load the customer (decrypted) to re-collect fresh signals.
    const customer = await this.getKycCustomerById(outcome.customerId, tenantId);
    if (!customer) {
      throw new FinalizeGuardError(`customer ${outcome.customerId} not found`, "NOT_FOUND");
    }

    // 4. Re-collect FRESH signals NOW (outside any write) and re-decide.
    const existingRiskTier = coerceRiskTier(customer.riskTier);
    const subject: KycSubjectForDecision = {
      id: customer.id,
      tenantId,
      nationalId: customer.nationalId,
      fullName: customer.fullName,
      dateOfBirth: customer.dateOfBirth,
      phoneNumber: customer.phoneNumber ?? null,
      riskTier: existingRiskTier,
    };
    const freshSignals = await collectFreshSignals(subject, correlationId);
    const derivedRiskTier = deriveRiskTier(existingRiskTier, freshSignals);
    const freshPlan = decide(freshSignals, derivedRiskTier);
    const freshSnapshot = buildSignalsSnapshot(
      tenantId,
      customer.id,
      existingRiskTier,
      derivedRiskTier,
      freshSignals,
    );
    const freshHash = computeSignalsHash(freshSnapshot);

    // 5. THE GUARD — throws (fail-loud) unless every precondition holds.
    assertFinalizePreconditions({ outcome, freshPlan, freshHash, approverUserId, approverRole });

    // 6. Writes LAST, in order, on the request tenant tx (all-or-nothing).
    const [approval] = await db
      .insert(kycDecisionApprovals)
      .values({
        tenantId,
        decisionId: outcome.id,
        customerId: customer.id,
        approverUserId,
        approverRole,
        approvalType: "SENIOR_AUTHORITY",
        signalsHash: outcome.signalsHash,
        reason: reason ?? null,
      })
      .returning();
    if (!approval) {
      throw new Error("recordApprovalAndFinalize: approval INSERT returned no row (write not durable)");
    }

    // The SOLE APPROVED writer (direct update — structurally guarded above).
    const [custRow] = await db
      .update(kycCustomers)
      .set({ status: "APPROVED", riskTier: derivedRiskTier, updatedAt: new Date() })
      .where(and(eq(kycCustomers.id, customer.id), eq(kycCustomers.tenantId, tenantId)))
      .returning();
    if (!custRow) {
      throw new Error("recordApprovalAndFinalize: customer APPROVED update wrote no row");
    }

    const [outRow] = await db
      .update(kycDecisionOutcomes)
      .set({ state: "FINAL", updatedAt: new Date() })
      .where(and(eq(kycDecisionOutcomes.id, outcome.id), eq(kycDecisionOutcomes.tenantId, tenantId)))
      .returning();
    if (!outRow) {
      throw new Error("recordApprovalAndFinalize: outcome FINAL update wrote no row");
    }

    return { outcome: outRow, approval, customer: await decryptKycRow(custRow, tenantId) };
  }
}

export const storage = new DatabaseStorage();