@guestcraft/db

The single owner of GuestCraft’s SQL (ADR 0010): the migrations, row-level security, the table inventory, the role bootstrap, the unit of work and the repositories that implement the ports of @guestcraft/core. The schema of the job queue, pg-boss’s, is installed by its migrations too (ADR 0019).

Entry point	For	Contents
@guestcraft/db	the runtimes (web, worker)	connectDatabase, the unit of work, DatabaseError, the table inventory
@guestcraft/db/auth	@guestcraft/auth only	AUTH_SCHEMA, connectAuthStore: organization access, workspaces, memberships and audit entries (ADR 0017)
@guestcraft/db/password-resets	@guestcraft/auth and the installation operator’s command	issueOperatorReset, resetSecrets, resetTokenDigest, ResetSecrets: one-time password reset links in plain SQL, with the budgets they spend; the migrate image loads neither Better Auth nor an ORM through it (ADR 0053)
@guestcraft/db/migrations	the migration step only	migrate, assertSchemaRevision, loadMigrations, guestcraftMigrations, MigrationError
@guestcraft/db/bootstrap	the migration step and tests	bootstrapRoles, applyGrants, roleNames, BootstrapError
@guestcraft/db/scheduler	@guestcraft/jobs and the composition root only	schedulerSql, the SQL executor of a unit of work’s scheduler handle; SCHEDULER_SCHEMA, SCHEDULER_SCHEMA_VERSION, the inventory of the job queue’s schema

import { connectDatabase } from "@guestcraft/db";

const database = connectDatabase({ connectionString: env.DATABASE_URL_APP });
await database.checkSchema(); // stops a runtime whose schema is not the expected one
const hotels = await database.unitOfWork.run(scope, ({ properties }) => properties.list());

Roles and connections

Each instance has three database users. PostgreSQL roles belong to the whole server, so the names carry an instance identifier of 4 to 16 lower-case letters and digits.

User	Used by	Rights	Connection
Owner (the user the provider gives you, such as neondb_owner or postgres)	the migration step	owns the tables; needs CREATEROLE for the bootstrap	direct or session pooler: the migrator holds a session advisory lock
gc_<instance>_app	the API	the grants of the inventory, nothing else; no BYPASSRLS, owns nothing	direct or transaction pooler
gc_<instance>_worker	the job worker	the same, with the worker’s grants	direct or transaction pooler

On Neon the pooled URL is the one with -pooler in the host name; on Supabase it is the transaction pooler on port 6543. The runtimes may use them because the unit of work leaves nothing on a connection: the tenant context, the timeouts (also lock_timeout, for a unit of work run with boundedLockWait) and jit = off are set with set_config(…, true) and end with the transaction. JIT is off because row-level security gives a unit of work’s short queries plan costs above PostgreSQL’s JIT thresholds, where compiling takes seconds and running takes milliseconds. Timeouts cannot be connection parameters there: PgBouncer refuses statement_timeout at connection time. The integration tests run the unit of work through PgBouncer in transaction mode to prove both. Standalone device/sign-in-limit queries also use local transaction bounds (2-second lock waits, 10-second statements); Better Auth applies them through its Kysely driver. Ordinary unit-of-work operations keep their existing bounds. See ADR 0040.

The deployment bootstrap creates missing runtime roles and preserves existing passwords, in one transaction. The standalone bootstrapRoles utility retains its password-replacement mode for controlled maintenance; deployment always requests preservation. Independent role secrets and a new role namespace allow a staged rotation without invalidating the serving pair; see the rotation procedure. Since PostgreSQL 16 a user with CREATEROLE may change only the roles it created, and only the attributes it has itself; the bootstrap says so when an existing role cannot be fixed. The passwords are sent in clear text in CREATE ROLE, because Neon refuses pre-hashed passwords: on a server that logs DDL statements, run the bootstrap with statement logging off, or change the passwords afterwards. Installations that create the roles themselves skip the bootstrap and provide the two URLs.

applyGrants runs after every migration: it revokes every privilege of PUBLIC and of the runtime roles on GuestCraft’s schemas, then grants what the inventory lists, in one transaction. Better Auth’s schema, guestcraft_auth, is reachable by the app role only. The scheduler schema grants the app only queue reads and job insertion; the worker runs the queue.

Tenant isolation

• Every workspace table has workspace_id as the first column of its primary key and of its foreign keys, so a row can never reference a row of another workspace.
• Row-level security is enabled and forced on every table, with one policy that compares the tenant column with app.workspace_ids or app.organization_ids.
• Identifiers are unique per workspace, not globally.
• Better Auth’s tables (guestcraft_auth) are global and have no row-level security; only the auth adapter uses them. A trigger copies each Better Auth organization to guestcraft.organizations, with the same identifier. Workspace memberships reference Better Auth’s members, so leaving an organization removes them.
• The audit log is append-only: the runtime roles may insert and read it, and a trigger refuses every change, the owner’s too, but one: the pseudonymization of an entry’s free-text details (migration 0060) by guestcraft.pseudonymize_details, which changes only data values to their keyed digest and sets pseudonymized_at once; the runtime roles have no update grant on it. An entry is visible to its organization, and to its workspace when it has one.
• The catalog test compares the migrated schema with the inventory: a table, policy, grant or function that is not declared fails the build. The RLS matrix is generated from the inventory for both runtime roles.

Errors

The unit of work converts failures to the error model of ADR 0008. Values never leave this package: Drizzle’s errors carry the SQL parameters and PostgreSQL’s carry the offending values, so only the SQLSTATE and the names of the constraint, table and column are kept.

Failure	Result
A refused statement: a constraint, row-level security, invalid data (SQLSTATE classes 22, 23, 42…)	DatabaseError
A lock not granted in time (SQLSTATE 55P03): lock_timeout of a unit of work run with boundedLockWait, 2 seconds for each lock it waits for once its transaction began, or a NOWAIT lock	LockUnavailable, an InfrastructureError that is retryable with retryAfterMs: nothing was applied
Serialization failure, deadlock, too many connections, a server shutting down or starting, a lost connection	InfrastructureError, retryable: nothing was applied
Statement timeout, idle-in-transaction timeout, out of resources, internal errors (classes 53, 57, 58, XX)	InfrastructureError, not retryable
A connection lost, or a server failure, during COMMIT	InfrastructureError with outcomeUnknown: the transaction may have been applied
A wrong password or database name	InfrastructureError, not retryable
Any error of the use case itself	passed through unchanged

Locks and their order

Writers serialize through a few lock families, each with a fixed acquisition order. A new operation must fit one of these orders or document its exception here and in its ADR. Runtime unit-of-work transactions wait with the server’s lock_timeout unless they run with boundedLockWait (2 seconds, see Errors); taking the acknowledgement fence sets the same 2-second bound for the rest of its transaction. Foreign-key checks take FOR KEY SHARE on the referenced row, which conflicts with FOR UPDATE: an insert that references a locked row waits for its holder. FOR NO KEY UPDATE, the lock of a change to no key column, neither waits for it nor blocks it.

Channex connection, then property. A transaction that takes more than one of these takes them in this order and never takes an earlier one after a later one:

1. the workspace’s acknowledgement fence, channex_ack_fences (lockOwnership, share or update);
2. the workspace’s sync row, channex_sync FOR UPDATE (bookingImport.connection(context, true));
3. the property row, properties FOR NO KEY UPDATE: the property gate (reservations.property(id, true), while messages.lockProperty uses the conflicting FOR UPDATE on the same row);
4. that property’s own rows: Messages’ rows, the ARI rows and those under Property writes.

Operation	Takes
Reconnect, account mode change	fence (update), the connection row, sync row; a reconnect then clears the stop of every ARI property row of the workspace
Webhook registration	fence (update), sync row
Mapping save, unlink, forgetting a pending removal	fence (update), sync row, property row
Webhook removal	the fence (share) across each DELETE; then fence (share) and sync row to end the removal
Acknowledgement	the fence (share) across the POST; the receipt is then recorded under the sync row alone
Revision intake and replay, webhook signals, drain settlement	sync row; then the mapped property row (intake) or ARI property rows (signals, settlement)
ARI’s gate: lease acquisition, the renewal before each send, an operator’s repair, mapping or resync, a target retirement’s start, cancel and resume and the run’s retirement transitions and mapping switch	sync row, property row, then the ARI property row, the retirement row and the property’s rate and room mapping rows
Messages’ serialized decisions: activation, deactivation, unlink, confirm, forget, stop steps, in-flight sweeps	sync row, property row, then the activation row, install attempt, revision row and outbox rows (ADR 0044)
Messages drain storage	the activation row (FOR NO KEY UPDATE), then thread, message and outbox rows; never the sync or property row afterwards
Messages outbox claim	its own transaction: the selected outbox row alone, FOR UPDATE … SKIP LOCKED

Online payments. Every write of payment rows takes the property row first (the property gate), then payment_requests, provider_payments, provider_refunds, payment_cards and payment_card_tokenizations rows FOR UPDATE, then folio rows; the payment connection row is locked alone (configuration, and storing a provider delivery). booking_payment_facts rows are written by revision intake after the mapped property row, in its order above. This holds for staff commands, for applying provider facts, which finds the property of the object without locking and then takes the gate (ADR 0054), and for reconciliation. No lock is held across a provider call.

Of these locks, only the fence is held across a Channex call (an acknowledgement POST or a webhook DELETE). A Messages call’s admission reads the activation without that row lock and commits before the call, so a call admitted before a stop can still be in flight; storage checks the activation again under its lock. An operator’s decision that takes the sync and property rows runs with boundedLockWait and answers channex.property_busy beyond the bound. Messages’ serialized decisions use the same bound in operator and worker paths alike, failing with LockUnavailable; ARI push and ordinary import worker transactions keep the server’s lock_timeout.

Property writes. Every local write to a property (reservations, rates, folios, policies, blocks, housekeeping tasks and settings, maintenance tickets and photos, property settings, night audits) first takes the property gate and never takes the fence or the sync row (ADR 0024). The gate also covers inventory rows that do not exist yet, so the ledger takes no lock of its own:

• inventory deltas are one set-based statement in (night, room_type_id) order (applyInventory); a block’s adjustment writes one room type’s nights (blocks.adjust);
• each folio movement insert takes a transaction advisory lock on its workspace and reservation (whose folios share one ledger), in the validate_folio_posting trigger;
• a guest edit locks the guest row (FOR UPDATE) without the gate; the booking import locks it after the property row.

ARI lease and stop. The lease is not a PostgreSQL lock: it is the generation, owner and expiry on the property’s ari_properties row (ADR 0034). acquire starts a new generation only under ARI’s gate and only when no lease is live, no pause runs and no stop is set. Renewal (heartbeat), stop and release name the holder’s generation and owner, so they do nothing for a run that lost its lease; claimed changes record both too. stop records a stop code only for the current holder, and not in a generation an administrator resumed: a repair or an enabled publication (under the gate) and a saved connection (under the fence) clear it and record that generation in resumed_generation. Release, back-off and stop each run in their own transaction, without the gate. Change claims use FOR UPDATE … SKIP LOCKED.

Identity. These transactions take no explicit Channex, property or inventory lock; a membership or an acceptance limited to selected properties references them, so its insert waits for a property writer that holds the gate:

• a team write locks the organization row (guestcraft_auth.organization, FOR UPDATE) first, then the rows it changes: the invitation and its verification row, memberships, a sign-in lockout it lifts;
• an invitation acceptance locks the organization row, then the invitation and its verification row, then takes a transaction advisory lock on the invited address (hashtextextended('team-account:' || email, 0)) before it looks up or creates the account;
• a sign-in attempt reserves its lockout row in one statement; cleanups use SKIP LOCKED;
• device sessions follow the per-writer orders in ADR 0041; the exchange of a sign-in or pairing code takes the person’s row, the browser session’s key, the code, then the device session; the withdrawal of a pairing code takes the person’s row, then the code. Creating a device session takes a key-share lock on its organization row, so it waits for a team write holding that row; a team write locks no person, code or device session row.

Outbound webhooks. An endpoint’s row before its deliveries’ rows (ADR 0040); none of them takes a Channex, property or identity lock:

• administration (an update, a rotation, a test, a deletion) locks the endpoint row (FOR UPDATE) first; a deletion then deletes its deliveries by cascade, and a test adds or updates its test delivery;
• recording a batch’s outcomes locks each endpoint in identifier order, each in a transaction of its own, then that endpoint’s deliveries in identifier order;
• a registration takes a transaction advisory lock on its workspace (hashtextextended('webhook-endpoints:' || workspace, 0)) before it counts the endpoints;
• a claim locks deliveries alone (FOR UPDATE … SKIP LOCKED), the outbox is claimed by deleting its rows, and retention deletes expired deliveries alone; a fan-out’s inserted deliveries reference their endpoint, so they wait for administration holding it;
• forgetting previous secrets whose window ended locks endpoint rows alone, in identifier order, FOR NO KEY UPDATE, in a transaction of its own. A fan-out’s inserted deliveries take FOR KEY SHARE on their endpoints in the order of events × endpoints, not identifier order: the two never wait for each other, while administration’s FOR UPDATE still waits for forgetting and forgetting for it;
• secret re-encryption locks ciphertext batches FOR NO KEY UPDATE (see Migrations below). It changes no key, so a fan-out’s foreign-key FOR KEY SHARE locks can proceed in another order. Concurrent edits, deletions and administration still wait for the rewritten batch; the cursor row still serializes rotation commands.

Migrations. The migration step holds a session advisory lock (the key is the bytes of gcmigrat) from just after it marks its session, through the probe, preflight, migrations and finalizer, to the unlock; runtimes never take it. Inside it, the scheduler installation migration takes pg-boss’s transaction advisory lock, with its own 30-second lock_timeout; expand migrations set 5 seconds. See Migration diagnostics. Credential rotation (rewriteChannexCredentials) locks its cursor row first, then one batch of one kind of row in cursor order (webhook secret batches lock sync rows, endpoint secret batches endpoint rows), all FOR NO KEY UPDATE because only ciphertext changes. Writers and deletions still conflict; foreign-key key shares proceed. The cursor retains FOR UPDATE; it is owner-only and nothing references it, so the stronger mode costs nothing while it serializes rotation commands. Rotation takes no fence or property row.

Migrations

ADR 0011 explains the choices. In practice:

• A migration is a file migrations/NNNN_snake_case.sql, numbered without gaps. The runner applies each file in its own transaction, together with its entry in guestcraft_meta.migrations, so a file never contains BEGIN or COMMIT.
• Migrations are append-only. The journal keeps the checksum of each file, and a file that changed after it was applied stops the migration step. A fix is a new migration.
• Declare every file as expand or contract in migrations/kinds.json; when unsure, use contract. Run pnpm --filter @guestcraft/db migrations:revisions and pnpm --filter @guestcraft/db migrations:lint to generate runtime revisions, parse expand SQL and check the grant floor. The runner adds the journal’s kind column when needed; no numbered migration owns it. See ADR 0038.
• A function created by a migration starts executable by PUBLIC: the same migration revokes it (a test checks it right after migrating, before the grants).
• src/schema.ts holds the Drizzle definitions used to write queries. A test compares them with the migrated catalog, column by column; the SQL is the source of truth.
• The migrations of the job queue (NNNN_scheduler_install_vXX.sql, then NNNN_scheduler_upgrade_vXX.sql) are generated, never written by hand. After changing the version of pg-boss in the catalog, run pnpm --filter @guestcraft/db scheduler:migration. It writes the next migration from pg-boss’s own plans, without their BEGIN and COMMIT. Then set SCHEDULER_SCHEMA_VERSION in src/scheduler/index.ts, review the SQL, and add to SCHEDULER_TABLES what the roles need. A unit test fails until the versions agree; the integration tests of @guestcraft/jobs ask pg-boss whether the migrated schema drifts from what it expects. An upgrade that recreates or re-partitions guestcraft_jobs.job must keep the normalize_job_output trigger, and the failure-output integration tests prove it.
• A queue’s policy cannot change through pg-boss, which refuses it, nor through registration, which refuses a stored policy the catalog does not give. When a job’s policy changes (it becomes a singleton, or stops being one), write a queue-policy migration NNNN_scheduler_queue_policy_<name>.sql, declared contract, whose only statements are update guestcraft_jobs.queue set policy = '<policy>' where name = '<queue>' (standard or stately) and, for the same queues, delete from guestcraft_jobs.job where name = '<queue>' and state in ('created', 'retry'). No other migration touches guestcraft_jobs, except those an amendment of ADR 0019 decided (scheduler_safe_output). migrations:lint and the scheduler’s unit test check these forms (src/migrations/scheduler-forms.ts), and refuse a generated file edited by hand.

A new table needs, in the same pull request: the migration with its RLS policy, an entry in src/inventory.ts with its scope and grants, its Drizzle definition, and a repository behind a core port when the domain uses it. The catalog and RLS tests then cover it without changes. A new Better Auth table goes in guestcraft_auth, with Better Auth’s own names as its CLI generates them, and an entry in AUTH_TABLES. Every new workspace business table also needs the restrictive workspace_active policy with the uncorrelated active-workspace array guard; migration 0044 discovers all existing workspace tables through pg_catalog, and the catalog test rejects later tables without protection.

Tests

pnpm test runs the unit and property tests, without Docker. pnpm test:integration starts PostgreSQL 17 and PgBouncer with Testcontainers (@guestcraft/testing), prepares a template database the way a deploy does (owner, bootstrap, migrations, grants) and gives each test file its own copy. They connect as the runtime roles, which cannot bypass row-level security.

Inventory and reservations

Migration 0006 adds units, guests, reservations/rooms/nightly prices, stay segments and inventory days, with forced RLS and composite references. The transaction-bound reservations capability implements the core port. Property row gates serialize writes only; reads use repeatable-read read-only snapshots with RLS context. Inventory deltas, changed nightly prices and segments use ordered/bulk SQL. A GiST exclusion constraint independently protects active unit assignments. PostgreSQL tests cover the last-room race, reverse request order, every new table’s tenant matrix and rollback. See ADR 0024.

Migration 0022 lets a booked room keep several consecutive stay segments, each recording the room type its nights consume, for moves, splits and upgrades (ADR 0042). Migration 0011 adds dated out-of-order and out-of-service unit blocks and maintenance tickets, with forced RLS (ADR 0033).

The unit of work accepts { readOnly: true } as its third argument to run. The fold_search(text) inventory function resolves unaccent in its actual extension schema; its fixed-path definer scope permits private extension schemas without broad grants.

Team management uses the identity invitation and verification tables through the narrow db/auth team store, the custom roles and property scopes of migration 0016 and the sign-in lockouts of migration 0020, then scoped transactions for memberships, audit and events; see ADR 0030.

Reservation folios and manual settlement are documented in the folio workflow and ADR 0029. The ledger, frozen room charges, property extras, manual payments/refunds, a check-out that settles only at the last room and never holds a credit (an owed balance needs settlement or an audited administrator approval), refunds due, net and full-ledger views, a non-fiscal statement, and split folios (payers, routes, transfer pairs, closing and per-room settlement, migration 0038: folio headers change only in their payer, name, room, routes and state; movements share the reservation’s keys and positions) are implemented. Taxes need a country module on the TaxCalculator port (with a re-rate operation); fiscal documents and online providers remain planned.

Rates and ARI publication

Migration 0007 adds rate plans, sparse nightly rates/restrictions and ARI change rows, plus calendar revisions and reservation-room price provenance. The RLS matrix covers every new table. Set-based writes update 20 × 366 cells within a constant statement budget. Rate data, audit/events and pending ARI rows commit together. See rates and ADR 0027. Migration 0013 adds the numbered-lease publisher, exact generation-bound claims, mappings, recorded sends and verified daily values; migration 0014 adds the repair, booking-barrier, mapping-version and recovery-cursor columns; migration 0015 timestamps unresolved booking holds (barrier_since) for bounded degraded escalation. New tables retain forced RLS and composite property/tenant keys; see ADR 0034.

Migration 0026 (expand) adds ari_properties.stop_code, the reason a property’s ARI publication stopped on a failure that does not heal by itself. While it is set, the property is not leased, requested or scheduled; a requested repair, an enabled publication or a saved connection clears it and records the lease generation of that resume in resumed_generation. Only the current lease holder stops a property, and not in the generation an administrator resumed (ADR 0034).

Channex booking import

Migration 0008 adds composite-key Channex property/room mappings, connection ownership, immutable revision metadata, separate encrypted personal content, processing receipts and thin signals. All its tenant tables have forced RLS and catalog/matrix coverage.

Migration 0018 defaults connections to shared mode, adds conditional account-mode versions, property recovery cursors, retained mapping/cleanup flags and active-target uniqueness. Unlink retires queued ARI work while preserving reservations, receipts and diagnostics. RLS and inventory grants cover the transaction.

Migration 0024 moves retired upstream target fences into their own workspace-scoped table and backfills inactive links, so reusing a local mapping row cannot reopen exclusive intake. It marks existing unknown-room-type quarantines as manual-only and tracks the last connection version at which a new quarantine was retried; a later connection edit permits another automatic attempt. Transient replay failures back off per receipt (replay_attempts, replay_after), and the last failure (replay_failure) is shown in sync health. Its two tenant tables (channex_unlinked_targets, channex_ack_fences) have forced RLS and inventory-backed grants; it also adds the owner-only credential rotation cursor and key-fingerprint tables. It is declared expand in migrations/kinds.json. rewriteChannexCredentials re-encrypts connection credentials, Channex webhook secrets, Messages records, endpoint signing secrets and revision archives in committed batches of at most 500. An owner-only cursor in guestcraft_meta.channex_credential_rotation resumes after a crash; archive batches do not hold connection or sync row locks. The old key remains required until completion.

Channex acknowledgement admission uses the dedicated tenant-scoped channex_ack_fences row, created lazily for compatibility with older connection writers. An acknowledgement commits a missing row in a transaction of its own (ensureOwnershipFence) before it holds it. Acknowledgements, webhook removals and ownership changes lock it; sync intake, webhook signals and ARI start at the sync row. A pending webhook removal records in cleanup_key_changed that the connection key changed after it was left pending (cleanupPropertyIds, markCleanupKeyChanged), and in cleanup_generation which unlink started it: endRemoval ends only the pending removal of the target and generation that pendingRemoval read. The owner-only guestcraft_meta.channex_credential_keys records a version-bound fingerprint of every key version that rotation read or wrote a value with, and maintenance refuses other material for a recorded version (CredentialRotationRefusal, with a fixed message per reason). Completion rechecks every ciphertext in read-only batches with the target key alone. See ADR 0028 and the installation guide for timeout coupling and key recovery.

Channex Messages

Migration 0025 adds encrypted Channex Messages properties (an active one always records GuestCraft’s own installation), install attempts of unfinished activations (preparing, pending, installed or unconfirmed, kept until settled), threads, sync markers, messages, outbox, requests and templates, plus the channex_message_workspaces safety-job function and built-in permission grants. Restrictive RLS preserves selected-property scope. Active queries require a linked, room-mapped property. Key rotation (rewriteChannexCredentials) re-encrypts their records with the other Channex values. Existing migration SQL stays immutable.

Property performance reports

The transaction-bound reports capability aggregates period, summary and source sufficient statistics in one SQL statement after the property and history-boundary reads. It runs in a repeatable-read read-only unit of work with RLS, no guest projection and no row locks. Migration 0030 retains reporting observations; unit/type validity intervals are intersected before joining stay dates, including historical OOO state, without multiplying the capacity denominator by reservation facts. The 2,100-reservation/365-day budget test remains below five seconds. See the reporting history section, operator and contract guide and ADR 0031.

Booking policies and no-shows

Migration 0010 adds property policies with forced RLS, the immutable conditions sold with each room and their decisions, for simulated cancellation fees, explicit no-show and recovery, guarantee attestations and prepayment tracking; lifecycle, inventory, policy ledger charges, audit and events share one transaction. See the policy workflow and ADR 0032 for permissions, deadlines, migration semantics, SDK operations and planned OTA reporting/provider capabilities.

Roles and property access

Migration 0016 adds custom roles, property-scoped memberships and invitation property scopes. The unit of work sets transaction-local property IDs. Restrictive RLS protects property rows and indirect folio children, including policies and physical blocks. Migration 0017 removes retired tables, functions, a guest preference column and stored permission grants while keeping earlier applied migration files immutable. Built-in role templates remain immutable. See ADR 0036.

Migration 0023 adds the global guestcraft_auth device sessions and device authorizations of the mobile app, without row-level security and reached by the app role only through auth (ADR 0041).

Delegation is bounded by the actor’s live permissions, property context fails closed, team control tables are protected and audit deltas are safe. See the role/access contract.

Housekeeping and maintenance

Migration 0039 (contract) adds housekeeping_settings, housekeeping_days and housekeeping_tasks, whose unique (workspace_id, property_id, origin_key) makes a second copy of a generated task impossible; it gives maintenance tickets a priority, an assignee, dates, their source task, reporter and closed_at (backfilled from updated_at for closed tickets), the status in_progress, an append-only activity table and photo metadata, and adds stored_files, the PostgreSQL implementation of the core FileStore port (bytes by key per workspace, reached through the photo rows, which carry the property). Every new property table has the tenant and the selected-property policies. workspace_administrators(uuid) (app role) names the organization owners and admins of a workspace in the context, so that housekeeping.workers can list who holds a permission at a property without the unit of work reading Better Auth’s tables. The migration also grants housekeeping.assign and housekeeping.configure to the built-in templates. It is contract: it replaces the ticket status check and changes role templates, which a previous runtime cannot honour. See ADR 0046.

Migration diagnostics

The deployment finalizer (migrate’s finalize option) runs under the same session lock as SQL and journal validation, including roles, grants and queue registration. Older code fails before that finalizer. Guest writes explicitly supply origin_property_id; the unit of work never chooses a guest owner from property selection order.

Deployment checks existing role logins under the lock before any schema writes (the preflight option), then checks newly bootstrapped roles again. Migration 0019 drops the legacy guest-owner column default; all application writers supply ownership explicitly.

Migrations need one PostgreSQL session from the lock to the unlock: a direct connection, a 1:1 proxy such as Neon’s endpoints (the unpooled URL included, although Neon’s proxy announces its own cancellation key), or a session pooler such as Supabase’s on port 5432. A transaction pooler is refused before any SQL, by what it does rather than by its address:

• the first statement marks the session with a random session-level setting, and the lock is taken in one statement only on the backend that carries the marker, so a lock is never left on a pooled backend this client cannot reach again;
• under the lock, a probe connection with the same URL (the probe option) must neither see the marker nor take the lock; a pooler with one server connection hands it the same backend and fails both;
• each migration transaction, the finalizer and the unlock check the backend and the marker again; a pooler with several server connections loses them between transactions.

The deployment command also refuses the known transaction-pooler addresses (Neon’s -pooler hosts, Supabase’s port 6543) in its configuration, before connecting. Runtime connections may still use transaction pooling.

Code	Meaning and recovery
migration.session_required	The migration URL does not keep one PostgreSQL session: it goes through a transaction pooler. Use the direct URL (on Neon, the unpooled one) or a session pooler (on Supabase, port 5432). Nothing was applied, and the lock is not left held.
migration.session_probe_failed	The probe connection that checks the session could not open or answer. An optional validated SQLSTATE says why, such as 53300 (too many connections). Nothing was applied; free a connection and retry.
migration.statement_failed	A numbered migration transaction failed and rolled back. An optional validated five-character SQLSTATE distinguishes permission (42501), lock (55P03), arithmetic (22012), extension or other database failures without printing SQL or values. Earlier migrations in the batch remain committed.
migration.unexpected_failure	An unclassified connection/deployment error. Check the migration URL, owner privileges, extensions and the journal privately; arbitrary upstream text is withheld.
database.credentials_mismatch	A runtime role was refused at login with SQLSTATE 28P01 (wrong or expired password) or 28000 (for example a role that may not log in). Check GUESTCRAFT_DATABASE_ROLE_SECRET (legacy fallback: AUTH_SECRET); preserve the serving secret or stage a new role ID. Existing-role failure occurs before schema writes.
database.role_login_failed	A runtime role could not log in for another reason: the validated SQLSTATE when the server gave one, such as 53300 (connection limit), or none for a network or TLS failure. The secret is not the problem; retry or check the database. Existing-role failure occurs before schema writes.

Journal errors also explain missing, changed, gapped or newer revisions using only version numbers. A runtime verifies its own checksum and kind prefix, tolerates only a newer contiguous expand suffix and logs the versions/names it accepted. A contract suffix needs roll-forward. Runtimes built before the compatibility check retain strict journal equality and cannot cold-start after an unknown migration commits.

Sign-in limits

Migration 0020 keeps the account lockout in guestcraft_auth.sign_in_lockouts, GuestCraft’s own table, apart from Better Auth’s rateLimit table, whose cleanup deletes rows older than one minute: an account digest, its attempts and the start of its 15-minute window. A lockout row goes only once its own window has ended, on a successful sign-in, or by an audited unlock; a reservation deletes at most 100 of them, so its work is bounded. The digest is an HMAC of the folded address under the 32 random bytes of guestcraft_auth.sign_in_lockout_key, which the migration generates once (gen_random_uuid draws from PostgreSQL’s strong random source) and the app role may only read: rotating the auth secret leaves every lock in place. @guestcraft/db/auth exports signInLockouts (reserve, clear, the digest of an address, and the team.sign_in_locked audit entries of a lock) and clientRateLimits, the storage of Better Auth’s per-client limiter, which reads the bigint timestamps as numbers. Like Better Auth’s tables, both lockout tables are global, without row-level security, and reached by the app role only.

Night audit

Migration 0021 adds the property business date and night_audits. Property business dates, automatic and manual night audits are described in the workflow contract and ADR 0043. The night audit keeps the existing check-in charge timing; local-time scheduling, configured automatic no-shows and fee-free expired options share the same durable day-close path.

API keys and webhooks

Migration 0027 adds api_keys (SHA-256 digests only), webhook_endpoints (sealed signing secrets), webhook_deliveries and webhook_outbox, all with forced row-level security and a restrictive policy that only an unrestricted context passes; members limited to properties append outbox rows with their reservation changes but read none. The event log inserts an outbox row with each event of a type webhooks deliver, in the event’s own statement, so that delivery adds no round trip to a use case and a rollback leaves neither. api_key_credential(text) (app role) finds a key by digest before any tenant context, and waits at most 2 seconds for a lock (migration 0028: the client’s query timeout would leave the server query waiting); the credential store turns any failure of that read but a refused statement or one a retry does not repair into a retryable InfrastructureError with retryAfterMs. webhook_workspaces(timestamptz, timestamptz) (worker role) names up to 1,000 workspaces with outbox rows, due deliveries or expired ones, the longest-waiting first. The delivery job claims outbox rows by deleting them and leases due deliveries with for update skip locked, never past their attempts; outcomes lock the endpoint before its deliveries, as a deletion does. A registration counts endpoints under a transaction lock per workspace, and a unique partial index keeps one pending test per endpoint. rewriteChannexCredentials also rewrites endpoint signing secrets. See ADR 0040.

Migration 0034 (integrations_complete, contract) completes ADR 0040: audit_log.api_key_id with the api_key actor (whose actor_id is the key’s creator); api_keys.allowed_ips (cidr[]) and replaced_by for rotation (a self-reference set to null when the replacement is deleted with its creator); api_key_credential created again to return the allow-list, with its 2-second lock bound; domain_events.public_subject, the property and reservation a public event names, written in the event’s own statement so that an event never changes (null for older events, which are completed when read); an expiry check that lets a rotated key expire at once, even at its creation instant, while a new key still expires in the future; webhook_deliveries.origin (event, replay, redelivery), with one fan-out delivery per endpoint and event and at most one pending delivery per endpoint and event as two partial unique indexes; and events.read in the administrator template. repositories/public-events.ts reads the catalog’s events (publicEvents capability) and resolves each event’s property and reservation, one query per kind of object; the events of a page are filtered by subject kind in SQL, before its limit.

Migration 0041 (api_platform, contract) adds the credentials of ADR 0047: organization_tokens (organization scope: only a context holding the organization, its owners and administrators, reads or writes them; digests only) and client_sessions (workspace scope, unrestricted contexts only, deleted with the API key that minted them and unusable once their organization token is gone, an expiry within 24 hours and a user identifier that cannot hold an e-mail address), the app-role functions organization_token_credential(text) and client_session_credential(text) (the latter with its minting credential’s grant) with the 2-second lock bound, the audit actors organization_token and client_session with their own columns, and client_sessions.manage in the administrator template. repositories/organization-tokens.ts and client-sessions.ts implement the core ports; auth/organization-tokens.ts and auth/client-sessions.ts are the credential stores.

Migration 0059 (exchange_rates, contract: it changes an existing policy and the role templates) adds exchange_rates, an organization table readable and writable from a context that holds the organization or from an active workspace of it (app.workspace_ids joined to non-archived workspaces), without adding the organization to anyone’s context; restrictive policies limit inserts and deletes to unrestricted contexts. A unique index on the unordered currency pair, day and source keeps one rate per pair and day in either direction. The WITH CHECK of audit_log.tenant_isolation also admits an organization entry (no workspace) appended from an active workspace of that organization; reading organization entries still needs the organization in the context. exchange_rates.manage joins the administrator and accounting templates. repositories/exchange-rates.ts and report-workspaces.ts implement the core ports (exchangeRates, reportWorkspaces capabilities).

Migration 0064 (scoped_client_and_guest_sessions, contract) gives client_sessions a scope (properties for every existing row, reservation, guest or property_public) with the columns of each kind, a check that refuses contradictory or missing fields, and composite foreign keys (a reservation always at the scope’s property) that delete a session with what it names; client_session_credential(text) returns the scope and finds no session of a merged guest. It adds guest_sessions, the first-party guest sessions (workspace scope, unrestricted contexts only, at most an hour, permissions of the guest vocabulary its scope allows), and the app-role lookup guest_session_credential(text), which finds none of an archived workspace or a merged guest. repositories/session-scopes.ts maps the scope columns both tables share; repositories/guest-sessions.ts is the unit-of-work port and guest-session-lookup.ts the Database.guestSessions lookup. Both guest_id columns keep their identity through a merge (preserve).

Migration 0068 (oauth_applications, contract) adds OAuth applications: Better Auth’s OAuth provider tables in guestcraft_auth (oauthClient, oauthResource, oauthClientResource, oauthRefreshToken, oauthAccessToken, oauthConsent, and oauthClientAssertion, which stays empty; list fields as JSON text, tokens as the SHA-256 hex of their whole secret), and oauth_grants (workspace scope, unrestricted and active contexts only), what a member authorized one application to do in one workspace, at most one active grant per workspace, application and member (a partial unique index, which a consent’s upsert meets), deleted with its application. The provider’s consent, codes and tokens name a grant by its identifier (referenceId). oauth_token_grant(text) (app role, 2-second lock bound) finds the grant of an access or refresh token by digest, refusing a token of another application or member, a revoked token and a sender-constrained one; oauth_pending_clients(integer) (app) counts the applications never authorized, up to a cap; oauth_cleanup(timestamptz, integer) (app and worker) is one bounded pass of their lifetime rules. Revoking a grant (repositories/oauth-grants.ts) deletes its tokens and consent in the same transaction. The audit actor oauth_application names the grant (oauth_grant_id), and oauth_applications.manage joins the administrator template. auth/oauth-applications.ts is the credential store. See OAuth applications.

Channex ARI and booking import completion

Migration 0042 (channex_ari_import_complete, contract: the expand policy admits no new privileged function) completes ADR 0034:

• ari_sends.lookups and looked_up_at: provider lookups of a send whose outcome is unknown, at most lookupLimit, lookupSeconds apart by database time. ari.lookedUp records one only while the lease holds and the send is still unknown; an applied lookup settles the send with the same statements as settle, a not-applied one returns its changes for recomputation with the statement of the bounded repair. nextDelay also wakes for the next due lookup.
• ari_drift_checks: one nightly comparison per property and local night (pending, completed or failed after driftAttempts failures; a deferral or failure waits driftRetrySeconds), 60 nights kept. ari.forget deletes a drifted availability night and removes drifted restriction fields from ari_published, so that they are published again. ari_drift_properties(text, timestamptz, integer) (worker) and ari_drift_due (app and worker, the request pump’s probe) find the publishing properties of this instance, with a mapped rate plan, from driftLocalHour of their own local night, not yet checked that night or with a due retry.
• channex_alerts: per-property conditions for an operator, at most one open per kind (a partial unique index, so concurrent raises open one). channex_alert_properties(text) (worker) names the linked properties of this instance and those with an open alert, so that alerts resolve after an unlink.
• channex_history_imports: imports of a property’s existing Channex bookings, created by the app role and run by the worker, at most one queued or running per property (a partial unique index), resumable after their cursor. The requesting user is an identifier without a foreign key, as in the audit log.

The three tables reference their property with ON DELETE CASCADE and have forced tenant and property policies. The worker may also delete ari_published rows (drift repairs).

Migration 0029 (scheduler_safe_output) installs an invoker trigger on the scheduler’s partitioned job table. Both output columns accept only fixed failure reports, including direct writes to job_common; retained outputs are scrubbed in the migration transaction. The normalizer has app/worker execute grants because inserts invoke it; the trigger function has no runtime execute grant. Neither function reads data or elevates privileges. This is a contract migration (existing-table trigger and backfill), so deployment recovery is roll-forward; allow for a scan of retained job outputs during the migration. Creating the trigger and scanning the retained rows pause job writes, including app enqueues; the measured pause was 2.9 seconds for 1.05 million rows, and the migration gives lock acquisition a five-second bound.

Receivables

Migration 0057 adds them with forced RLS, the active-workspace and property policies, and triggers that tie each folio transfer to its item and bound allocations under advisory locks. Counterparties are an organization directory reached from the organization’s active workspaces; open items, their append-only entries (debits, allocations, write-offs, releases, cancellations) and receipts belong to one workspace. Closing a folio linked to a counterparty books one receivable_transfer movement and opens its item; reopening reverses it exactly. OTA-collected bookings open expected payouts from the revision’s facts. See the receivables guide and ADR 0051.

Audit automation and cash drawers

Night audit settings, configured no-shows and expired tentative options share the durable property-date audit workflow. Cash drawer shifts provide currency-aware opening float, server-linked cash activity and versioned counted closure. See the night audit contract and cash shift contract for public operations, permissions, recovery, persistence and the Vercel daily-trigger cadence limit.

Guest identity references

src/repositories/guest-references.ts is the explicit inventory of guest identity foreign keys and their merge policy. The merge helper re-points its live references; consents, dismissal pairs, tombstone pointers and provenance preserve their original identities. guest-reference-coverage.integration.test.ts queries every foreign key targeting guests.id in the migrated PostgreSQL catalog and rejects an unlisted column. Folio payers (folios.payer_guest_id) re-point with a folio version bump. Add future guest references to this inventory with a reviewed policy and behavioral coverage. See ADR 0045.

Guest identity review persists email, phone and folded-name keys maintained by a trigger on every guest write, backfilled and indexed for live identities. The domain’s ordered key rules generate SQL normalizers, and a drift test pins the migration. Email padding means ASCII spaces; name folding uses NFKD, combining marks U+0300–U+036F and the explicit Latin replacement catalog. PostgreSQL and the memory adapter run the same normalization examples. Candidate queries union equality matches, aggregate reasons and filter dismissals before strength-ordered paging. Shared keys with more than five visible members produce bounded previews and an indexed paged membership read. Group thresholds and counts use RLS-visible rows; they never reveal hidden workspace membership.

Ordinary identity/reference writers hold a shared workspace advisory gate. Only merges hold it exclusively, before sorted guest row locks; property writers retain property → gate → row order. A materialized property-lock query acquires the shared gate before any reservation mutation read; subsequent READ COMMITTED statements see a waiting merge’s committed versions before checking the caller’s version. The reservation reference trigger resolves visible tombstone chains under that shared gate. Merge updates reservation versions and timestamps; core records each moved reservation’s ordinary update audit/event atomically. The property gate uses FOR NO KEY UPDATE: writers still serialize and property deletion/key changes still wait, while merge ownership FK checks can take KEY SHARE without a lock cycle. Consent evidence persists the guest version for reliable same-instant current/history ordering. Reservation ota_contact JSON stores incoming contact evidence separately from the operator-owned identity, without operator notes. guest_contact_reviews records a review timestamp per guest field and all permanently dismissed exact values (including null). Notice reads compare each field independently, exclude reviewed or previously dismissed values, and page newest snapshots first using revision time and reservation id. Review writes use the ordinary shared guest gate and locked guest version; the current visible reservation snapshot must match the submitted revision and exact value. Reviews retain their original identity on merge, follow guest visibility through RLS, and cascade with their workspace/guest parents. Migration 0035 introduces guest merges and consent evidence. Contract migration 0036 adds these review rules without changing migration 0035; both follow the reporting migrations 0030/0031 and need the matching runtime.

The merge-managed identity flag is persisted and server owned, backfilled from existing provenance. Its value is independent of whether a property-scoped reader can see the absorbed identity. The app role may read provenance link metadata only; column grants protect former contact snapshots, which only the worker reads, for an access export. Both roles redact them for an erasure or retention without reading them, and redacted_at records it (see Guest data rights); RLS requires both linked identities to be visible.

Reporting history

Migration 0030 starts a PII-free reporting baseline for each existing property. New properties receive their own baseline. All three history tables cascade on property deletion because their rows are derived reporting data. Runtime roles can only read report_history_start, report_reservation_history and report_inventory_history; fixed-path trigger functions retain only sold frozen nights and inventory lifecycle facts. Reservation capture is deferred until commit so a header, its rooms and repriced nights form one snapshot; every supported reservation writer uses saveReservation, including OTA import. Metadata-only changes do not add snapshots. Unit/type archive and restoration and block changes retain observation times with clock_timestamp() after writer locks; housekeeping and label edits add no inventory history.

Migration 0058 adds cancellation_events: one append-only row per room and saved reservation version that cancels or reinstates it, written by reservations.saveCancellationEvents in the same unit of work as saveReservation (the reservation saver and the Channex import). Runtime roles may only select and insert it; a repeated transition is ignored by its primary key. Rows cascade with their property, reservation and room. report_history_start.cancellations_from records when capture began for each property; nothing earlier is reconstructed.

Pickup compares the last observed reservation state before the end of each property-local as-of date. Historical capacity uses each stay date’s end-of-day unit/type and OOO block state; future dates project the latest observed state. Removed or edited blocks do not rewrite prior dates. Pickup refuses pre-baseline observations because prior versions did not retain reservation history. Performance accepts older ranges using first inventory states recorded by the end of the history_from local day, including rooms added during a new property’s setup day, labelled with pre_history_nights (count of stay dates, not room-nights); later-created entities first observed on later local dates still start at creation. Ledger reports remain independent of that boundary: they aggregate append-only postings by each movement’s service_date, following folio identity rather than a one-folio-per-reservation assumption, and explicitly allow only charges/charge reversals for revenue or payments/refunds and their reversals for cash. Monetary sums are checked for safe minor-unit integers before reaching the domain. Expand migration 0031 adds the btree index on (workspace_id, service_date) for ledger range reads, without changing table grants. Its separate transaction begins after 0030 commits. The runner bounds index lock acquisition to five seconds; building the index can block ledger writes for longer, so deploy large ledgers in a quiet window. Reads continue during the build.

Migration 0030 is a contract migration: it adds triggers to existing writer tables, captures a baseline under the migration transaction and requires roll-forward recovery. The backfill scans current reservation nights in one set-based aggregation and inventory. Locks block writes to properties, reservations, reservation rooms/nights, units, room types and unit blocks throughout the baseline and DDL. Five seconds bounds each table acquisition wait, not the hold time. Deploy large instances in a quiet window; see ADR 0031 for measured timings.

Contract migration 0037 adds independent per-field contact review times and remembered dismissed OTA values. Reviews have tenant/guest cascading parents and forced scoped RLS; private values never enter audit/events. Key normalizers become immutable invokers, with minimal execute ACLs retained under the default-deny bootstrap. Profile shared contacts probe only the profile’s indexed keys. Re-point policies declare revision columns per table and use the injected merge instant. Moved reservation audit/events use bounded bulk inserts with the ordinary update data shape.

Migration 0044 adds versioned workspace names and archival, an explicit instance-administrator grant, and restrictive active-workspace policies for business data. The identity tenancy store serializes provisioning/settings with team changes on the organization row, then rechecks live membership and grants. Workspace-only managers retain their one-workspace context. Runtime UPDATE grants on workspaces cover name, archived and version only; organization, identifier, environment and creation time remain immutable. Archived workspace records, roles, memberships, audit and events remain available to administrative recovery; business-table access is restored on unarchive. New workspace business tables must retain that active-workspace policy. Channex mappings must be unlinked and webhook/installation cleanup finished before archival. Saved credentials are retained; locked triggers protect concurrent connection and mapping writes.

Migration 0045 (archived_channex_routers, contract) gives the drift and alert routers of migration 0042 the same archived-workspace exclusion; ari_drift_due follows its router.

Migration 0054 (occupancy_pricing_packages, contract) lets rate plans sell per person: the occupancy curve (base, maximum, per-adult amounts or explicit adjustments) and the infant and child bands are CHECKed columns of rate_plans, so a constraint enforces the domain’s distributable shape (at most 18 levels, infant band below child band, nothing of its own on a derived plan). rate_plan_package_items lists each plan’s included extras (FK to the property’s extra), reservation_rooms.child_ages the children’s ages (empty or one per child) and reservation_night_packages the components frozen on each night, deleted with it. folio_movements accepts the package source, and booked room revenue in performance and pickup capture leaves package components out. It is a contract migration: the previous runtime would read per-person plans as per-room and rewrite nights without their packages. Changing the domain bounds needs a new migration. See ADR 0027 and ADR 0029.

Migration 0055 (rate_distribution_sold_packages, contract) stores where a plan is sold (distribution_scope direct or channels, distribution_channels from the known channel kinds, none on a direct plan) and the sold basis and source of each frozen package component, so a re-dated stay keeps what it was sold and a channel booking’s carved-out package is told apart.

Migration 0069 (online_payments, contract) adds the online payment tables, and the provider and rounding folio sources (ADR 0054). Besides the workspace policies, payment_connections has a restrictive organization_binding policy: a unit of work that names organizations reaches the connection only under the one it belongs to, so a provider delivery addressed to {organization}/{workspace} with another organization finds and writes none. A staff scope that names no organization (a member who does not administer it) relies on the workspace grant; the repository’s connection and saveConnection apply the same rule. A link has one page per number (attempt) and at most one page creating at a time. A delivery is pending while it is neither processed (with its outcome, applied or unowned) nor dead-lettered; retryEvent dead-letters it on the EVENT_ATTEMPTSth failure and reviveEvents offers dead-lettered ones again. The worker’s routers count pending deliveries only, and open links whose provider customer was asked for before the patience and is not recorded.

The same migration keeps OTA bookings’ payment facts and their cards. The Channex booking import writes booking_payment_facts (one row per reservation) in the unit of work that projects an accepted revision, as the API or the worker: who collects, the payment type, the card’s brand, last four digits and expiry, whether it is virtual, and the virtual card’s currency, balance (minor units), effective and expiry dates. Facts Channex did not give stay null; card_shown and virtual_card_shown tell an uninformative card from none. The upsert keeps the facts of the newest revision (revision_at, the revision’s inserted_at): an older revision replayed late changes nothing, the same revision again replaces them. bookingCollector answers the facts’ collector, null without facts. payment_card_tokenizations holds each property’s Channex Stripe Tokenization App state (the operator’s acknowledgement that they installed it, and when Channex last answered 403 without it), one versioned row per property: saveTokenization writes a newer version only and fails on a stale one. payment_ota_cards keeps OTA booking cards moved to the provider (the payment method and the card summary); a moved card is never changed. provider_payments records each payment’s method (card_online or ota_virtual_card, the latter only for an ota_card charge, which names its ota_card_id). No card number, security code, holder’s name or token is stored in any of them.

Channex environment of a connection

Migration 0056 (channex_connection_environment, contract) adds channex_connections.environment, production or staging: the Channex environment that verified the saved key, and the only one it is sent to (ADR 0050). Existing rows were verified by production and keep it; a sandbox workspace’s row of production is an environment_mismatch and fails closed. get reads the workspace’s environment with the connection. The repository writes the environment core chose from the workspace and reads a workspace’s environment with workspaceEnvironment. It is a contract migration because a previous runtime would send a staging key to production.

Guest data rights

Migration 0060 adds the retention settings (guest_data_retention, with the next run’s claim), the workspaces’ versioned pseudonym keys (guest_data_pseudonym_keys, no runtime grant; guestcraft.rotate_pseudonym_key(workspace) adds a version), erasure and access requests (guest_data_requests, counts and a version; an export’s archive is a file of the file store), legal holds (guest_legal_holds, one active per guest), guests.updated_at/erased_at, reservations.personal_data_removed_at, audit_log.pseudonymized_at and guest_data_workspaces(text, timestamptz) (worker role), the discovery of due workspaces (ADR 0052).

src/personal-data.ts is the personal-data catalog: every column of every workspace table, and of the organization tables it names (counterparties, exchange_rates), is either a personal column, with its purpose, erasure rule and access rule, or listed as holding no personal data. personal-data-catalog.integration.test.ts reads every column from pg_catalog and fails on an unclassified, doubly classified or stale entry, and runs every statement the catalog builds with the grants of the role that applies it. A new column or workspace table is classified in the same pull request. The catalog’s engine (eraseSubjects, exportSubjects) builds the erasure, retention and export statements; a row erased once is not changed again unless its data came back, and its marker column records when. src/repositories/privacy.ts implements the core port on it.

• Erasure (app role, one transaction) takes the workspace’s guest gate exclusively, then locks the merge chain’s guest rows in identifier order (FOR NO KEY UPDATE), then changes the other tables: profiles anonymized with their identifiers and merge pointers kept, stays’ personal fields cleared with their versions moved, archived revisions deleted (revision and receipt rows stay), webhook deliveries naming the guest or a stay deleted, consent notes cleared (decisions kept), audit and event details pseudonymized through guestcraft.pseudonymize_details (the only writer of digests; the workspace’s versioned key never leaves the database), quarantined receipts of the stays made stale, the chain’s open exports ended. Folios and their movements are kept.
• Retention (worker role, privacy-retention.ts) takes the gate shared and locks the guest and reservation rows it anonymizes SKIP LOCKED; each step changes at most one batch, ten for technical records, and skips every identity of a chain under an active legal hold. Technical periods: absorbed identities after 90 days, events after 31 days unless a webhook still has to fan them out, processed ARI changes once nothing supersedes into them, finished ARI sends and history imports after 30 days except each property’s newest 20 and 10, signals after 30 days.
• A legal-hold change takes the gate exclusively and locks the guest row, so it waits for a running retention pass and every later pass sees it.
• Export transitions (expiry from ready, the stall from queued, download, preparation, erasure) update only rows in the expected state, locked SKIP LOCKED, and return the rows they won; only a winner removes the archive.

Migration mcp_confirmations (expand) adds guestcraft_meta.mcp_confirmations, the ledger of the built-in MCP server’s confirmations (Database.mcpConfirmations): the first claim of a challenge’s nonce inserts its row, on any instance, and a later claim reads the outcome recorded by the first. It is not tenant data and has no row-level security; checks keep it to a nonce, an expiry and the outcome as identifiers and status only. Claims delete up to 100 expired rows each, since an expired challenge never verifies again. Only the app role reaches it.

Rate-plan sales caps

Migration 0071 (rate_sales_caps, contract) adds the nullable rate_days.max_sell (0–9999, null for no cap) and grants reservations.override_sales_cap to the built-in administrator role, which is why it is a contract migration. Plan sales are not stored: RateRepository.sales counts them from the reservations (ADR 0027).

Booking reports to the OTA

Migration 0072 (booking_ota_reports, contract) adds booking_ota_reports: one row per reservation and report kind (no-show, invalid card, cancellation over an invalid card) sent to the booking’s OTA through Channex, with its state (sending, sent, failed, unknown), version, attempts, the safe failure reason and, for a no-show, the fee decision and Expedia’s penalty. No card data, guest data or upstream body is stored. Claims and outcomes are compare-and-set on the version, so simultaneous requests send one report. It has forced RLS with property isolation and the workspace_active policy, composite references to the property and reservation with ON DELETE CASCADE, and grants reservations.report_to_ota to the built-in administrator and front desk roles, which is why it is a contract migration. See the booking reports guide and ADR 0032.

Live target retirement

Migration 0073 (channex_target_retirement, expand) adds ari_retirements (one active retirement per property, a partial unique index) and channex_retired_targets (the fences of retired Channex room types and rate plans), both with composite references to the property and ON DELETE CASCADE, forced RLS and the workspace_active policy, and grants the worker the mapping writes the switch makes (ADR 0034, “Live target retirement”).

Migration 0074 (person_credential_revocation, contract) adds guestcraft.revoke_person_credentials(organization, person, at), a SECURITY DEFINER function granted to the API role only. SCIM deprovisioning calls it to revoke the API keys a person created and the OAuth grants they gave, with the provider’s tokens and consents, in every workspace of the organization: archived workspaces too, whose rows no runtime policy reaches.