> ## Documentation Index > Fetch the complete documentation index at: https://help.stralos.app/llms.txt > Use this file to discover all available pages before exploring further. # Runbooks > Operational runbook index This page records starter operational procedures for the current platform foundation. ## Local service startup 1. Start Postgres. 2. Start `stralos-api-global` dependencies with DynamoDB Local and initialise OTP/session tables. 3. Run migrations for the API services you need. 4. Start API services on ports 3300 to 3304. 5. For Documents, start LocalStack and `pnpm dev:worker`. 6. Start `stralos-shell`. ## Database migration recovery * Confirm the service `.env` points at the intended database. * Run `pnpm db:generate` only when schema changes are intended. * Run `pnpm db:migrate`. * If a migration fails, inspect the generated SQL and database state before retrying. ## Documents API: org-security M4 migration (api-documents) The M4 rollout for `stralos-api-documents` uses three migration phases followed by an automatic resolver bootstrap. Run the package entrypoint: ```bash theme={null} pnpm db:migrate ``` This runs `scripts/migrate-orgsec-waves.mjs` and then `scripts/bootstrap-doc-resolver.mjs`. **Phase 1 — wave 1 (migration 0008)** Migration `0008_orgsec_m4_add_org.sql` adds nullable `organisation_id` columns, composite parent uniques, and supporting indexes. **Phase 2 — wave 3 gate (populated environments only)** If any of the nine gated tables contain null `organisation_id` values, the migration runner stops before applying wave 5. Wave 1 is committed but wave 5 is not applied. The operator must stamp the null rows directly in the documents database, then re-run `pnpm db:migrate` to resume. The gate is skipped on a fresh or empty database. **Phase 3 — wave 5 (migration 0009)** `0009_orgsec_m4_constrain_rls.sql` sets `organisation_id` to `NOT NULL` on the nine gated tenant tables, replaces single-column foreign keys with composite ones, and recreates business-unique indexes as org-composite. It enables and forces RLS on all twelve organisation-bearing tables. The migration opens with an in-database guard that raises if any required organisation value remains null. **Phase 4 — bootstrap the org resolver** The package script automatically runs `scripts/bootstrap-doc-resolver.mjs` after the migration choreography. The owner connection temporarily grants the required privileges, the platform connection creates and owns `resolve_org_for_document`, and the temporary schema privilege is then revoked. Run the bootstrap directly only to recover a missing or dropped function: ```bash theme={null} node scripts/bootstrap-doc-resolver.mjs ``` Pipeline workers drop and log document-keyed messages while the function is unavailable. **Environment variables** | Variable | Default | Purpose | | - | - | - | | `DATABASE_URL` | None — required | Documents database, owner (migration) role. | | `PLATFORM_DATABASE_URL` | Derived from a localhost `DATABASE_URL` only | Documents database, `stralos_platform_documents` role. Required explicitly outside local development. | **Troubleshooting** * If wave 5 fails because the wave 3 gate found null organisation values, stamp the affected rows directly in the documents database, then re-run `pnpm db:migrate`. The choreography script resumes from the last recorded migration. * `DATABASE_URL` must be set explicitly. A missing value exits before touching a database. * If the resolver bootstrap fails, confirm `PLATFORM_DATABASE_URL` targets the same Documents database using the platform role, then run `pnpm db:migrate` again. The migrations are resumable and the bootstrap is idempotent. * Do not run `drizzle-kit migrate` directly against a database where 0008 and 0009 are both pending. Use `pnpm db:migrate` instead. ## Documents API: project-membership migration (api-documents) Migration `0014_project_membership.sql` adds restrictive project-membership RLS policies to every table in `stralos-api-documents` that carries a `project_id` column. The migration is marked `CUTOVER_REQUIRED` because old API instances lack the actor and project scope that the new policies expect. ### Before you start * Old API instances cannot serve traffic across this migration. They lack `app.project_ids` and will fail closed under the new restrictive RLS. * Workers also require the new code path (`runScopedMessage` / `runScopedMaintenance` from `src/workers/projectScope.ts`). Old worker binaries drop messages in terminal error rather than processing them. ### Migration steps 1. Stop all API Lambda and worker Lambda invocations for the Documents service (set reserved concurrency to zero or pause the SQS event sources). 2. Apply the migration: ```bash theme={null} pnpm db:migrate ``` 3. Deploy the new application code (API and workers) that sets `app.project_ids`, `app.estimate_ids`, and `app.worker_scope` on worker connections and the authenticated actor scope on API connections. 4. Resume API and worker invocations. 5. Confirm processing events are being written by monitoring the `documents_processing_events` table or CloudWatch worker metrics. ### Documents migration changes * Adds a `project_scope_allows(project_id)` SQL function that checks whether a given project ID appears in `app.project_ids`. * Adds restrictive `project_membership` policies on every `project_id`-bearing table, enforced for `stralos_runtime_documents`. * Walks FK descendants transitively to add `project_parent_` policies on child tables that reference a project-owning parent. * Patches `project_document_event` on `documents_processing_events` to also allow writes where `document_id IS NULL AND app.worker_scope = 'true'`, so sweep and sync-dispatch workers can write events for null-project documents during cutover. * Adds `project_assessment` on `documents_variation_assessment_jobs` keyed on `app.estimate_ids`. * Adds `project_outbox_parent` on `documents_outbox` using FK-linked document or variation-job ownership. ### Recovery after a failed cutover Follow the [API writer cutover](https://help.stralos.app/internal/runbooks.md#api-writer-cutover) general recovery procedure. The migration is applied atomically; a failure leaves the schema in its pre-migration state. Do not reopen traffic until the code and schema are confirmed to be consistent. ## DWG demand and conversion spend report The `report-dwg-demand.mts` script in `stralos-api-documents` produces a combined view of DWG/DXF upload demand and APS Design Automation conversion spend. Run it from the repository root: ```bash theme={null} # Human-readable output node --import tsx scripts/report-dwg-demand.mts # Machine-readable JSON node --import tsx scripts/report-dwg-demand.mts --json ``` **Connection requirement.** The documents tables are under FORCE RLS, so tenant queries through the runtime role would see zero rows. The report detects that role and refuses to run before producing output. Set `PLATFORM_DATABASE_URL` to the platform (BYPASSRLS) connection before running. `DATABASE_URL` is accepted as a fallback only when it points at the platform role. ```bash theme={null} read -rsp 'Platform database DSN: ' PLATFORM_DATABASE_URL printf '\n' export PLATFORM_DATABASE_URL node --import tsx scripts/report-dwg-demand.mts unset PLATFORM_DATABASE_URL ``` Paste the approved platform DSN at the silent prompt. The value does not appear in the command text or terminal output and is removed from the shell after the report. Prefer the approved secret-store lookup where one is available; never commit or print the DSN. **Sensitive output.** The platform connection reads across organisations. Human-readable output includes organisation and project identifiers and raw filenames; JSON output also includes individual attempt and conversion records. Treat either form as tenant-sensitive operational data: keep it in approved restricted storage, limit access to authorised operators, and redact identifiers and filenames before sharing extracts. ### Demand section The demand section counts DWG and DXF files that reached the server and were refused: * **Upload** — presign attempts where `validateFile` rejected the file. Recorded as a `status='rejected'` document row. * **Sync** — drive-sync crawl entries skipped as an unsupported type. The filter uses the file name extension, not the skip reason, to isolate CAD files from other unsupported formats. These counts are bounded: the browser still drops zero-byte, unreadable, over-long-path, and duplicate picks before they reach the server. The denominator is "DWG/DXF attempts that were otherwise valid", which is the right figure for measuring feature demand and the wrong one for estimating how many DWG files exist across all organisations. ### Spend section The spend section totals APS Design Automation processing time for conversions that were actually run. The output reports processing time in both milliseconds and minutes for comparison with Autodesk usage reporting. Allowances, metered charges, and the response to reaching a limit depend on the account's current Autodesk tier; confirm them in the Autodesk developer hub and current rate table before making an operational decision. **The total is a floor, not an exact count.** Two cases produce spend that cannot be measured from the stored record: * A retry replaces the previous attempt's metadata, so failed attempts before a final success or terminal failure carry no duration. Their count is reported as a range (`unmeasuredAttempts min–max`). * A settle that never polled a workitem — submit failure, orphaned claim, wall-clock deadline, DLQ exhaustion — cannot confirm whether a workitem was created and billed. A reconciliation gap against Autodesk's own usage figure is expected up to the upper bound of `unmeasuredAttempts`. A gap larger than that bound is unexplained. **Spend by month** covers measured durations only. An erased prior attempt carries no timestamp, so it is counted once in the totals and never attributed to a calendar month it may not belong to. ## Documents API: page render and scale backfill (api-documents) The Documents pipeline renders every page image and thumbnail ahead of time. New uploads are covered by their upload paths and the sweep's safety nets. Documents uploaded before ahead-of-time rendering have no pre-rendered pages, so `scripts/backfill-page-renders.mts` in `stralos-api-documents` queues the missing renders once per environment. With `--scale`, it also queues scale detections for historic drawings. ### Prerequisites * AWS credentials for the target environment. The script uses the `stralos-` profile unless `AWS_PROFILE` is set. Unset `AWS_ENDPOINT_URL`; the script refuses to run while it is set. * `PLATFORM_DATABASE_URL` set to the documents platform role's connection string, reached through the bastion. The script refuses a role that cannot see every organisation. The script header shows the port-forwarding command. ### Dry run, then execute Run from the repository root. The default is a dry run, which reads only: it reports how many documents need work and an estimated Lambda cost, and it claims and queues nothing. ```bash theme={null} node --import tsx scripts/backfill-page-renders.mts --env dev node --import tsx scripts/backfill-page-renders.mts --env dev --execute ``` The run is idempotent and resumable. S3 object presence is the "done" marker, so a re-run plans from what exists now and queues only pages that are still missing and not in flight. Each page is claimed before it is queued, so a page whose job is already on its way is skipped. A document with no page count yet gets one whole-document fan-out request; the parse worker counts its pages and queues the missing ones. | Option | Description | | - | - | | `--env dev\|test\|prod` | Required. The target environment. | | `--execute` | Queue the work. Without it, the run is a dry run. | | `--org ` | Only this organisation. | | `--limit ` | Act on at most `n` documents that need work. Re-run to continue. | | `--retry-failed` | Also queue pages whose render failed. Without it, failed pages are left for the sweep's bounded retry. | | `--scale` | Also queue scale detections for historic drawings. See below. | ### Safety guards The script reads the bucket and queue names from the deployed render worker's own Lambda configuration. It refuses to run when the database's documents live in a different bucket, so a dev database pointed at prod queues (or the reverse) stops before anything is sent. All work goes to the render worker's bulk queue, whose maximum concurrency of 10 is the throttle. The bulk queue never sits in front of a page someone has open, which uses the interactive queue. ### Scale detection backfill (`--scale`) With `--scale`, the script also queues a scale detection for every page of a historic drawing that has no scale and no cached detection result. Pages that already have a scale, a cached result, a detection in flight, or a failed detection are left alone. A document counts as a drawing when any of these holds, and it was never rejected for takeoff: * It carries a render-time drawing marker (the takeoff import marker, a converted CAD drawing, or the classifier's drawing marker). * It has been approved for takeoff. * Its takeoff run completed, and it is not a Drive sync new version. A historic drawing without a render-time marker is marked as a drawing before its detections are queued, so the detection job applies the same auto-set rule as a new upload: a page that states one clear scale has it set, and a manual or automatic scale is never overwritten. An ambiguous or absent result is only cached for the takeoff screen. Each detection slot is claimed before queuing, so a re-run never doubles one. A detection reads the PDF's text first, which costs no AI calls. Only a page whose text states no scale costs vision calls: one, or two when close-up crops are needed. ## API writer cutover Activities, Assistant, Documents, Estimate, Global, Rates, and Scheduler deploy migrations online by default. Their pinned shared workflow reads `drizzle/CUTOVER_REQUIRED` and versioned `drizzle/_CUTOVER_REQUIRED` markers, then checks the migration ledger for pending named migrations. The ordinary pull-request guard still requires the unversioned form in every service except Rates. ### Arm the outage for one release 1. Add the numbered migration file under `drizzle/`. 2. In the same pull request, create `drizzle/CUTOVER_REQUIRED` with no extension. Each non-blank, non-comment line names the migration file as it appears in `drizzle/`: ```text theme={null} # This migration rewrites records in place. 0036_rate_item_drop_code_and_unit.sql ``` A filename must match `^[0-9]{4}_[A-Za-z0-9_]+\.sql$` and the named file must exist. A malformed or empty marker, an unknown filename, an unreadable migration tree, or a symlink fails validation. Deployment does not infer that writers can remain online from missing or invalid marker evidence; correct the marker before deploying. Rates also accepts a versioned marker such as `drizzle/0036_CUTOVER_REQUIRED`, with the same four-digit prefix as its migration. The other six pull-request guards require the unversioned form. Both forms are visible to the pinned deployment workflow. 3. Run the pull-request guard locally: ```bash theme={null} node scripts/check-destructive-migrations.mjs --base "$(git merge-base origin/main HEAD)" ``` The guard prints `ARMED` when a marker names every changed migration containing a guarded destructive statement. A clean additive change prints `CLEAN`; any other result is a refusal with the detected statements and marker problems. 4. Confirm the pull-request guard and the ordinary migration validation pass before merging. The shared deployment workflow reads the marker from the exact deployed source; `MIGRATION_MANIFEST.json` remains `additive-online` and does not arm the outage. 5. Confirm that completion is recorded for each named migration in every target environment. Markers can remain in source because a later release subtracts migrations recorded as completed by another release in that environment. A retry of the same release retains its outage, and unreadable ledger evidence retains arming. Any commit that changes or removes a marker also refuses automatic dev deployment; use a reviewed manual dispatch for that range. ### Recover after a failed cutover Once the cutover step has stopped writers, a later migration or Terraform failure deliberately leaves them stopped. Do not reopen traffic until the deployed code and migration state are understood. 1. Inspect the failed workflow's **Cutover writer recovery** step summary. It records the function name, the observed prior concurrency, and the exact restoration command. 2. Inspect the migration ledger and database state to determine whether each migration completed. Do not rerun a partially applied destructive migration without reviewing its effects and recovery path. 3. Recover or complete the deployment while writers remain stopped. 4. Only after the code and schema are safe together, run the recorded restoration command. It restores the prior reserved concurrency or removes the reservation when the function was previously unreserved. 5. Confirm the request Lambda is serving normally before ending the incident. A successful restoration does not by itself prove the migration or deployment completed. ## Rate import: stuck job investigation A rate import job shows `running` indefinitely when all its blocks have finished processing but the job status has not settled, or when a block was never processed. **Check job and block state first.** 1. Retrieve the job via `GET /api/rates/rate-imports/:jobId`. The `status` field and the `blocks` array show the current state of each block (`pending`, `running`, `done`, `empty`, or `failed`). 2. A job stuck in `running` with all blocks in terminal states (`done`, `empty`, or `failed`) indicates the job aggregation step has not run. This is self-resolving; wait for the next SQS redelivery or contact an engineer if it does not settle within the visibility timeout window. 3. A block stuck in `pending` with no recent activity means its SQS message was never delivered or was delivered and exhausted its retry budget. **Dead-lettered blocks.** When a block message exhausts its retry budget, the worker consumes it from the dead-letter queue and marks the block `failed` with error code `dead_letter`. This prevents the job from polling `running` indefinitely. The worker logs a `SUPPORT_ALERT` entry in CloudWatch before acknowledging the message. To find the forensic record in CloudWatch: * Log group: `/aws/lambda/stralos--rate-import-worker` * Filter: `{ $.marker = "SUPPORT_ALERT" && $.kind = "rate_import_dead_letter" }` The log entry includes the `messageId`, `jobId`, `blockId`, and `organisationId` from the original message. **A block already in `done`, `empty`, or `failed` is never overwritten by the dead-letter drain.** If a block shows a successful state but the message also appeared in the dead-letter queue, the extraction completed and the acknowledgement was lost. The result is intact. **Retrying a failed job.** Use `POST /api/rates/rate-imports/:jobId/retry` to re-dispatch blocks for a failed job. The retry route resets the job to `running` before re-dispatching, so the dead-letter drain does not interfere with a deliberate retry. ## Documents API: Lambda connection pool limits (api-documents) Each Lambda container that serves the Documents API creates up to three Postgres connection pools (owner, runtime, and platform roles). Each pool is capped at `max: 2` with a `connectionTimeoutMillis` of `5000` ms. The cap prevents a burst of warm containers from exhausting a database's connection slots. Without it, a fleet of containers each holding up to ten connections can claim far more connections than the database allows, causing every API request to fail with a 500 for the duration. When a pool cannot acquire a connection within 5 seconds, the request fails immediately. The safe error handler maps this to a `503` with `code: "service_unavailable"`, a `reference` matching the request's `X-Request-ID` header, and a `Retry-After: 5` header. Clients should retry after that interval. The local development server and one-off scripts are not subject to this cap. They use pg's default pool size of 10, which lets a single local process handle concurrent requests without serialising them. **Diagnosing a `503` from the Documents API** 1. A `503` with `code: "service_unavailable"` from `api-documents` means the pool timed out waiting for a Postgres slot. 2. Check the current database connection count against `max_connections` and `pg_stat_activity` to confirm saturation. 3. If Lambda concurrency is unexpectedly high, check for a processing backlog or a runaway retry loop that is holding connections open. 4. The `reference` value in the response body maps to a log entry in the Lambda's CloudWatch log group under `x-request-id`. ## Document pipeline queue inspection * Check document status first through `GET /api/documents?estimateId=...`. * Check area runs and pending suggestion counts. * Inspect LocalStack or AWS queues for stage backlog. * Inspect DLQs before manually retrying documents. ## Failed document retry * Retry only documents in `failed`. * If the upload never completed, upload the file again. * If scan failed or the file is infected, do not bypass the download gate. * After retry, poll status and review area run errors. ## Estimate totals recompute Use `POST /api/estimate/estimates/:id/recompute-totals` to recalculate an affected estimate from its materialised pricing inputs. The endpoint holds the estimate-totals advisory lock and returns the updated work item list. For legacy material inputs, `scripts/materialise-pricing-inputs.mjs` snapshots rate-option identity, effective unit cost, and source and location labels into keyed component overrides. `DATABASE_URL` is required; `STRALOS_RATES_URL` defaults to `http://localhost:3301`. Run this only after reviewing the affected rows against the current schema. It makes HTTP calls and requires a running Rates service, so do not run it inside a database migration. An ordinary rerun skips complete overrides. Clearing or rematerialising snapshots, or filling previously incomplete rows, can change prices. Review the changes and recompute the affected estimate totals afterwards. The legacy `estimate_work_items.pricing_source` column is retired. Do not run a pricing-row backfill that reads that column against the current schema. ### Service token for cross-service reads The material-input script mints a `service:pricing-materialise` bearer token before reading Rates and refreshes it within 30 seconds of expiry (TTL 600 s). If minting fails, the helper attempts the request without a header, which Rates rejects. Successful minting is required for a useful run. Service-token configuration: | Variable | Default | Purpose | | - | - | - | | `STRALOS_GLOBAL_URL` | `http://localhost:3300` | Global API base URL and default organisation lookup base. | | `SERVICE_MINT_SECRET` | `local-dev-mint-pricing-materialise` | Shared secret accepted by the `api-global` local mint endpoint | | `STRALOS_ORG_ID` | *(auto-resolved)* | Tenant organisation UUID. When omitted the script resolves it from the sole org returned by `GET /api/global/organisations` | | `SERVICE_MINT_URL` | `${STRALOS_GLOBAL_URL}/api/global/internal/service-tokens` | Override the mint endpoint URL | The helper currently uses the local HTTP mint transport. The deployed mint uses direct Lambda invocation instead and is not exposed through API Gateway. Do not rely on tokenless degradation: the current Global and Rates services reject unauthenticated protected requests. Do not commit mint secrets to source control. ## Drive sync: sync folder transfer backfill Run this **once** after deploying the sync folder transfer feature (`api-documents` migration `0011_sync_folder_transfer.sql`). The backfill seeds a new reconciliation crawl for every resumable connection in `active`, `paused`, or `error` state. ```bash theme={null} DATABASE_URL=… pnpm db:backfill-sync-folders # --dry-run to preview ``` The script fence-breaks each connection's `current_run_id` before seeding, superseding any in-progress crawl while preserving the change cursor. It never writes a folder directly. The new crawl re-evaluates documents that sync owns or that have never been filed; user-owned folders remain unchanged, and an unresolvable source path preserves the existing filing. The script is safe to re-run. Every still-resumable connection receives a newer crawl generation; it does not pre-scan documents to skip connections. ## Drive sync: source stuck in auth\_error or error See [Drive sync error classification](https://help.stralos.app/internal/drive-sync.md#error-classification) for what moves a source into each state. 1. For `auth_error`: confirm the provider grant is still in place (Google Drive member, SharePoint site permission) and re-add it if removed. If every source on the organisation's drive identity moved together (`identity_auth_error`), check the identity's status and reconnect it if needed. 2. For `error`: the environment's federation failed (`platform_federation_failure`, which also emits the `drive_platform_federation_failure` metric). It affects every active source for that provider. Ops must fix the drive platform configuration before recovery is possible. 3. Once the underlying issue is resolved, call `POST /api/documents/drive-sources/:id/verify`. No new proof file is required; re-verify only re-runs the access check. 4. Monitor the source status and the next run's outcome. ## Drive sync: re-include a file The sync engine links files rather than copying them, and no longer quarantines files after repeated ingest failures: it re-evaluates any older `ingesting`, `failed`, `skipped` or `error` mapping on its next pass. A file a user deleted from Stralos is `excluded` and stays excluded until an admin re-includes it. 1. Call `GET /api/documents/sync-connections/:id` and locate the file in the connection detail. 2. As an admin, call `POST /api/documents/sync-connections/:id/files/:fileId/reinclude`. Only an `excluded`, `error` or `failed` file can be re-included (`409` otherwise). The route resets the file to `error`, clears its ingest budget, and seeds a reconciliation crawl. 3. The reconciliation re-lists the folder tree and links the file on the next run. A re-included excluded file arrives as a new document. ## Drive sync: connection paused unexpectedly A connection is fence-broken (run cleared) when its source is disabled, the connection is patched to `paused`, or the connection is deleted. It is also broken on a reconciliation seed. 1. Check the source status — `auth_error`, `error`, or `disabled` suspends all its mappings. Restore access and re-verify or enable the source as appropriate. 2. Check the connection status via `GET /api/documents/sync-connections/:id`. 3. If the connection itself is `paused`, resume it with `PATCH /api/documents/sync-connections/:id` and `{ "status": "active" }`. 4. Once both the source and connection are active, use **Sync now** from the project **Documents** page if you want to request the next run immediately. ## Drive sync: toggle the kill switch The drive-sync kill switch stops sync dispatch, the sync worker, and drive source and connection mutations without a deploy. See [Drive sync](https://help.stralos.app/internal/drive-sync.md) for how the gate works. The lever is the `api-documents` SSM parameter `/stralos//api-documents/drive-sync-enabled`. Terraform seeds it once per environment and then ignores its value, so change it with the AWS CLI, not a Terraform apply. Every Lambda reads it through the shared five-minute SSM cache, so a change reaches warm instances within five minutes. A failed read counts as closed. ### Close the switch ```bash theme={null} aws ssm put-parameter \ --name "/stralos//api-documents/drive-sync-enabled" \ --value "false" \ --type String \ --overwrite ``` While closed, the dispatch tick claims no connections and enqueues nothing, a queued worker message is dropped before any provider call, and drive-sync mutations (sources, connections, drive identities, and drive-link actions such as prefetch) return `409 drive_sync_disabled`. Reads of already-synced documents are unaffected. Closed-gate invocations log a `{"event":"drive_sync_disabled","stage":...}` line. ### Reopen the switch Set the value back to `true`. Only the exact string `true` opens the gate; `True`, ` true`, or `1` keep it closed. The next dispatch tick after the cache refreshes resumes normal scheduling. No restart or deploy is needed. ### Confirm the switch state ```bash theme={null} aws ssm get-parameter \ --name "/stralos//api-documents/drive-sync-enabled" \ --query "Parameter.Value" ``` ### Sync-dispatch schedule rule The `drive_sync_schedule_enabled` Terraform variable (default `true`) controls whether the five-minute sync-dispatch EventBridge rule is enabled. Setting it to `false` in the environment's tfvars and applying stops even the dispatch cold starts. The SSM parameter still gates every surface on its own. ## Org-security M3 backfill and migration Estimate API After deploying `stralos-api-estimate` with the M3 org-security wave, run its migration choreography. **Pre-requisites** * `DATABASE_URL` is set and points at the estimate database using the owner (migration) role. * Outside local development, `PLATFORM_DATABASE_URL` points at the same Estimate database using `stralos_platform_estimate`. **Step 1 — run the choreography script** ```bash theme={null} pnpm db:migrate ``` This package script runs `scripts/migrate-orgsec-waves.mjs` and then `scripts/drop-portal-resolver.mjs`. On a populated database it: 1. Applies migration `0015_orgsec_m3_add_org.sql` (adds nullable `organisation_id` columns). 2. Refuses to continue if any row still needs `organisation_id` stamping. The cross-database `scripts/backfill-organisation.mjs` that once stamped them is deleted: cross-database migrations are not allowed, and every deployed environment passed `0016` long ago. 3. Applies migration `0016_orgsec_m3_constrain_rls.sql` (sets `NOT NULL`, FORCE RLS, policies, and grants). 4. Drops the retired `resolve_org_for_portal_token` SECURITY DEFINER function, if present, through the platform connection. On a fresh or empty database there is nothing to stamp and the run passes straight through. **Environment variables** | Variable | Default | Purpose | | - | - | - | | `DATABASE_URL` | None — required | Estimate database, owner (migration) role. | | `PLATFORM_DATABASE_URL` | Derived from a localhost `DATABASE_URL` only | Estimate database, `stralos_platform_estimate` role. Required explicitly outside local development. | **Step 2 — verify** Confirm the migration run completes without refusing at wave 3. ## Estimate lifecycle M3 backfill The one-off `scripts/backfill-estimate-lifecycle.mjs` that normalised estimate statuses after migration `0014_lifecycle_m3_estimate.sql` has been deleted from `stralos-api-estimate`. It read the Global database directly, cross-database access is no longer allowed, and every deployed environment has already run it. Use the reconciliation report below to check for status drift. ## Lifecycle reconciliation report The reconciliation script is a read-only two-database safety net. Run it whenever you suspect cross-service drift. ```bash theme={null} node scripts/lifecycle-reconcile.mjs ``` Output a JSON report instead: ```bash theme={null} node scripts/lifecycle-reconcile.mjs --json ``` The script checks three things: 1. **Status disagreements** — estimate statuses that are not in the allowed set for their project status. For example, an estimate in `draft` when the project is `awarded`. 2. **Stale pending rows** — `estimate_status_history` rows (and the global `project_status_history` rows) in `state='pending'` for more than 10 minutes. These are transitions the sweeper has not yet resolved. 3. **Version mismatches** — estimates whose `version` field does not match their highest committed revision number (`"0"` when none exists). The script exits with code `1` if any section is non-empty, which ops alerting can hang off. **Environment variables** | Variable | Default | Purpose | | - | - | - | | `DATABASE_URL` | `postgres://postgres:postgres@localhost:55432/stralos_estimate` | Estimate database (read). | | `GLOBAL_DATABASE_URL` | `postgres://postgres:postgres@localhost:55432/stralos_global` | Global database (read). | ## Admin API tenant bootstrap (`db:init`) `pnpm db:init` runs on the migration rail on every deploy. It is idempotent and exits 0 in all non-error states. See [Deploy-time tenant bootstrap](https://help.stralos.app/internal/admin-console.md#deploy-time-tenant-bootstrap-dbinit) for the full configuration reference. ### Verifying a successful bootstrap The command writes a JSON line to stdout. A `created` run includes `organisationId` and `userId`; an `already_initialised` run includes `userId` and `email`; a `skipped` run includes a `reason`. ```bash theme={null} # Run locally against a fresh database ADMIN_INIT_ORGANISATION_NAME="Example Contracting" \ ADMIN_INIT_ADMIN_EMAIL="founder@example.com" \ ADMIN_INIT_ADMIN_NAME="Founder" \ pnpm db:init ``` ### Diagnosing a partially-configured error If the command exits non-zero with a message like `db:init is partially configured; missing ...`, one or two of the three required variables are set but not all three. Set all three or unset them all. A partially-set configuration is always a hard error. ### Admin email already taken by another organisation The bootstrap is keyed on `ADMIN_INIT_ADMIN_EMAIL`. If that address already belongs to a user in a different organisation (for example after a seed mistake on a shared environment), the run returns `already_initialised` pointing at the existing user rather than creating a new tenant. Confirm the existing user's `organisationId` in the output before deciding whether the environment needs to be reset. ### Cognito account never created (local development) Locally there is no Cognito sweeper, so the admin user's `provisioning` column stays `pending` and no invite email is sent. This is expected: the regional runtime authenticates from Postgres using OTP sign-in, so the administrator can log in without a Cognito account. In a deployed environment, confirm the Cognito sweeper is running (`enable_event_sync = true` and `cognito_user_pool_id` set in Terraform) and check the `cognito_operations` table for a `pending` `create` row. ## Admin console operator access The admin console has no public signup or password-reset flow. A private, IAM-invoked Lambda provides the first-operator bootstrap and break-glass recovery operations. Use `stralos-admin-api/scripts/admin-operator.sh` from an authorised administrator workstation. ### Bootstrap the first operator Prerequisites: * the admin API stack is deployed to the target environment; * your AWS session can invoke that environment's operator-operations Lambda; and * the AWS CLI and `jq` are installed locally. ```bash theme={null} AWS_PROFILE=stralos-dev scripts/admin-operator.sh bootstrap \ --email operator@stralos.co.nz \ --name "Platform Operator" ``` Run this interactively from a trusted workstation. The script prompts twice for a password without echoing it. The password must be 12–1024 characters. Never put the password on the command line. A successful bootstrap prints the operator record and a one-time `otpauth://` enrolment URI. Treat the URI as a credential: enrol it immediately and never put it in CI logs, shell transcripts, tickets, or chat. The supported flow emits the URI once; recovery rotates the underlying secret. Bootstrap is self-disabling once any operator exists and the operation writes an `operator.bootstrap` audit record. ### Recover a locked-out operator Break-glass recovery resets the named operator's password and MFA secret, reactivates the account, and revokes all existing sessions: ```bash theme={null} AWS_PROFILE=stralos-dev scripts/admin-operator.sh recover \ --email operator@stralos.co.nz ``` The command returns a new one-time `otpauth://` URI and writes an `operator.break_glass_recover` audit record. Apply the same credential-handling rules. Use this only when normal operator access is unavailable. `ADMIN_ENV` selects the environment used in the default Lambda name. `ADMIN_OPERATOR_FUNCTION` can provide an exact function name when required. ## Admin database URL rotation sync The admin API's database connection URL is stored as an SSM SecureString at `DATABASE_URL_SSM_PARAMETER`. RDS manages the master password on a 7-day rotation schedule. A secret-sync Lambda re-derives and writes the assembled URL whenever the password rotates. ### How the sync runs Three EventBridge triggers invoke the Lambda: * **RDS-EVENT-0016** — RDS fires this event directly when it resets the master credentials. It is the primary trigger. * **Secrets Manager label updated** — fires when the `AWSCURRENT` label moves to the new secret version. This catches manual `update-secret` calls that the RDS event would not see. It is a secondary belt; its behaviour for service-managed (`rds!`) secrets has not been observed directly. * **Hourly safety-net sweep** — runs on the schedule in `var.database_url_sync_schedule`. This is the only trigger without a best-effort caveat and sets the worst-case recovery window if both event paths fail. The Lambda reads `AWSCURRENT` from the RDS-managed secret, assembles the connection URL, compares it byte-for-byte to the current SSM value, and writes it only when the values differ. A no-op result does not update the parameter's version history. The Lambda also invokes once at `terraform apply` time to repair an already-stale parameter immediately. ### Diagnosing a stale URL If admin API Lambdas are failing 28P01 (authentication failures), the parameter may not have been refreshed after a rotation. Check the most recent sync outcome in CloudWatch: ```text theme={null} /aws/lambda/stralos--admin-secret-sync ``` Look for log lines with `"message": "admin database-url sync"`. The `action` field is either `in-sync` or `updated`. The `secretVersionId` field identifies which secret version was current at sync time. If the Lambda has not run since the last rotation, the hourly sweep will reconcile automatically. To force an immediate repair, invoke the Lambda directly: ```bash theme={null} AWS_PROFILE=stralos- aws lambda invoke \ --function-name stralos--admin-secret-sync \ --cli-binary-format raw-in-base64-out \ --payload '{"trigger":"manual"}' \ /dev/stdout ``` The payload is ignored; any shape is accepted. The function logs the outcome and returns a JSON object with `parameter`, `action`, and optionally `reason` and `secretVersionId`. The password and assembled URL are never logged. ### Rotation during a sync attempt The Lambda detects when the secret rotates while it is running and retries up to three times. If the secret rotates on every attempt, the Lambda throws and the next trigger (at worst, the hourly sweep) reconciles. This is logged as an error in CloudWatch and does not require manual intervention unless the error recurs. ## Taxonomy M13 migration (admin-api) Migration `0021_taxonomy_m13_purge_precutover_snapshots.sql` purges pre-cutover seed snapshots for organisations that have not yet been provisioned. It runs automatically as part of the normal deploy migration Lambda. This section covers the quiesce precondition check and triage steps for a failed deploy. ### What the migration does The migration deletes `org_seed_snapshots` rows for organisations whose status is `provisioning` or `failed` and whose snapshot contains at least one rate-category row without a `parentGroup` field. These are snapshots frozen before taxonomy M4, which would seed a parentless flat category tree if their provisioning event were ever delivered. The migration does not touch: * snapshots for `active` or `disabled` organisations — their provisioning events were already delivered and the snapshot is spent history; * empty snapshots (`rate_categories = '[]'`) — an empty snapshot seeds nothing and is shape-indistinguishable from a post-M4 snapshot; and * any snapshot whose categories all carry `parentGroup`. Results are written to `audit_log` with action `seed_snapshot.precutover_purge` and aggregate `taxonomy-m13-d13`. The migrate Lambda swallows `RAISE NOTICE`, so this is the only record of what the migration did. ### Quiesce precondition Before deleting anything, the migration checks that every `seed_template` row in `admin_outbox` has reached a terminal status. Both `pending` and `conflicted` rows fail the check. The migration raises an exception and the deploy aborts if this precondition is not met. The check covers `conflicted` rows because a dead-lettered outbox row can still be re-armed by an operator. Purging its snapshot would not neutralise the queued event, leaving the delivery vehicle intact but pointing at a deleted snapshot. ### Triage: migration aborted with pending outbox rows 1. Query the outbox for non-terminal rows: ```sql theme={null} SELECT id, aggregate_id, status, version, created_at FROM admin_outbox WHERE aggregate_type = 'seed_template' AND status <> 'processed' ORDER BY created_at; ``` 2. For `pending` rows: confirm the event flusher is running and drain the outbox. Re-run the deploy after the rows reach `processed`. 3. For `conflicted` rows: the dead-lettered event requires operator triage. Either resolve and re-arm the row through reconciliation (the normal recovery path) and wait for it to process, or — if the organisation is permanently abandoned — disable the organisation first. Disabling moves its status to `disabled`, which is excluded from the purge predicate, so the row can be left `conflicted` without blocking the migration. 4. Redeploy after all `seed_template` outbox rows are `processed`. ### Verify after deployment Query the audit log to confirm the migration ran and review its counts: ```sql theme={null} SELECT before, after, created_at FROM audit_log WHERE action = 'seed_snapshot.precutover_purge' ORDER BY created_at DESC LIMIT 1; ``` The `before` object carries `totalSnapshots` and `pendingSeedOutbox`. The `after` object carries `purgedPendingPrecutover` (rows deleted) and `precutoverOnActiveOrDisabledOrgs` (rows counted but intentionally kept). ## Cartage retirement audit capture (Estimate API) The Estimate API no longer runs `db:capture-cartage-ledger` from `MIGRATION_MANIFEST.json`. The one-off capture has been retired from ordinary deployments because the cartage-removal migration has already run. The script remains available for a deliberate manual investigation of a database that may still contain the legacy schema: ```bash theme={null} pnpm db:capture-cartage-ledger ``` The command writes the legacy cartage state to `estimate_cartage_retirement_audit`. On a fresh database where none of the capture tables exist, it records an explicit empty payload and exits successfully. The four required tables are `estimate_estimates`, `estimate_work_items`, `estimate_work_item_components`, and `estimate_work_item_pricing`. If some but not all exist, the schema is partially initialised and the capture fails before any migration runs: ```text theme={null} [cartage-retirement-audit-capture] estimate schema is partially initialized; missing required tables: ... ``` Investigate and repair the database state before retrying the manual capture. The legacy `estimate_work_item_cartage_demands` table is optional because it is intentionally absent after the retirement migration. The command requires `DATABASE_URL` for the Estimate database's migration-owner connection. It has no localhost fallback. ## Data retention operations Every service's retention Lambda runs weekly on Sunday 15:00 UTC. See [Data retention](https://help.stralos.app/internal/data-retention.md) for what each purge and erasure deletes, and [Retention Lambdas by service](https://help.stralos.app/internal/data-retention.md#retention-lambdas-by-service) for the function names. Use credentials for the target environment's account and region `ap-southeast-2`. ### Check an organisation's purge clock Each Postgres service keeps its clock in its own table: `org_offboarding` in api-global and admin-api, and `_org_offboarding` elsewhere (for example `documents_org_offboarding`). Query it through the platform database connection (for `admin-api`, its normal database connection): ```sql theme={null} SELECT organisation_id, first_disabled_at, purged_at FROM documents_org_offboarding WHERE organisation_id = ''::uuid; ``` api-assistant names the first-sighting column `disabled_observed_at`. A non-null `purged_at` means the purge completed. No row means the sweep has not seen the organisation as `disabled`, or has since seen it reactivated. ### Read the last sweep result Each run logs its sweep result as a JSON line in the function's log group, `/aws/lambda/`. The result lists the organisations checked, clocks started and cleared, organisations awaiting the window, purges with row counts, and refusals or failures. ### Diagnose a failed purge 1. The `retention` Errors alarm fires when any organisation failed. Find the organisation ID and error in the Lambda log. 2. Common causes: the organisation was reactivated between the sweep's check and the purge, the mint refused the token, api-global was unreachable, or (api-documents) S3 or Google returned a permission error. 3. Fix the underlying cause. The next weekly run retries automatically; rows are kept until the purge succeeds. ### Run a sweep outside the schedule A manual sweep processes every organisation, not just one. Confirm the window and the clock state before running it in production. ```bash theme={null} aws lambda invoke \ --function-name \ --region ap-southeast-2 \ --cli-binary-format raw-in-base64-out \ --payload '{}' \ /dev/stdout ``` An event without a `kind` runs the sweep in every service except api-scheduler, which needs `--payload '{"kind":"org-purge"}'`. api-login has no sweep. Do not invoke admin-api's retention Lambda while a deployment with a cutover marker is in flight: it is outside the cutover writer gate. ### Erase a user Run `scripts/erase-user.mjs` in `stralos-core` (see [Per-user erasure](https://help.stralos.app/internal/data-retention.md#per-user-erasure)). Start with `--dry-run` to confirm the resolved user and the function plan. If the run fails partway, fix the cause and rerun the same command: every service's erasure is idempotent, and api-global writes the audit row only after every other service has succeeded. To inspect one service's erasure during an investigation, invoke its function with `--payload '{"kind":"erase-user","userId":"","requestId":""}'`. The response is `{ service, userId, requestId, counts }`; all-zero counts mean nothing was left to erase. Do not use a direct invoke of api-global in place of the script, because the audit row would not include the other services' reports. ### Change a retention window Windows are Terraform variables: `org_offboarding_purge_after_days` (default `90`) in the regional services, and `directory_purge_after_days` (default `104`) in admin-api and api-global. A change needs a Terraform apply. Keep the directory window at least two weekly runs longer than every other service's window, so the directory rows go last. Lowering a window takes effect on the next run for organisations already past the new threshold. ## Terraform state checks * Confirm the backend file matches the target environment. * Confirm AWS account validation before apply. * Confirm service stacks read `environment-setup/terraform.tfstate`. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.