nekonest-cloud/db/retention.ts

export const CLAIM_RATE_RETENTION_MS = 24 * 60 * 60_000;
export const CLAIM_ATTEMPT_RETENTION_MS = 30 * 24 * 60 * 60_000;
export const HANDOFF_TICKET_RETENTION_MS = 24 * 60 * 60_000;
export const RETENTION_JOB_KEY = "retention_cleanup";
export const RETENTION_CRON = "17 18 * * *";
export const RETENTION_RUNNING_STALE_MS = 30 * 60_000;
export const RETENTION_SUCCESS_OVERDUE_MS = 36 * 60 * 60_000;
const RETENTION_FUTURE_SKEW_MS = 60_000;

export type RetentionCutoffs = {
  now: string;
  claimRateBefore: string;
  claimAttemptBefore: string;
  handoffTicketBefore: string;
};

export type RetentionJobRecord = {
  key: string;
  state: string;
  run_id: string | null;
  trigger: string | null;
  scheduled_at: string | null;
  started_at: string | null;
  completed_at: string | null;
  last_success_at: string | null;
  result_json: string | null;
  error_code: string | null;
  consecutive_failures: number;
  run_count: number;
  created_at: string;
  updated_at: string;
};

export type RetentionJobHealth = {
  state: "never_run" | "running" | "healthy" | "overdue" | "failed" | "stalled" | "invalid";
  trigger: "scheduled" | "manual" | null;
  startedAt: string | null;
  completedAt: string | null;
  lastSuccessAt: string | null;
  errorCode: string | null;
  consecutiveFailures: number;
  runCount: number;
  ageSeconds: number | null;
};

function parseTimestamp(value: string | null): number | null {
  if (!value) return null;
  const timestamp = new Date(value).getTime();
  return Number.isFinite(timestamp) ? timestamp : null;
}

export function deriveRetentionJobHealth(
  record: RetentionJobRecord | null,
  now = new Date().toISOString(),
): RetentionJobHealth {
  if (!record) {
    return {
      state: "never_run",
      trigger: null,
      startedAt: null,
      completedAt: null,
      lastSuccessAt: null,
      errorCode: null,
      consecutiveFailures: 0,
      runCount: 0,
      ageSeconds: null,
    };
  }

  const nowMilliseconds = new Date(now).getTime();
  const scheduledMilliseconds = parseTimestamp(record.scheduled_at);
  const startedMilliseconds = parseTimestamp(record.started_at);
  const completedMilliseconds = parseTimestamp(record.completed_at);
  const successMilliseconds = parseTimestamp(record.last_success_at);
  const countsValid = Number.isSafeInteger(record.run_count) && record.run_count >= 1
    && Number.isSafeInteger(record.consecutive_failures) && record.consecutive_failures >= 0;
  const errorCodeValid = record.error_code === null
    || /^[a-z0-9_]{3,64}$/.test(record.error_code);
  const trigger: RetentionJobHealth["trigger"] = record.trigger === "scheduled" || record.trigger === "manual"
    ? record.trigger
    : null;
  const base = {
    trigger,
    startedAt: record.started_at,
    completedAt: record.completed_at,
    lastSuccessAt: record.last_success_at,
    errorCode: record.error_code,
    consecutiveFailures: countsValid ? record.consecutive_failures : 0,
    runCount: countsValid ? record.run_count : 0,
  };
  const invalid = !Number.isFinite(nowMilliseconds)
    || record.key !== RETENTION_JOB_KEY
    || !countsValid
    || !errorCodeValid
    || !trigger
    || !record.run_id
    || scheduledMilliseconds === null
    || !startedMilliseconds
    || scheduledMilliseconds > nowMilliseconds + RETENTION_FUTURE_SKEW_MS
    || startedMilliseconds > nowMilliseconds + RETENTION_FUTURE_SKEW_MS
    || (record.completed_at !== null && completedMilliseconds === null)
    || (record.last_success_at !== null && successMilliseconds === null)
    || (completedMilliseconds !== null && completedMilliseconds > nowMilliseconds + RETENTION_FUTURE_SKEW_MS)
    || (successMilliseconds !== null && successMilliseconds > nowMilliseconds + RETENTION_FUTURE_SKEW_MS)
    || (completedMilliseconds !== null && completedMilliseconds < startedMilliseconds)
    || (successMilliseconds !== null && completedMilliseconds !== null && successMilliseconds > completedMilliseconds);
  if (invalid) {
    return { state: "invalid", ...base, ageSeconds: null };
  }

  if (record.state === "running") {
    if (record.error_code !== null) {
      return { state: "invalid", ...base, ageSeconds: null };
    }
    const ageMilliseconds = Math.max(0, nowMilliseconds - startedMilliseconds);
    return {
      state: ageMilliseconds > RETENTION_RUNNING_STALE_MS ? "stalled" : "running",
      ...base,
      ageSeconds: Math.floor(ageMilliseconds / 1_000),
    };
  }

  if (record.state === "failed") {
    if (record.error_code === null) {
      return { state: "invalid", ...base, ageSeconds: null };
    }
    const failureAgeMilliseconds = Math.max(
      0,
      nowMilliseconds - (completedMilliseconds ?? startedMilliseconds),
    );
    return {
      state: "failed",
      ...base,
      ageSeconds: Math.floor(failureAgeMilliseconds / 1_000),
    };
  }
  if (record.state !== "succeeded"
    || record.error_code !== null
    || successMilliseconds === null
    || completedMilliseconds === null) {
    return { state: "invalid", ...base, ageSeconds: null };
  }
  const ageMilliseconds = Math.max(0, nowMilliseconds - successMilliseconds);
  return {
    state: ageMilliseconds > RETENTION_SUCCESS_OVERDUE_MS ? "overdue" : "healthy",
    ...base,
    ageSeconds: Math.floor(ageMilliseconds / 1_000),
  };
}

export function getRetentionCutoffs(now: string): RetentionCutoffs {
  const nowMilliseconds = new Date(now).getTime();
  if (!Number.isFinite(nowMilliseconds)) {
    throw new TypeError("retention maintenance requires an ISO timestamp");
  }
  return {
    now: new Date(nowMilliseconds).toISOString(),
    claimRateBefore: new Date(nowMilliseconds - CLAIM_RATE_RETENTION_MS).toISOString(),
    claimAttemptBefore: new Date(nowMilliseconds - CLAIM_ATTEMPT_RETENTION_MS).toISOString(),
    handoffTicketBefore: new Date(nowMilliseconds - HANDOFF_TICKET_RETENTION_MS).toISOString(),
  };
}

/**
 * Pairing history remains available, but its credential-derived hash does not.
 * The deterministic tombstone is unique because pairing request IDs are unique.
 */
export const RETIRE_EXPIRED_PAIRING_CODES_SQL = `
UPDATE pairing_requests
SET code_hash = 'expired:' || id
WHERE expires_at <= ?
  AND code_hash != ('expired:' || id)`;

export const DELETE_EXPIRED_CLAIM_RATE_WINDOWS_SQL = `
DELETE FROM pairing_claim_rate_limits
WHERE window_start < ?`;

export const DELETE_EXPIRED_CLAIM_ATTEMPTS_SQL = `
DELETE FROM pairing_claim_attempts
WHERE created_at < ?`;

export const DELETE_EXPIRED_IDEMPOTENCY_SQL = `
DELETE FROM idempotency_records
WHERE expires_at <= ?`;

/**
 * Handoff tickets contain only a one-way ticket digest and public phone identity,
 * never the raw ticket or phone token. Retain consumed/expired rows briefly for
 * replay diagnostics, then remove them on a bounded schedule.
 */
export const DELETE_RETIRED_PHONE_HANDOFF_TICKETS_SQL = `
DELETE FROM phone_handoff_tickets
WHERE expires_at <= ?
   OR (consumed_at IS NOT NULL AND consumed_at <= ?)`;

export const DELETE_RETIRED_PENDING_PHONE_ROUTES_SQL = `
DELETE FROM phone_route_handles
WHERE status = 'pending'
  AND EXISTS (
    SELECT 1 FROM phone_handoff_tickets AS tickets
    WHERE tickets.tenant_id = phone_route_handles.tenant_id
      AND tickets.completed_phone_id = phone_route_handles.phone_id
      AND tickets.completed_route_handle_hash = phone_route_handles.handle_hash
      AND (
        tickets.expires_at <= ?
        OR (tickets.consumed_at IS NOT NULL AND tickets.consumed_at <= ?)
      )
  )`;

export const DELETE_RETIRED_PENDING_PHONE_PRINCIPALS_SQL = `
DELETE FROM relay_phone_principals
WHERE status = 'pending'
  AND EXISTS (
    SELECT 1 FROM phone_handoff_tickets AS tickets
    WHERE tickets.tenant_id = relay_phone_principals.tenant_id
      AND tickets.completed_phone_id = relay_phone_principals.phone_id
      AND tickets.completed_phone_token_hash = relay_phone_principals.token_hash
      AND (
        tickets.expires_at <= ?
        OR (tickets.consumed_at IS NOT NULL AND tickets.consumed_at <= ?)
      )
  )`;

export const DELETE_EXPIRED_DEVICE_REGISTRATION_REPLAYS_SQL = `
DELETE FROM device_registration_replays
WHERE expires_at <= ?`;

export const ACQUIRE_RETENTION_JOB_SQL = `
INSERT INTO maintenance_jobs
  (key, state, run_id, trigger, scheduled_at, started_at, completed_at,
   last_success_at, result_json, error_code, consecutive_failures, run_count,
   created_at, updated_at)
VALUES (?, 'running', ?, ?, ?, ?, NULL, NULL, NULL, NULL, 0, 1, ?, ?)
ON CONFLICT(key) DO UPDATE SET
  state = 'running',
  run_id = excluded.run_id,
  trigger = excluded.trigger,
  scheduled_at = excluded.scheduled_at,
  started_at = excluded.started_at,
  completed_at = NULL,
  result_json = NULL,
  error_code = NULL,
  run_count = maintenance_jobs.run_count + 1,
  updated_at = excluded.updated_at
WHERE maintenance_jobs.state != 'running'
   OR maintenance_jobs.started_at < ?`;

export const COMPLETE_RETENTION_JOB_SQL = `
UPDATE maintenance_jobs
SET state = 'succeeded',
    completed_at = ?,
    last_success_at = ?,
    result_json = ?,
    error_code = NULL,
    consecutive_failures = 0,
    updated_at = ?
WHERE key = ?
  AND run_id = ?
  AND state = 'running'`;

export const FAIL_RETENTION_JOB_SQL = `
UPDATE maintenance_jobs
SET state = 'failed',
    completed_at = ?,
    result_json = NULL,
    error_code = ?,
    consecutive_failures = consecutive_failures + 1,
    updated_at = ?
WHERE key = ?
  AND run_id = ?
  AND state = 'running'`;