import { sql } from "drizzle-orm"; import { index, integer, primaryKey, sqliteTable, text, uniqueIndex, } from "drizzle-orm/sqlite-core"; const createdAt = () => text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`); export const accounts = sqliteTable( "accounts", { id: text("id").primaryKey(), authSubject: text("auth_subject").notNull(), email: text("email").notNull(), displayName: text("display_name").notNull(), status: text("status").notNull().default("active"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_accounts_auth_subject").on(table.authSubject), index("idx_accounts_email").on(table.email), ], ); export const hosts = sqliteTable( "hosts", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), name: text("name").notNull(), os: text("os").notNull(), lifecycle: text("lifecycle").notNull().default("active"), slotState: text("slot_state").notNull().default("active"), connectionState: text("connection_state").notNull().default("unknown"), daemonVersion: text("daemon_version"), recoveryFingerprint: text("recovery_fingerprint"), ed25519Public: text("ed25519_public"), x25519Public: text("x25519_public"), identityFingerprint: text("identity_fingerprint"), claimRequestId: text("claim_request_id"), claimedAt: text("claimed_at"), lastSeenAt: text("last_seen_at"), createdAt: createdAt(), deactivatedAt: text("deactivated_at"), }, (table) => [ index("idx_hosts_account_lifecycle").on( table.accountId, table.lifecycle, ), uniqueIndex("idx_hosts_recovery_fingerprint").on( table.recoveryFingerprint, ), uniqueIndex("idx_hosts_identity_fingerprint").on( table.identityFingerprint, ), ], ); export const pairingRequests = sqliteTable( "pairing_requests", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), requestedName: text("requested_name").notNull(), os: text("os").notNull(), codeHash: text("code_hash").notNull(), status: text("status").notNull().default("waiting"), expiresAt: text("expires_at").notNull(), claimedHostId: text("claimed_host_id").references(() => hosts.id), failedAttempts: integer("failed_attempts").notNull().default(0), lockedAt: text("locked_at"), lastAttemptAt: text("last_attempt_at"), createdAt: createdAt(), claimedAt: text("claimed_at"), }, (table) => [ uniqueIndex("idx_pairing_code_hash").on(table.codeHash), index("idx_pairing_account_status").on(table.accountId, table.status), ], ); export const deviceCredentials = sqliteTable( "device_credentials", { id: text("id").primaryKey(), hostId: text("host_id") .notNull() .references(() => hosts.id), tokenHash: text("token_hash").notNull(), status: text("status").notNull().default("active"), issuedAt: text("issued_at").notNull(), expiresAt: text("expires_at"), lastUsedAt: text("last_used_at"), revokedAt: text("revoked_at"), createdAt: createdAt(), }, (table) => [ uniqueIndex("idx_device_credentials_token_hash").on(table.tokenHash), index("idx_device_credentials_host_status").on(table.hostId, table.status), ], ); export const pairingClaimRateLimits = sqliteTable( "pairing_claim_rate_limits", { sourceHash: text("source_hash").notNull(), windowStart: text("window_start").notNull(), attempts: integer("attempts").notNull().default(1), updatedAt: text("updated_at").notNull(), }, (table) => [primaryKey({ columns: [table.sourceHash, table.windowStart] })], ); export const pairingClaimAttempts = sqliteTable( "pairing_claim_attempts", { id: text("id").primaryKey(), pairingRequestId: text("pairing_request_id").notNull(), sourceHash: text("source_hash").notNull(), outcome: text("outcome").notNull(), createdAt: createdAt(), }, (table) => [ index("idx_pairing_claim_attempts_target_time").on( table.pairingRequestId, table.createdAt, ), index("idx_pairing_claim_attempts_source_time").on( table.sourceHash, table.createdAt, ), ], ); export const cloudSchemaMigrations = sqliteTable("cloud_schema_migrations", { id: text("id").primaryKey(), appliedAt: text("applied_at").notNull(), }); export const priceVersions = sqliteTable( "price_versions", { id: text("id").primaryKey(), productCode: text("product_code").notNull().default("host_slot"), billingPeriod: text("billing_period").notNull(), unitSlots: integer("unit_slots").notNull().default(1), amountMinor: integer("amount_minor").notNull(), currency: text("currency").notNull().default("CNY"), taxMode: text("tax_mode").notNull().default("undecided"), quoteTtlSeconds: integer("quote_ttl_seconds").notNull().default(900), status: text("status").notNull().default("draft"), effectiveFrom: text("effective_from").notNull(), effectiveTo: text("effective_to"), createdBy: text("created_by").notNull(), createdAt: createdAt(), }, (table) => [ index("idx_prices_catalog").on( table.productCode, table.billingPeriod, table.status, ), ], ); export const orders = sqliteTable( "orders", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), clientOrderId: text("client_order_id").notNull(), kind: text("kind").notNull().default("purchase"), priceVersionId: text("price_version_id") .notNull() .references(() => priceVersions.id), slotQuantity: integer("slot_quantity").notNull(), termStart: text("term_start").notNull(), termEnd: text("term_end").notNull(), amountMinor: integer("amount_minor").notNull(), currency: text("currency").notNull(), status: text("status").notNull().default("draft"), quoteExpiresAt: text("quote_expires_at").notNull(), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_orders_account_client").on( table.accountId, table.clientOrderId, ), index("idx_orders_account_status").on(table.accountId, table.status), ], ); export const paymentAttempts = sqliteTable( "payment_attempts", { id: text("id").primaryKey(), orderId: text("order_id") .notNull() .references(() => orders.id), provider: text("provider").notNull(), providerPaymentRef: text("provider_payment_ref"), providerEventId: text("provider_event_id"), amountMinor: integer("amount_minor").notNull(), currency: text("currency").notNull(), status: text("status").notNull().default("created"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_payments_provider_event").on( table.provider, table.providerEventId, ), uniqueIndex("idx_payments_provider_ref").on( table.provider, table.providerPaymentRef, ), ], ); export const invoices = sqliteTable( "invoices", { id: text("id").primaryKey(), orderId: text("order_id") .notNull() .references(() => orders.id), invoiceType: text("invoice_type").notNull().default("commercial"), invoiceNumber: text("invoice_number"), amountMinor: integer("amount_minor").notNull(), taxMinor: integer("tax_minor"), currency: text("currency").notNull(), status: text("status").notNull().default("draft"), documentRef: text("document_ref"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [uniqueIndex("idx_invoices_order").on(table.orderId)], ); export const refunds = sqliteTable( "refunds", { id: text("id").primaryKey(), paymentAttemptId: text("payment_attempt_id") .notNull() .references(() => paymentAttempts.id), invoiceId: text("invoice_id").references(() => invoices.id), amountMinor: integer("amount_minor").notNull(), currency: text("currency").notNull(), reason: text("reason").notNull(), status: text("status").notNull().default("requested"), entitlementEffect: text("entitlement_effect").notNull().default("pending"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [index("idx_refunds_payment").on(table.paymentAttemptId)], ); export const betaPrograms = sqliteTable( "beta_programs", { id: text("id").primaryKey(), state: text("state").notNull(), capacitySlots: integer("capacity_slots"), startsAt: text("starts_at").notNull(), endsAt: text("ends_at"), graceDays: integer("grace_days").notNull().default(7), createdBy: text("created_by").notNull(), createdAt: createdAt(), retiredAt: text("retired_at"), }, (table) => [index("idx_beta_state_time").on(table.state, table.startsAt)], ); export const entitlementGrants = sqliteTable( "entitlement_grants", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), hostId: text("host_id").references(() => hosts.id), source: text("source").notNull(), sourceRef: text("source_ref"), capacitySlots: integer("capacity_slots"), startsAt: text("starts_at").notNull(), endsAt: text("ends_at"), state: text("state").notNull().default("scheduled"), reason: text("reason").notNull(), createdBy: text("created_by").notNull(), createdAt: createdAt(), revokedAt: text("revoked_at"), }, (table) => [ index("idx_grants_account_state_time").on( table.accountId, table.state, table.startsAt, ), uniqueIndex("idx_grants_source_ref").on(table.source, table.sourceRef), ], ); export const betaAccessRequests = sqliteTable( "beta_access_requests", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), status: text("status").notNull().default("requested"), preferredOs: text("preferred_os").notNull(), requestedSlots: integer("requested_slots").notNull().default(1), useCase: text("use_case").notNull(), adminResponse: text("admin_response"), resolvedBy: text("resolved_by"), invitationGrantId: text("invitation_grant_id").references( () => entitlementGrants.id, ), requestedAt: text("requested_at").notNull(), resolvedAt: text("resolved_at"), cancelledAt: text("cancelled_at"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_beta_access_requests_one_pending") .on(table.accountId) .where(sql`${table.status} = 'requested'`), index("idx_beta_access_requests_status_time").on( table.status, table.requestedAt, ), index("idx_beta_access_requests_account_time").on( table.accountId, table.requestedAt, ), index("idx_beta_access_requests_requested_time").on(table.requestedAt), ], ); export const tenantInstances = sqliteTable( "tenant_instances", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), slug: text("slug").notNull(), lifecycle: text("lifecycle").notNull().default("requested"), desiredState: text("desired_state").notNull().default("requested"), observedState: text("observed_state").notNull().default("absent"), desiredGeneration: integer("desired_generation").notNull().default(1), activeGeneration: integer("active_generation"), credentialRevision: integer("credential_revision").notNull().default(0), runtimeVersion: text("runtime_version"), runtimeRef: text("runtime_ref"), relayOrigin: text("relay_origin"), secretBundleRef: text("secret_bundle_ref"), relayReady: integer("relay_ready", { mode: "boolean" }).notNull().default(false), lastHealthAt: text("last_health_at"), tombstonedAt: text("tombstoned_at"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_tenants_account").on(table.accountId), uniqueIndex("idx_tenants_slug").on(table.slug), ], ); export const relayRegions = sqliteTable( "relay_regions", { id: text("id").primaryKey(), code: text("code").notNull(), displayName: text("display_name").notNull(), status: text("status").notNull().default("active"), publicOrigin: text("public_origin").notNull(), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_relay_regions_code").on(table.code), uniqueIndex("idx_relay_regions_public_origin").on(table.publicOrigin), ], ); export const relayNodes = sqliteTable( "relay_nodes", { id: text("id").primaryKey(), regionId: text("region_id") .notNull() .references(() => relayRegions.id), status: text("status").notNull().default("provisioning"), internalEndpointRef: text("internal_endpoint_ref").notNull(), capacityTenants: integer("capacity_tenants").notNull().default(0), lastHeartbeatAt: text("last_heartbeat_at"), heartbeatGeneration: integer("heartbeat_generation").notNull().default(0), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ index("idx_relay_nodes_region_status").on(table.regionId, table.status), ], ); export const tenantPlacements = sqliteTable( "tenant_placements", { tenantId: text("tenant_id") .primaryKey() .references(() => tenantInstances.id), homeRegionId: text("home_region_id") .notNull() .references(() => relayRegions.id), relayNodeId: text("relay_node_id").references(() => relayNodes.id), generation: integer("generation").notNull().default(1), state: text("state").notNull().default("provisioning"), lastErrorCode: text("last_error_code"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ index("idx_tenant_placements_region_state").on( table.homeRegionId, table.state, ), index("idx_tenant_placements_node_state").on( table.relayNodeId, table.state, ), ], ); export const tenantAuthorizationState = sqliteTable( "tenant_authorization_state", { tenantId: text("tenant_id") .primaryKey() .references(() => tenantInstances.id), revision: integer("revision").notNull().default(0), status: text("status").notNull().default("active"), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [index("idx_tenant_authorization_revision").on(table.revision)], ); export const relayMigrations = sqliteTable( "relay_migrations", { id: text("id").primaryKey(), tenantId: text("tenant_id") .notNull() .references(() => tenantInstances.id), sourceNodeId: text("source_node_id") .notNull() .references(() => relayNodes.id), targetNodeId: text("target_node_id") .notNull() .references(() => relayNodes.id), sourceGeneration: integer("source_generation").notNull(), targetGeneration: integer("target_generation").notNull(), state: text("state").notNull().default("quiescing"), backupRef: text("backup_ref"), manifestSha256: text("manifest_sha256"), requestedBy: text("requested_by").notNull(), reason: text("reason").notNull(), startedAt: text("started_at").notNull(), updatedAt: text("updated_at").notNull(), switchedAt: text("switched_at"), completedAt: text("completed_at"), lastErrorCode: text("last_error_code"), }, (table) => [ index("idx_relay_migrations_source_state").on( table.sourceNodeId, table.state, table.updatedAt, ), index("idx_relay_migrations_target_state").on( table.targetNodeId, table.state, table.updatedAt, ), ], ); export const relaySigningKeys = sqliteTable("relay_signing_keys", { kid: text("kid").primaryKey(), algorithm: text("algorithm").notNull().default("Ed25519"), publicKeyJwk: text("public_key_jwk").notNull(), privateKeyRef: text("private_key_ref").notNull(), status: text("status").notNull().default("active"), notBefore: text("not_before").notNull(), notAfter: text("not_after").notNull(), createdAt: createdAt(), retiredAt: text("retired_at"), }); export const relayNodeCredentials = sqliteTable( "relay_node_credentials", { id: text("id").primaryKey(), nodeId: text("node_id") .notNull() .references(() => relayNodes.id), mtlsSpiffeId: text("mtls_spiffe_id").notNull(), certificateFingerprintSha256: text("certificate_fingerprint_sha256").notNull(), status: text("status").notNull().default("active"), bootstrapReason: text("bootstrap_reason").notNull(), issuedBy: text("issued_by").notNull(), issuedAt: text("issued_at").notNull(), expiresAt: text("expires_at"), lastUsedAt: text("last_used_at"), revokedAt: text("revoked_at"), createdAt: createdAt(), }, (table) => [ uniqueIndex("idx_relay_node_credentials_mtls").on( table.mtlsSpiffeId, table.certificateFingerprintSha256, ), index("idx_relay_node_credentials_node_status").on( table.nodeId, table.status, ), ], ); export const phoneHandoffTickets = sqliteTable( "phone_handoff_tickets", { id: text("id").primaryKey(), ticketHash: text("ticket_hash").notNull(), accountId: text("account_id") .notNull() .references(() => accounts.id), tenantId: text("tenant_id") .notNull() .references(() => tenantInstances.id), expectedOrigin: text("expected_origin").notNull(), expiresAt: text("expires_at").notNull(), consumedAt: text("consumed_at"), consumedByNodeId: text("consumed_by_node_id").references( () => relayNodes.id, ), pendingPhoneName: text("pending_phone_name"), pendingEd25519Public: text("pending_ed25519_public"), pendingX25519Public: text("pending_x25519_public"), pendingIdentityFingerprint: text("pending_identity_fingerprint"), completedAt: text("completed_at"), completedPhoneId: text("completed_phone_id"), completedPhoneTokenHash: text("completed_phone_token_hash"), completedRouteHandleHash: text("completed_route_handle_hash"), activationNonce: text("activation_nonce"), activatedAt: text("activated_at"), createdAt: createdAt(), }, (table) => [ uniqueIndex("idx_phone_handoff_ticket_hash").on(table.ticketHash), uniqueIndex("idx_phone_handoff_activation_nonce").on(table.activationNonce), index("idx_phone_handoff_account_expiry").on( table.accountId, table.expiresAt, ), ], ); export const phoneRouteHandles = sqliteTable( "phone_route_handles", { id: text("id").primaryKey(), handleHash: text("handle_hash").notNull(), tenantId: text("tenant_id") .notNull() .references(() => tenantInstances.id), phoneId: text("phone_id").notNull(), status: text("status").notNull().default("active"), createdAt: createdAt(), lastUsedAt: text("last_used_at"), revokedAt: text("revoked_at"), }, (table) => [ uniqueIndex("idx_phone_route_handle_hash").on(table.handleHash), index("idx_phone_route_tenant_status").on(table.tenantId, table.status), ], ); export const relayPhonePrincipals = sqliteTable( "relay_phone_principals", { phoneId: text("phone_id").primaryKey(), tenantId: text("tenant_id") .notNull() .references(() => tenantInstances.id), tokenHash: text("token_hash").notNull(), name: text("name").notNull(), ed25519Public: text("ed25519_public").notNull(), x25519Public: text("x25519_public").notNull(), identityFingerprint: text("identity_fingerprint").notNull(), status: text("status").notNull().default("active"), createdAt: createdAt(), lastUsedAt: text("last_used_at"), revokedAt: text("revoked_at"), }, (table) => [ uniqueIndex("idx_relay_phone_token_hash").on(table.tokenHash), uniqueIndex("idx_relay_phone_tenant_identity").on( table.tenantId, table.identityFingerprint, ), index("idx_relay_phone_tenant_status").on(table.tenantId, table.status), ], ); export const deviceRegistrationReplays = sqliteTable( "device_registration_replays", { pairingId: text("pairing_id") .primaryKey() .references(() => pairingRequests.id), requestHash: text("request_hash").notNull(), responseCiphertext: text("response_ciphertext").notNull(), responseNonce: text("response_nonce").notNull(), expiresAt: text("expires_at").notNull(), createdAt: createdAt(), }, (table) => [index("idx_device_registration_replay_expiry").on(table.expiresAt)], ); export const launchGates = sqliteTable( "launch_gates", { key: text("key").primaryKey(), priority: text("priority").notNull(), category: text("category").notNull(), title: text("title").notNull(), status: text("status").notNull().default("blocked"), owner: text("owner"), evidenceUrl: text("evidence_url"), notes: text("notes").notNull().default(""), reviewedAt: text("reviewed_at"), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [index("idx_launch_gates_status").on(table.priority, table.status)], ); export const betaFeedback = sqliteTable( "beta_feedback", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), category: text("category").notNull(), message: text("message").notNull(), status: text("status").notNull().default("open"), adminResponse: text("admin_response"), handledBy: text("handled_by"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), resolvedAt: text("resolved_at"), }, (table) => [ index("idx_beta_feedback_account_time").on( table.accountId, table.createdAt, ), index("idx_beta_feedback_status_time").on(table.status, table.createdAt), ], ); export const accountDeletionRequests = sqliteTable( "account_deletion_requests", { id: text("id").primaryKey(), accountId: text("account_id") .notNull() .references(() => accounts.id), status: text("status").notNull().default("requested"), reason: text("reason"), requestedAt: text("requested_at").notNull(), cancelledAt: text("cancelled_at"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ uniqueIndex("idx_account_deletion_one_requested") .on(table.accountId) .where(sql`${table.status} = 'requested'`), index("idx_account_deletion_status_time").on( table.status, table.requestedAt, ), ], ); export const relayPurgeJobs = sqliteTable( "relay_purge_jobs", { id: text("id").primaryKey(), deletionRequestId: text("deletion_request_id") .notNull() .references(() => accountDeletionRequests.id), tenantId: text("tenant_id") .notNull() .references(() => tenantInstances.id), relayNodeId: text("relay_node_id") .notNull() .references(() => relayNodes.id), placementGeneration: integer("placement_generation").notNull(), state: text("state").notNull().default("quiescing"), requestedBy: text("requested_by").notNull(), reason: text("reason").notNull(), startedAt: text("started_at").notNull(), updatedAt: text("updated_at").notNull(), completedAt: text("completed_at"), evidenceSha256: text("evidence_sha256"), lastErrorCode: text("last_error_code"), }, (table) => [ uniqueIndex("idx_relay_purge_deletion_request").on(table.deletionRequestId), uniqueIndex("idx_relay_purge_tenant").on(table.tenantId), index("idx_relay_purge_node_state").on(table.relayNodeId, table.state, table.updatedAt), ], ); export const serviceIncidents = sqliteTable( "service_incidents", { id: text("id").primaryKey(), severity: text("severity").notNull(), title: text("title").notNull(), message: text("message").notNull(), status: text("status").notNull().default("active"), createdBy: text("created_by").notNull(), resolvedBy: text("resolved_by"), resolution: text("resolution"), startedAt: text("started_at").notNull(), resolvedAt: text("resolved_at"), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), }, (table) => [ index("idx_service_incidents_status_started").on( table.status, table.startedAt, ), ], ); export const auditEvents = sqliteTable( "audit_events", { id: text("id").primaryKey(), actorId: text("actor_id").notNull(), action: text("action").notNull(), targetType: text("target_type").notNull(), targetId: text("target_id").notNull(), reason: text("reason").notNull(), beforeJson: text("before_json"), afterJson: text("after_json"), correlationId: text("correlation_id").notNull(), createdAt: createdAt(), }, (table) => [ index("idx_audit_target_time").on( table.targetType, table.targetId, table.createdAt, ), index("idx_audit_actor_time").on(table.actorId, table.createdAt), ], ); export const idempotencyRecords = sqliteTable( "idempotency_records", { scope: text("scope").notNull(), key: text("key").notNull(), requestHash: text("request_hash").notNull(), responseJson: text("response_json").notNull(), statusCode: integer("status_code").notNull(), expiresAt: text("expires_at").notNull(), createdAt: createdAt(), }, (table) => [ primaryKey({ columns: [table.scope, table.key] }), index("idx_idempotency_expires").on(table.expiresAt), ], ); export const maintenanceJobs = sqliteTable("maintenance_jobs", { key: text("key").primaryKey(), state: text("state").notNull(), runId: text("run_id"), trigger: text("trigger"), scheduledAt: text("scheduled_at"), startedAt: text("started_at"), completedAt: text("completed_at"), lastSuccessAt: text("last_success_at"), resultJson: text("result_json"), errorCode: text("error_code"), consecutiveFailures: integer("consecutive_failures").notNull().default(0), runCount: integer("run_count").notNull().default(0), createdAt: createdAt(), updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`), });