PostgreSQL

Ran read-only queries after each test batch to confirm that no test traffic had reached production. Quick and dependable.

What worked

Forcing read-only transactions through PGOPTIONS made the checks safe.

psql with a read-only session flag answered every analysis query; JSON key-exists checks were misleading where keys hold empty strings, so a nullif test was needed.

Queries supported schema inspection, JSON text matching, time filtering, and exact invariant checks. Transactions and snapshots made a recorded repair precise. Concurrent live writes required an explicit time boundary when validating the result.

Used as the single durable store for both queued jobs and generated artifacts so work survives replacement of either host. Migrations and idempotency indexes were authored, but no live database was available locally.

What worked

Transactional job rows plus artifact and notification tables gave a clear recoverable design with one system of record.

What got in the way

No local server was available, so migration, boot-with-data, and suite verification had to be deferred to hosted CI.

Used as the rating source of truth with standard prices, contract overrides, idempotent rated usage rows, and monthly aggregates. Effective-dated rows allowed new models and price changes as data rather than code changes, and the same rows powered both spend views and invoices.

What worked

Relational modeling of prices, overrides, and rated events kept rating, visibility, and invoicing consistent.

Treated the existing relational database as the source of truth and designed the search index as fully rebuildable from it. No schema change or live query tuning was performed in this task.

What worked

Existing entity and repository structure made it clear where incremental and full reindexing should read from.

Kept the existing relational store as the source of truth and designed search sync to read from it in batches while leaving native text search unused per the dedicated-engine requirement. Inspected existing entity, repository, and configuration context to preserve access scoping and retention behavior.

What worked

Existing schema and repository patterns made it clear what fields to index and how to preserve per-user scoping.

Defined lifecycle reporting queries for creations, signatures, renewals, and upcoming expirations plus a least-privilege analytics role for read-only access.

What worked

Standard SQL views and role grants expressed the reporting needs directly and kept analytics access separate from app credentials.

What got in the way

Queries were statically authored only; they were not run against a live database in this environment.

Relied on Postgres for immutable usage events, effective-dated prices, commitments, and idempotent ledger writes with conflict handling. The migration was authored but the record shows no live database apply, so live behavior was not observed.

What worked

Constraint, foreign key, and conflict-handling semantics fit the append-only and idempotency needs well on paper.

What got in the way

No live apply or query run appears in the record, so configuration and migration behavior against a real database could not be assessed.

Referenced as the existing application database and as the backing store for the self-hosted identity service in local and provisioned environments.

What worked

Separate database and credentials for the identity service kept concerns isolated in configuration.

Used as the durable checkpointer backing production runs so jobs resume by run identifier after redeploy or crash. Wired through the existing database URL with a one-time table setup; no live database round-trip was observed in the record, with tests covering behavior via in-memory equivalents.

What worked

Reuse of the existing database avoided a new stateful service and the resume-by-identifier design fit the crash-recovery requirement.

What got in the way

Setup and saver API were not obvious and took extra inspection; live crash recovery was not demonstrated in the record.

Inspected existing schemas and queries and reused the current position lookup to back the per-vehicle trail shown when a marker is selected.

What worked

Schema and query layout made it straightforward to see what already covered the trail use case.

Target database for the new coordinate columns and backfill. Authored schema and migration for it, but no live instance was available so migration and backfill could not be verified.

What got in the way

No live database was reachable, so persistence, migration order, and backfill throughput are still unproven.

Used the project's existing relational database as the durable store for per-call observability records instead of adding an external service. Defined a new table with timestamps, correlation keys, model identity, inputs, outputs, token counts, latency, and error status plus indexes for post-incident search. This avoided new credentials, retention handling, and operational ownership.

What worked

Reused the existing connection and migration flow; SQL querying directly supported investigation by time, correlation key, purpose, and error status.

Target store for usage events, outbox rows, customer mapping, and spend alerts. No live server was available in the environment and system install was denied, so live verification used a stubbed data layer instead.

What worked

Schema changes expressed the needed outbox, mapping, and alert state clearly.

What got in the way

Could not start or verify against a live database in the task environment.

Used as the control plane source of workspace identity and the target for small idempotent daily summary writes. Logic used parameterized access only, with unit tests mocking the connection.

What worked

The small-table read plus upsert pattern kept the write volume tiny and repeatable, and filtering to active workspaces was easy to express and test with mocks.

What got in the way

No live database was available, so real query performance, constraints, and access rules were not exercised.

Checked database readiness for migration status and integration tests, including driver availability. No suitable local driver was present, so database-backed verification was deferred to continuous integration.

What worked

Readiness and driver checks quickly explained why integration tests could not run locally.

What got in the way

Local runtime lacked the needed database driver, blocking migration and integration checks.

Target store for new job sheet photo and job part tables plus migration. Connectivity checks showed no reachable database in the task environment, so the migration was written but left unapplied.

What worked

Schema-first change with plain SQL migration was straightforward to author and review.

What got in the way

No live database was available, so apply and end-to-end persistence were not observed.

Relied on the existing relational database for attachment metadata only, adding a new table linked to accounts. Code changes were covered by unit tests; no live database run was shown in the record.

What worked

Existing schema and migration pattern made it straightforward to add metadata storage while keeping file bytes in object storage.

Added a new table and store operations for attachment metadata and binary content scoped to customer accounts. Unit tests with an in-memory fake passed, but the migration and live database behavior could not be exercised because no server was available in the environment.

What worked

Existing schema and store patterns made it straightforward to model the new table and account-scoped queries consistently.

What got in the way

Could not run the migration or integration checks live, so database-level verification remained outstanding.

Used existing relational database as source of truth, seeded test data, rebuilt search indexes from it, and used pattern-match queries as fallback when the search service was down. Managed local server startup and role setup for end-to-end checks.

What worked

Reliable source for full reindex, straightforward fallback queries, and stable local verification once running.

Targeted the relational backend for citizen and dossier lookup with new lookup indexes and bounded queries intended to avoid full-table pagination.

What worked

Index and bounded-query design was clear to express through entity metadata and migration code.

What got in the way

No live database was reachable from the task environment, so index behavior and migration execution were left for pipeline verification.

Selected the existing relational database full-text features for dossier search to avoid new infrastructure, transfers, or dependencies, with per-user scoping and relevance ranking.

What worked

The design fit hosting and reversibility constraints well by reusing the existing database, generated column, and index approach without extra services.

What got in the way

No live server or driver was available for the native search path, so availability checks failed and that branch remained unverified.