nekonest-cloud/db/relay-control-sql.ts

export const ADVANCE_AUTHORIZATION_AFTER_CLAIM_SQL = `
  UPDATE tenant_authorization_state
  SET revision = revision + 1, updated_at = ?1
  WHERE tenant_id = (SELECT id FROM tenant_instances WHERE account_id = ?2)
    AND EXISTS (
      SELECT 1 FROM device_credentials
      WHERE id = ?3 AND host_id = ?4 AND token_hash = ?5 AND status = 'active'
    )
`;

export const ADVANCE_AUTHORIZATION_AFTER_REVOKE_SQL = `
  UPDATE tenant_authorization_state
  SET revision = revision + 1, updated_at = ?1
  WHERE tenant_id = (SELECT id FROM tenant_instances WHERE account_id = ?2)
    AND EXISTS (
      SELECT 1 FROM hosts
      WHERE id = ?3 AND lifecycle = 'deactivated' AND deactivated_at = ?4
    )
`;

export const CONSUME_PHONE_HANDOFF_SQL = `
  UPDATE phone_handoff_tickets
  SET consumed_at = ?1, consumed_by_node_id = ?5,
      pending_phone_name = ?6, pending_ed25519_public = ?7,
      pending_x25519_public = ?8, pending_identity_fingerprint = ?9
  WHERE id = ?2 AND ticket_hash = ?3 AND expected_origin = ?4
    AND consumed_at IS NULL AND expires_at > ?1
`;

export const DELETE_SUPERSEDED_PENDING_PHONE_ROUTES_SQL = `
  DELETE FROM phone_route_handles
  WHERE status = 'pending'
    AND EXISTS (
      SELECT 1
      FROM phone_handoff_tickets AS previous
      INNER JOIN phone_handoff_tickets AS current ON current.id = ?1
      WHERE previous.id != current.id
        AND previous.tenant_id = current.tenant_id
        AND previous.pending_identity_fingerprint = current.pending_identity_fingerprint
        AND previous.completed_phone_id = phone_route_handles.phone_id
        AND previous.completed_route_handle_hash = phone_route_handles.handle_hash
        AND previous.expires_at <= ?2
        AND previous.activation_nonce IS NULL
    )
`;

export const DELETE_SUPERSEDED_PENDING_PHONE_PRINCIPALS_SQL = `
  DELETE FROM relay_phone_principals
  WHERE status = 'pending'
    AND EXISTS (
      SELECT 1
      FROM phone_handoff_tickets AS previous
      INNER JOIN phone_handoff_tickets AS current ON current.id = ?1
      WHERE previous.id != current.id
        AND previous.tenant_id = current.tenant_id
        AND previous.pending_identity_fingerprint = current.pending_identity_fingerprint
        AND previous.completed_phone_id = relay_phone_principals.phone_id
        AND previous.completed_phone_token_hash = relay_phone_principals.token_hash
        AND previous.expires_at <= ?2
        AND previous.activation_nonce IS NULL
    )
`;

export const CLAIM_PHONE_HANDOFF_ACTIVATION_SQL = `
  UPDATE phone_handoff_tickets
  SET activation_nonce = ?1
  WHERE completed_route_handle_hash = ?2
    AND completed_phone_token_hash = ?3
    AND completed_at > ?4
    AND consumed_by_node_id = ?5
    AND activation_nonce IS NULL
    AND EXISTS (
      SELECT 1
      FROM phone_route_handles AS handles
      INNER JOIN relay_phone_principals AS phones ON phones.phone_id = handles.phone_id
      INNER JOIN tenant_placements AS placements ON placements.tenant_id = handles.tenant_id
      INNER JOIN tenant_authorization_state AS authorizations ON authorizations.tenant_id = handles.tenant_id
      WHERE handles.handle_hash = ?2 AND handles.status = 'pending'
        AND handles.revoked_at IS NULL AND phones.token_hash = ?3
        AND phones.status = 'pending' AND phones.revoked_at IS NULL
        AND phones.tenant_id = phone_handoff_tickets.tenant_id
        AND phones.phone_id = phone_handoff_tickets.completed_phone_id
        AND handles.tenant_id = phone_handoff_tickets.tenant_id
        AND placements.relay_node_id = ?5
        AND placements.state IN ('active', 'draining')
        AND authorizations.status = 'active'
    )
`;

export const ACTIVATE_PHONE_PRINCIPAL_SQL = `
  UPDATE relay_phone_principals
  SET status = 'active'
  WHERE token_hash = ?2 AND status = 'pending' AND revoked_at IS NULL
    AND EXISTS (
      SELECT 1 FROM phone_handoff_tickets AS tickets
      WHERE tickets.activation_nonce = ?1
        AND tickets.completed_phone_id = relay_phone_principals.phone_id
        AND tickets.completed_phone_token_hash = ?2
        AND tickets.tenant_id = relay_phone_principals.tenant_id
    )
`;

export const ACTIVATE_PHONE_ROUTE_SQL = `
  UPDATE phone_route_handles
  SET status = 'active'
  WHERE handle_hash = ?2 AND status = 'pending' AND revoked_at IS NULL
    AND EXISTS (
      SELECT 1 FROM phone_handoff_tickets AS tickets
      WHERE tickets.activation_nonce = ?1
        AND tickets.completed_route_handle_hash = ?2
        AND tickets.completed_phone_token_hash = ?3
        AND tickets.completed_phone_id = phone_route_handles.phone_id
        AND tickets.tenant_id = phone_route_handles.tenant_id
    )
    AND EXISTS (
      SELECT 1 FROM relay_phone_principals AS phones
      WHERE phones.phone_id = phone_route_handles.phone_id
        AND phones.tenant_id = phone_route_handles.tenant_id
        AND phones.token_hash = ?3
        AND phones.status = 'active' AND phones.revoked_at IS NULL
    )
`;

export const ADVANCE_AUTHORIZATION_AFTER_PHONE_ACTIVATION_SQL = `
  UPDATE tenant_authorization_state
  SET revision = revision + 1, updated_at = ?1
  WHERE status = 'active'
    AND tenant_id = (
      SELECT tenant_id FROM phone_handoff_tickets
      WHERE activation_nonce = ?2
    )
    AND EXISTS (
      SELECT 1
      FROM phone_handoff_tickets AS tickets
      INNER JOIN relay_phone_principals AS phones
        ON phones.phone_id = tickets.completed_phone_id
       AND phones.tenant_id = tickets.tenant_id
      INNER JOIN phone_route_handles AS handles
        ON handles.phone_id = tickets.completed_phone_id
       AND handles.tenant_id = tickets.tenant_id
       AND handles.handle_hash = tickets.completed_route_handle_hash
      WHERE tickets.activation_nonce = ?2
        AND phones.status = 'active' AND phones.revoked_at IS NULL
        AND handles.status = 'active' AND handles.revoked_at IS NULL
        AND phones.token_hash = tickets.completed_phone_token_hash
    )
`;

export const FINALIZE_PHONE_HANDOFF_ACTIVATION_SQL = `
  UPDATE phone_handoff_tickets
  SET activated_at = ?1
  WHERE activation_nonce = ?2 AND activated_at IS NULL
    AND EXISTS (
      SELECT 1 FROM relay_phone_principals AS phones
      WHERE phones.phone_id = phone_handoff_tickets.completed_phone_id
        AND phones.tenant_id = phone_handoff_tickets.tenant_id
        AND phones.token_hash = phone_handoff_tickets.completed_phone_token_hash
        AND phones.status = 'active' AND phones.revoked_at IS NULL
    )
    AND EXISTS (
      SELECT 1 FROM phone_route_handles AS handles
      WHERE handles.phone_id = phone_handoff_tickets.completed_phone_id
        AND handles.tenant_id = phone_handoff_tickets.tenant_id
        AND handles.handle_hash = phone_handoff_tickets.completed_route_handle_hash
        AND handles.status = 'active' AND handles.revoked_at IS NULL
    )
`;

export const AUTHORIZE_PHONE_ROUTE_SQL = `
  SELECT handles.tenant_id, regions.code AS home_region,
         placements.relay_node_id, placements.generation,
         phones.phone_id, phones.name, phones.ed25519_public,
         phones.x25519_public, phones.identity_fingerprint
  FROM phone_route_handles AS handles
  INNER JOIN relay_phone_principals AS phones ON phones.phone_id = handles.phone_id
  INNER JOIN tenant_placements AS placements ON placements.tenant_id = handles.tenant_id
  INNER JOIN relay_regions AS regions ON regions.id = placements.home_region_id
  INNER JOIN tenant_authorization_state AS authorizations ON authorizations.tenant_id = handles.tenant_id
  WHERE handles.handle_hash = ?1 AND handles.status = 'active'
    AND handles.revoked_at IS NULL AND phones.token_hash = ?2
    AND phones.status = 'active' AND phones.revoked_at IS NULL
    AND phones.tenant_id = handles.tenant_id
    AND authorizations.status = 'active'
    AND placements.state IN ('active', 'draining')
`;

export const PHONE_FOR_NODE_REVOCATION_SQL = `
SELECT placements.relay_node_id, phones.status
FROM tenant_placements AS placements
INNER JOIN relay_phone_principals AS phones
  ON phones.tenant_id = placements.tenant_id
WHERE placements.tenant_id = ?1 AND phones.phone_id = ?2`;

export const REVOKE_PHONE_PRINCIPAL_SQL = `
UPDATE relay_phone_principals
SET status = 'revoked', revoked_at = ?1
WHERE phone_id = ?2 AND tenant_id = ?3
  AND status = 'active' AND revoked_at IS NULL`;

export const REVOKE_PHONE_ROUTES_SQL = `
UPDATE phone_route_handles
SET status = 'revoked', revoked_at = ?1
WHERE phone_id = ?2 AND tenant_id = ?3
  AND status = 'active' AND revoked_at IS NULL`;

export const ADVANCE_AUTHORIZATION_AFTER_PHONE_REVOKE_SQL = `
UPDATE tenant_authorization_state
SET revision = revision + 1, updated_at = ?1
WHERE tenant_id = ?2
  AND EXISTS (
    SELECT 1 FROM relay_phone_principals
    WHERE phone_id = ?3 AND tenant_id = ?2
      AND status = 'revoked' AND revoked_at = ?1
  )`;