nekonest-cloud/db/pairing.ts

import { PUBLIC_BETA_GATE_READY_SQL } from "./launch-gates.ts";

/**
 * Reserve a pairing slot in one SQLite statement.
 *
 * Parameters:
 *   ?1 request id, ?2 account id, ?3 requested name, ?4 OS,
 *   ?5 code hash, ?6 expiry, ?7 current UTC timestamp.
 *
 * Keeping the capacity and pending-limit predicates inside the INSERT makes
 * concurrent D1 writes serialize around the actual reservation instead of
 * trusting an earlier read that may already be stale.
 */
export const RESERVE_PAIRING_SQL = `
  WITH active_beta AS (
    SELECT capacity_slots
    FROM beta_programs
    WHERE state = 'active' AND starts_at <= ?7
      AND (ends_at IS NULL OR ends_at > ?7)
      AND ${PUBLIC_BETA_GATE_READY_SQL}
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  )
  INSERT INTO pairing_requests
    (id, account_id, requested_name, os, code_hash, status, expires_at, created_at)
  SELECT ?1, ?2, ?3, ?4, ?5, 'waiting', ?6, ?7
  WHERE
    (
      SELECT COUNT(*) FROM pairing_requests
      WHERE account_id = ?2 AND status = 'waiting' AND expires_at > ?7
    ) < 5
    AND (
      EXISTS (
        SELECT 1 FROM active_beta WHERE capacity_slots IS NULL
      )
      OR EXISTS (
        SELECT 1 FROM entitlement_grants
        WHERE account_id = ?2 AND state = 'active' AND starts_at <= ?7
          AND (ends_at IS NULL OR ends_at > ?7)
          AND revoked_at IS NULL AND capacity_slots IS NULL
      )
      OR (
        COALESCE((
          SELECT SUM(capacity_slots) FROM active_beta
          WHERE capacity_slots IS NOT NULL
        ), 0)
        + COALESCE((
          SELECT SUM(capacity_slots) FROM entitlement_grants
          WHERE account_id = ?2 AND state = 'active' AND starts_at <= ?7
            AND (ends_at IS NULL OR ends_at > ?7)
            AND revoked_at IS NULL AND capacity_slots IS NOT NULL
        ), 0)
        >
        (
          SELECT COUNT(*) FROM hosts
          WHERE account_id = ?2 AND lifecycle = 'active' AND slot_state = 'active'
        )
        + (
          SELECT COUNT(*) FROM pairing_requests
          WHERE account_id = ?2 AND status = 'waiting' AND expires_at > ?7
        )
      )
    )
`;

export const CONSUME_CLAIM_RATE_SQL = `
  INSERT INTO pairing_claim_rate_limits
    (source_hash, window_start, attempts, updated_at)
  VALUES (?1, ?2, 1, ?3)
  ON CONFLICT(source_hash, window_start) DO UPDATE SET
    attempts = pairing_claim_rate_limits.attempts + 1,
    updated_at = excluded.updated_at
  WHERE pairing_claim_rate_limits.attempts < 20
`;

export const RECORD_FAILED_CODE_SQL = `
  UPDATE pairing_requests
  SET failed_attempts = failed_attempts + 1,
      last_attempt_at = ?1,
      locked_at = CASE WHEN failed_attempts + 1 >= 5 THEN ?1 ELSE locked_at END,
      status = CASE WHEN failed_attempts + 1 >= 5 THEN 'locked' ELSE status END
  WHERE id = ?2 AND status = 'waiting' AND locked_at IS NULL
    AND expires_at > ?1 AND failed_attempts < 5
`;

/**
 * Invalidate an unclaimed pairing request owned by one account.
 *
 * Parameters:
 *   ?1 request id, ?2 account id, ?3 replacement code hash.
 *
 * Replacing the hash makes an accidentally reverted status insufficient to
 * revive the original bearer code.
 */
export const CANCEL_PAIRING_SQL = `
  UPDATE pairing_requests
  SET status = 'cancelled', code_hash = ?3
  WHERE id = ?1 AND account_id = ?2 AND status = 'waiting'
`;

export const OWNED_PAIRING_PROGRESS_SQL = `
  SELECT pairing_requests.id, pairing_requests.status,
         pairing_requests.expires_at, pairing_requests.claimed_host_id,
         pairing_requests.claimed_at,
         (SELECT MAX(attempts.created_at)
          FROM pairing_claim_attempts AS attempts
          WHERE attempts.pairing_request_id = pairing_requests.id)
           AS last_claim_attempt_at
  FROM pairing_requests
  WHERE pairing_requests.id = ? AND pairing_requests.account_id = ?
`;

export type PairingProgressRow = {
  id: string;
  status: string;
  expires_at: string;
  claimed_host_id: string | null;
  claimed_at: string | null;
  last_claim_attempt_at: string | null;
};

export type PairingProgress = {
  id: string;
  status: "waiting" | "claimed" | "expired" | "locked" | "cancelled";
  expiresAt: string;
  claimedHostId: string | null;
  claimedAt: string | null;
  claimAttemptState: "not_seen" | "seen" | "invalid";
  lastClaimAttemptAt: string | null;
};

export function derivePairingProgress(
  row: PairingProgressRow,
  nowInput: string,
): PairingProgress {
  const now = Date.parse(nowInput);
  const expiresAt = Date.parse(row.expires_at);
  if (!Number.isFinite(now) || !Number.isFinite(expiresAt)) {
    throw new RangeError("invalid_pairing_progress_time");
  }
  const claimAttemptAt = row.last_claim_attempt_at
    ? Date.parse(row.last_claim_attempt_at)
    : null;
  const claimAttemptState = claimAttemptAt === null
    ? "not_seen"
    : !Number.isFinite(claimAttemptAt) || claimAttemptAt > now + 5 * 60_000
      ? "invalid"
      : "seen";
  const lastClaimAttemptAt = claimAttemptState === "seen"
    ? row.last_claim_attempt_at
    : null;

  if (row.status === "waiting") {
    return {
      id: row.id,
      status: expiresAt <= now ? "expired" : "waiting",
      expiresAt: row.expires_at,
      claimedHostId: null,
      claimedAt: null,
      claimAttemptState,
      lastClaimAttemptAt,
    };
  }
  if (row.status === "claimed") {
    if (!row.claimed_host_id || !row.claimed_at) {
      throw new RangeError("incomplete_claimed_pairing_progress");
    }
    return {
      id: row.id,
      status: "claimed",
      expiresAt: row.expires_at,
      claimedHostId: row.claimed_host_id,
      claimedAt: row.claimed_at,
      claimAttemptState,
      lastClaimAttemptAt,
    };
  }
  if (row.status === "locked" || row.status === "cancelled") {
    return {
      id: row.id,
      status: row.status,
      expiresAt: row.expires_at,
      claimedHostId: null,
      claimedAt: null,
      claimAttemptState,
      lastClaimAttemptAt,
    };
  }
  throw new RangeError("unknown_pairing_progress_status");
}

const CURRENT_PAIRING_ACCESS = `
  (
    EXISTS (
      SELECT 1 FROM beta_programs
      WHERE state = 'active' AND starts_at <= ? AND (ends_at IS NULL OR ends_at > ?)
        AND ${PUBLIC_BETA_GATE_READY_SQL}
    )
    OR EXISTS (
      SELECT 1 FROM entitlement_grants
      WHERE account_id = pairing_requests.account_id
        AND state = 'active' AND starts_at <= ? AND (ends_at IS NULL OR ends_at > ?)
        AND revoked_at IS NULL
    )
  )`;

const VALID_CLAIM = `
  id = ? AND code_hash = ? AND status = 'waiting'
  AND expires_at > ? AND locked_at IS NULL AND failed_attempts < 5 AND os = ?
  AND ${CURRENT_PAIRING_ACCESS}
`;

/**
 * Claim-time capacity check for CLAIM_HOST_SQL.
 *
 * The numbered parameters intentionally reuse that statement's current-time
 * bindings: ?11/?12 for the latest public beta and ?13/?14 for grants. A
 * waiting request reserves capacity while it is waiting, but claim converts
 * it into an active host. Therefore the atomic claim boundary compares the
 * current finite entitlement with active hosts only. If capacity was reduced
 * after several pairing codes were issued, the first claims up to the new
 * limit may succeed and later claims fail without touching existing hosts.
 */
const HOST_CLAIM_CAPACITY = `
  (
    EXISTS (
      SELECT 1 FROM current_beta WHERE capacity_slots IS NULL
    )
    OR EXISTS (
      SELECT 1 FROM entitlement_grants
      WHERE account_id = pairing_requests.account_id
        AND state = 'active' AND starts_at <= ?13
        AND (ends_at IS NULL OR ends_at > ?14)
        AND revoked_at IS NULL AND capacity_slots IS NULL
    )
    OR (
      COALESCE((SELECT capacity_slots FROM current_beta), 0)
      + COALESCE((
        SELECT SUM(capacity_slots) FROM entitlement_grants
        WHERE account_id = pairing_requests.account_id
          AND state = 'active' AND starts_at <= ?13
          AND (ends_at IS NULL OR ends_at > ?14)
          AND revoked_at IS NULL AND capacity_slots IS NOT NULL
      ), 0)
      > (
        SELECT COUNT(*) FROM hosts
        WHERE account_id = pairing_requests.account_id
          AND lifecycle = 'active' AND slot_state = 'active'
      )
    )
  )`;

export const CLAIM_HOST_SQL = `
  WITH current_beta AS (
    SELECT capacity_slots
    FROM beta_programs
    WHERE state = 'active' AND starts_at <= ?11
      AND (ends_at IS NULL OR ends_at > ?12)
      AND ${PUBLIC_BETA_GATE_READY_SQL}
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  )
  INSERT INTO hosts
    (id, account_id, name, os, lifecycle, slot_state, connection_state,
     daemon_version, ed25519_public, x25519_public, identity_fingerprint, claim_request_id,
     claimed_at, created_at)
  SELECT ?1, account_id, requested_name, os, 'active', 'active', 'offline',
         ?15, ?2, ?3, ?4, id, ?5, ?6
  FROM pairing_requests
  WHERE id = ?7 AND code_hash = ?8 AND status = 'waiting'
    AND expires_at > ?9 AND locked_at IS NULL AND failed_attempts < 5
    AND os = ?10
    AND (
      EXISTS (SELECT 1 FROM current_beta)
      OR EXISTS (
        SELECT 1 FROM entitlement_grants
        WHERE account_id = pairing_requests.account_id
          AND state = 'active' AND starts_at <= ?13
          AND (ends_at IS NULL OR ends_at > ?14)
          AND revoked_at IS NULL
      )
    )
    AND ${HOST_CLAIM_CAPACITY}
  ON CONFLICT(identity_fingerprint) DO UPDATE SET
    name = excluded.name,
    os = excluded.os,
    lifecycle = 'active',
    slot_state = 'active',
    connection_state = 'offline',
    daemon_version = COALESCE(excluded.daemon_version, hosts.daemon_version),
    ed25519_public = excluded.ed25519_public,
    x25519_public = excluded.x25519_public,
    claim_request_id = excluded.claim_request_id,
    claimed_at = excluded.claimed_at,
    deactivated_at = NULL
  WHERE hosts.account_id = excluded.account_id
    AND hosts.lifecycle = 'deactivated'
    AND hosts.slot_state = 'released'
`;

export const CLAIM_CREDENTIAL_SQL = `
  INSERT INTO device_credentials
    (id, host_id, token_hash, status, issued_at, created_at)
  SELECT ?, ?, ?, 'active', ?, ?
  FROM pairing_requests
  WHERE ${VALID_CLAIM}
    AND EXISTS (
      SELECT 1 FROM hosts
      WHERE id = ? AND identity_fingerprint = ?
        AND claim_request_id = pairing_requests.id
    )
`;

export const COMMIT_PAIRING_CLAIM_SQL = `
  UPDATE pairing_requests
  SET status = 'claimed', claimed_host_id = ?, claimed_at = ?, last_attempt_at = ?,
      code_hash = ?
  WHERE ${VALID_CLAIM}
    AND EXISTS (
      SELECT 1 FROM device_credentials
      WHERE id = ? AND host_id = ? AND status = 'active'
    )
`;

/**
 * Publish a successfully claimed device set to the tenant reconciler.
 *
 * Parameters:
 *   ?1 current UTC timestamp, ?2 account id, ?3 pairing id, ?4 host id,
 *   ?5 claimed timestamp, ?6 credential id, ?7 device token hash.
 *
 * The positive pairing and credential predicates keep this update in the
 * same fail-closed D1 batch as the claim. A failed or partial claim therefore
 * cannot create provisioning work.
 */
export const ADVANCE_TENANT_CREDENTIAL_AFTER_CLAIM_SQL = `
  UPDATE tenant_instances
  SET credential_revision = credential_revision + 1,
      lifecycle = CASE WHEN lifecycle = 'ready' THEN 'degraded' ELSE lifecycle END,
      relay_ready = 0,
      updated_at = ?
  WHERE account_id = ?
    AND EXISTS (
      SELECT 1 FROM pairing_requests
      WHERE id = ? AND status = 'claimed' AND claimed_host_id = ? AND claimed_at = ?
    )
    AND EXISTS (
      SELECT 1 FROM device_credentials
      WHERE id = ? AND host_id = ? AND token_hash = ? AND status = 'active'
    )
`;