Database Schema¶
The canonical schema is seat-manager/prisma/schema.prisma; raw SQL migrations add partial indexes that Prisma cannot express.
| model | columns that matter to integrators | uniqueness invariants | read indexes |
|---|---|---|---|
Seat |
seat_id, operator_id, status, registered_at, pubkey, beneficiary, vault_address, withdrawal_credentials, canonical_tuple_hash, vault_config_version, nonce_consumed, operator_api_key_expires_at |
seat_id primary key, pubkey unique, vault_address unique, nonce_consumed unique in seat-manager/prisma/schema.prisma:10; non-revoked operator_id unique via seat-manager/prisma/migrations/20260514165745_one_active_seat_per_operator/migration.sql:37 |
idx_seats_created_at |
NonceLedger |
nonce, consumed_at, seat_id, operator_id |
nonce primary key in seat-manager/prisma/schema.prisma:35 |
primary-key lookup |
Idempotency |
endpoint, seat_id, idempotency_key, payload_hash, response_code, response_body, created_at |
composite primary key in seat-manager/prisma/schema.prisma:44 |
primary-key lookup |
AdminSession |
session_id, user_id, username, role, csrf_token, expires_at, step_up_expires_at, revoked_at, replaced_by_session_id |
session_id primary key in seat-manager/prisma/schema.prisma:57 |
idx_admin_sessions_user_created_desc, idx_admin_sessions_expires_at |
AdminWriteIdempotency |
endpoint, target_id, actor_id, idempotency_key, payload_hash, response_code, response_body, expires_at |
composite primary key in seat-manager/prisma/schema.prisma:75 |
idx_admin_write_idempotency_expires_at |
WcTemplate |
seat_id, nonce, pubkey, beneficiary, canonical_tuple_hash, vault_address, withdrawal_credentials |
seat_id primary key, pubkey unique, vault_address unique in seat-manager/prisma/schema.prisma:91 |
primary-key lookup |
OperatorApiKey |
api_key, seat_id, created_at, scopes_json, revoked_at |
api_key primary key in seat-manager/prisma/schema.prisma:104 |
idx_operator_api_keys_seat_active |
AuditEvent |
id, ts, action, request_id, actor_id, seat_id, operator_id, operation_id, result, reason, governance_ticket, detail |
id primary key in seat-manager/prisma/schema.prisma:115 |
idx_audit_events_id_desc |
LifecycleEvidence |
id, seat_id, from_status, to_status, evidence_json, created_at |
id primary key in seat-manager/prisma/schema.prisma:140 |
idx_lifecycle_evidence_seat_created_desc |
Operation |
id, seat_id, kind, status, payload_json, result_json, last_error, processing_by, lease_expires_at, attempt_count, request_hash, idempotency_key |
id primary key in seat-manager/prisma/schema.prisma:152; live (seat_id, kind) unique for outstanding statuses in seat-manager/prisma/migrations/20260507080000_security_remediation_hardening/migration.sql:19; request hash dedupe in seat-manager/prisma/migrations/20260511090000_partial_operation_request_hash_dedupe/migration.sql:3 |
idx_operations_seat_created_desc, idx_operations_idempotency_key |
OperationTxIntent |
id, operation_id, action, target, calldata_hash, value_wei, sender_address, nonce, status, tx_hash, receipt_status, block_number, block_hash, confirmations, error |
id primary key, fingerprint unique, (sender_address, nonce) unique in seat-manager/prisma/schema.prisma:175 |
idx_operation_tx_intents_operation_id, idx_operation_tx_intents_fingerprint |
WatcherState |
state_key, state_value, updated_at |
state_key primary key in seat-manager/prisma/schema.prisma:204 |
primary-key cursor lookup |
Run migrations with ONBOARDING_POSTGRES_URL=postgresql://... npm run prisma:migrate:deploy; the command is documented in seat-manager/README.md:164.
Per-Model Detail¶
Seat¶
Seat records one operator enrollment and its lifecycle status. New rows are written by startup seeding through seedCreatedSeat, which inserts CREATED seats after checking the active-operator invariant in seat-manager/src/onboarding/prismaStore.ts:274. Register writes validator and vault columns in seat-manager/src/onboarding/prismaStore.ts:976; lifecycle transitions later update only status and updated time in seat-manager/src/onboarding/prismaStore.ts:1116.
The API reads seats for enrollment, status, admin lists, admin details, support bundles, and worker matching through getSeat and listSeats in seat-manager/src/onboarding/prismaStore.ts:339. Uniqueness is seat_id, pubkey, vault_address, nonce_consumed, and the partial non-revoked operator_id index in seat-manager/prisma/schema.prisma:10 and seat-manager/prisma/migrations/20260514165745_one_active_seat_per_operator/migration.sql:37. Rows are retained forever; revocation changes status to REVOKED instead of deleting history.
NonceLedger¶
NonceLedger records enrollment JWT nonces consumed by successful register. It is written inside the same register transaction that moves a seat to ALLOWLISTED in seat-manager/src/onboarding/prismaStore.ts:967. It is read by hasConsumedNonce before wc-template and by commitRegisterTx before consuming a nonce in seat-manager/src/onboarding/prismaStore.ts:477 and seat-manager/src/onboarding/prismaStore.ts:896.
The uniqueness constraint is the nonce primary key in seat-manager/prisma/schema.prisma:35. Rows are retained forever because replay protection must survive process restarts and later audit review; there is no prune job in the current store implementation.
Idempotency¶
Idempotency records operator-facing write responses for enrollment and day-2 endpoints. It is written by saveIdempotentTx for wc-template, register, claim, exit, migrate, and rotate flows in seat-manager/src/onboarding/prismaStore.ts:623. It is read before write processing by getIdempotent in seat-manager/src/onboarding/prismaStore.ts:591.
The uniqueness constraint is the composite primary key (endpoint, seat_id, idempotency_key) in seat-manager/prisma/schema.prisma:44. It rejects reuse with different payload hash in seat-manager/src/onboarding/prismaStore.ts:607. Rows are retained forever in current code; there is no TTL column and no pruning job.
AdminSession¶
AdminSession records server-side admin session state, CSRF token, expiry, step-up expiry, revocation time, and replacement session id. It is written at login and step-up in seat-manager/src/onboarding/prismaStore.ts:692, touched during session refresh in seat-manager/src/onboarding/prismaStore.ts:727, and revoked by logout or step-up replacement in seat-manager/src/onboarding/prismaStore.ts:750.
The admin auth service reads it on every session check in seat-manager/src/api/adminAuth.ts:207. Uniqueness is session_id; indexes cover user history and expiry in seat-manager/prisma/schema.prisma:57. Rows are retained as session history; expiration is enforced by expires_at checks in seat-manager/src/api/adminAuth.ts:204, not by a delete job.
AdminWriteIdempotency¶
AdminWriteIdempotency records replay responses for admin mutations such as revoke, retry, and cancel. The backend saves rows through saveAdminMutationReplay, which delegates to saveAdminWriteIdempotent in seat-manager/src/api/server.ts:1386 and seat-manager/src/onboarding/prismaStore.ts:808. It is read by replayAdminMutationIfCached before each admin write in seat-manager/src/api/server.ts:1372.
The uniqueness constraint is (endpoint, target_id, actor_id, idempotency_key) in seat-manager/prisma/schema.prisma:75. It has an expires_at column and an expiry index, and the store ignores expired rows in seat-manager/src/onboarding/prismaStore.ts:799. The current implementation does not include a delete job; expired rows stop replaying but remain stored.
WcTemplate¶
WcTemplate records the vault prediction and withdrawal credentials issued before register. It is written by rememberTemplate during POST /v1/seats/:seatId/wc-template in seat-manager/src/onboarding/prismaStore.ts:499 and seat-manager/src/api/server.ts:1545. Register reads it to reject nonce, pubkey, beneficiary, vault, withdrawal credential, or canonical tuple drift in seat-manager/src/onboarding/prismaStore.ts:905.
Uniqueness is seat_id, pubkey, and vault_address in seat-manager/prisma/schema.prisma:91. Rows are retained forever as enrollment audit evidence and replay guard; there is no TTL or prune path.
OperatorApiKey¶
OperatorApiKey stores hashes of operator API keys, scopes, creation time, and revocation time. Register writes the first key in seat-manager/src/onboarding/prismaStore.ts:1005, and rotate writes a replacement while revoking previous active keys in seat-manager/src/onboarding/prismaStore.ts:416.
Runtime auth reads this model through getSeatByOperatorApiKey and getOperatorApiKeyScopes in seat-manager/src/onboarding/prismaStore.ts:390 and seat-manager/src/onboarding/prismaStore.ts:401. The primary key is api_key; active lookup uses idx_operator_api_keys_seat_active in seat-manager/prisma/schema.prisma:104. Rows are retained with revoked_at for audit history; key expiry is enforced through Seat.operator_api_key_expires_at in seat-manager/src/onboarding/prismaStore.ts:395.
AuditEvent¶
AuditEvent is the append-only operational audit log. API routes call emitAudit, and the store writes events through recordAuditEvent in seat-manager/src/onboarding/prismaStore.ts:1780. Admin dashboard, audit page, seat detail, and support bundle read events through listAuditEvents in seat-manager/src/onboarding/prismaStore.ts:1805.
The uniqueness constraint is the autoincrementing id primary key, and reads use idx_audit_events_id_desc in seat-manager/prisma/schema.prisma:115. Rows are retained forever; no code path deletes audit events.
LifecycleEvidence¶
LifecycleEvidence records transition evidence for every seat status change. It is written only by transitionSeatStatus, after assertTransitionAllowed validates the required fields, in seat-manager/src/onboarding/prismaStore.ts:1089 and seat-manager/src/onboarding/prismaStore.ts:1130.
Status, admin seat detail, seat events, support bundles, and watchers read it through listLifecycleEvidence in seat-manager/src/onboarding/prismaStore.ts:1149. The uniqueness constraint is id, with read index (seat_id, created_at desc) in seat-manager/prisma/schema.prisma:140. Rows are retained forever because they are the state-machine audit trail.
Operation¶
Operation is the durable work queue for deposit, claim, voluntary_exit, and migrate. Register and day-2 endpoints enqueue new rows through enqueueOperation in seat-manager/src/onboarding/prismaStore.ts:1171. The worker claims rows with claimNextQueuedOperation, updates leases, and moves status in seat-manager/src/onboarding/prismaStore.ts:1303.
Admin routes and status read operations through listOperations, getOperation, and listOperationsForSeat in seat-manager/src/onboarding/prismaStore.ts:1426. Uniqueness is id, live (seat_id, kind) for outstanding statuses, and partial (seat_id, kind, request_hash) while not finalized in seat-manager/prisma/schema.prisma:152, seat-manager/prisma/migrations/20260507080000_security_remediation_hardening/migration.sql:19, and seat-manager/prisma/migrations/20260511090000_partial_operation_request_hash_dedupe/migration.sql:3. Rows are retained forever; finalization or failure changes status instead of deleting.
OperationTxIntent¶
OperationTxIntent records each planned or submitted transaction for an operation. The execution adapter writes or reuses intent rows through upsertOperationTxIntent in seat-manager/src/onboarding/prismaStore.ts:1477. It then records signed, broadcast, confirmed, reverted, or failed status through the intent status methods in seat-manager/src/onboarding/prismaStore.ts:1668.
Status, admin detail, and support routes read intents through listOperationTxIntents in seat-manager/src/onboarding/prismaStore.ts:1766. Uniqueness is id, (operation_id, action, target, calldata_hash, value_wei), and (sender_address, nonce) in seat-manager/prisma/schema.prisma:175. Rows are retained forever; failed nonce reservations without a transaction hash can have nonce released by the upsert logic in seat-manager/src/onboarding/prismaStore.ts:1525.
WatcherState¶
WatcherState stores durable cursors for background workers, including el:last_scanned_block, el:block_ring:<slot>, and cl:seen_epoch:<seatId>. The lifecycle watcher reads and writes those keys through getWatcherState and setWatcherState in seat-manager/src/onboarding/prismaStore.ts:1835.
The primary key is state_key in seat-manager/prisma/schema.prisma:204. The EL watcher advances el:last_scanned_block in seat-manager/src/watchers/lifecycleWatchLoop.ts:356, ring-buffer keys in seat-manager/src/watchers/lifecycleWatchLoop.ts:399, and CL seen epoch keys in seat-manager/src/watchers/lifecycleWatchLoop.ts:216. Rows are retained until overwritten by the same key; there is no prune job.