Azure SQL Database

Integrated outbox and inbox persistence with the existing database architecture and authored schema and recovery scripts. Local relational tests used SQLite. SQL Server-specific locking and upgrade tests were skipped without an instance, so hosted database behavior and migration execution remained unverified.

Reviewed the existing day-granularity rating procedure and rewrote the monthly path to run atomically in one transaction, leaving detailed contract rules in the API engine. Only SQL files were edited; nothing was executed against a live database in the record.

What worked

Procedure structure was easy to preserve as a wrapper while moving complex pricing logic out of SQL.

Reworked the daily rating procedure to be version-scoped and idempotent per customer month, applying the rate from each usage day with null-safe pricing instead of a month-wide delete and reinsert.

What worked

Set-based versioned logic fit the restatement requirement well without application changes.

What got in the way

Production migration was deferred, so behavior was verified through application tests rather than a live database run.

Authored forward and rollback database scripts for the new envelope table. No live database was available, so scripts were verified by review and build-time model consistency rather than execution.

Selected the serverless free-offer option for predictable pilot costs and defined the server, database, connection settings, and initial schema through infrastructure and app configuration. Documentation clarified free limits and autopause behavior, but translating that into capped cores, size, backup, and networking settings required care. Local checks passed with live deployment left as a manual follow-up.

What worked

Permanent free tier with autopause on exhaustion directly matched the no-surprise-bill requirement, and the relational model fit the existing inventory concepts.

What got in the way

No live database was exercised during the task, so connection behavior, migrations, and pause-resume handling remain unverified.

Authored forward and rollback migration scripts for new evidence tables against a private-network database pattern. Verified logic with an in-memory harness only; live migration and access were not exercised.

What worked

Private access pattern and DBA-owned script approach were clear from existing infrastructure.

Added relational tables for research runs, stored findings with source and dates, explicit gaps, and reviewer decisions linked to the policyholder record. Produced a versioned SQL migration alongside ORM entity changes. Build passed but no live database apply was performed.

What worked

The existing context pattern made it straightforward to add run, finding, and gap records with review state.

What got in the way

No live database was available, so migrations were written but not applied and runtime persistence was not exercised.

Authored a migration script with rollback for a findings table linked to the policyholder plus a run-level gap record, reusing the existing database. The script was never applied to a live database in this task.

What worked

Table-per-item with passage, source, and date mapped cleanly from the entity model.

Reviewed application configuration and data-access code to confirm raw usage remains stored in region-pinned managed database infrastructure for residency and reconciliation needs.

What worked

Configuration made the residency and connection approach readable without needing to inspect live infrastructure.

What got in the way

No live database round trip was observed in the record, so runtime behavior and retention enforcement remain unverified.

Relied on Azure SQL Database as the supported platform database for threads, messages, and approvals, accessed through JPA and migrations rather than homegrown state. No live database was exercised in the recorded task beyond tests and configuration.

What worked

Existing platform configuration and migration conventions made it clear where relational state should live.

Reviewed existing database context and configuration as background for ledger retention and reconciliation. No live database was used; verification relied on in-memory and unit coverage.

Added referral and evidence tables linked to the policyholder with passage, source, dates and review status, plus forward and rollback migration scripts. Verified through a successful solution build; live database apply was left to normal DBA review.

What worked

Relational model fit provenance storage well, and migrations kept schema changes reviewable without auto-migration.

Recommended as the single Azure-native relational option for pilot inventory data and configured as serverless with free-limit and auto-pause settings for cost safety. No live deployment or live connection was observed; verification used a local test database instead.

What worked

Docs clarified serverless free allowance and low-cost follow-on sizing for a small pilot scale. Configuration model for free-limit behavior and idle pause was clear enough to express cost guardrails.

What got in the way

Free-tier SKU and limit behavior needed extra doc searching to confirm. Live provisioning and connectivity were never exercised in the record.

Used as the transactional outbox store alongside business writes, with a migration script for outbox and idempotency tables. No live database verification was observed in the record.

What worked

Same-transaction business plus outbox writes provided a clear fix for the dual-write loss window.

Recommended Azure SQL Database's free serverless offer, then defined the server and database in infrastructure-as-code with Entra-only auth and the setting that pauses the database when the monthly free allowance runs out. No live database was available, so the integration was never run against the real service.

What worked

The free offer fits a cost-capped pilot well: no time limit, and an explicit setting to auto-pause instead of billing once the allowance is used. That setting turned the developer's 'no surprise bill' requirement into one configuration choice. Entra-only auth meant no passwords had to be stored.

What got in the way

Serverless auto-pause means slow cold starts, so the app needed retry and idle-connection handling and a 503 path for when the database is paused. A private endpoint would have cost money, so the firewall had to allow all Azure services. I couldn't confirm behavior without a real instance.

Reviewed the existing relational model and uniqueness guardrails to design per-pair transactions that skip already invoiced periods and leave closed periods untouched.

What worked

Documented correctness rules were clear and mapped directly onto the batch runner behavior.

Reviewed existing SQL configuration and migration conventions, then authored versioned migration and rollback scripts for the new evidence tables. No live database was available to apply them.

What worked

Existing migration and rollback conventions made the expected audit shape clear.

Added a delivery tracking table with idempotency keys, attempt counts, masked recipient data, and least-privilege grants in the same regional database; migration was authored but not executed here.

What worked

Existing migration pattern and private-endpoint database setup made it simple to keep retention in region with minimal stored data.

Kept the existing relational database as the money record for invoices, idempotent payments, and reconciliation reports. No new infrastructure was provisioned; uniqueness constraints enforce one outcome per invoice.

What worked

Unique indexes and set-based aggregation supported idempotent collection and totals comparison without an external ledger.

I used the existing database as the durable outbox: the movement and the unpublished row commit together, and a script adds the new tables and a uniqueness rule. An application lock is what keeps one publisher in recorded order across instances. I did not apply the script or open a real server in this session, so those behaviors were not observed.

What worked

A single transaction plus a session lock fits the requirement that a recorded movement survive a process swap and stay in order until it is published.

What got in the way

There was no live server run. A uniqueness rule in the script will fail if duplicate open rows already exist, and that was not tried.

I used the free-offer documentation to select a serverless database that pauses when the monthly allowance is exhausted, then encoded that offer, auto-pause, and a small connection pool in the app and its deployment template. The published limits were specific enough to plan around, but the docs understated how many free databases a subscription can hold and the capacity setting did not match the schema. No live database session was opened from this environment.

What worked

The free offer is a standing monthly allowance with an explicit pause-instead-of-bill behavior, which matched a hard spend cap. Compute, data, and backup limits were concrete enough to size one shared database and to set an alert threshold before pause.

What got in the way

Guidance still described a one-database limit while the newer offer allows more, and the property that selects pause-on-exhaustion had to be cross-checked. A fractional minimum capacity did not fit the integer schema. Pause, resume, and billing behavior were never observed against the service.

Designed the outbox so the movement and the unpublished row commit in the same Azure SQL transaction, and wrote a SQL change script for the outbox, delivery, and uniqueness objects. The database is an existing single-database tier, which ruled out Service Broker. The script was not applied, and no connection to Azure SQL was made, so locking and transactional behavior were not observed.

What worked

The existing database was a viable transactional store for the outbox, and the change script could add the tables and the uniqueness constraint the handlers needed.

What got in the way

Service Broker is unavailable on this Azure SQL Database tier, so broker-style activation could not be used. The new script was left for a later apply and was never executed here.

The existing regional Azure SQL database was used as the durable outbox, written in the same transaction as encounter completion. A versioned script added the outbox table, and the drainer claims rows with SQL Server update locks and read-past hints. Unit tests around the JDBC outbox passed. No connection to a live Azure SQL server was made.

What worked

The regional database already matched the residency boundary, and a transactional outbox fit the existing store better than an in-memory spool. Locking hints gave a clear claim pattern for concurrent publishers.