Lab 6: Harbourline banking data store (RDS PostgreSQL, EF Core, IAM auth, restore drill)
Build and run the module project: an idempotent, concurrency-safe ledger API on EF Core and PostgreSQL, first locally, then on an encrypted private RDS instance with IAM authentication, ending in a measured, evidenced point-in-time restore drill and a written decision record.
Assumes: .NET 10 LTS · EF Core 10.0.15 · Npgsql 10.0.3 · AWS SDK for .NET v4 · PostgreSQL 17 · Terraform AWS provider v6 · Region us-east-1 · labs/m06-banking-data-store. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lab you can:
- Run a customer / account / transaction ledger on EF Core 10 and PostgreSQL, locally with SQLite or Docker, and read every layer of the code.
- Prove that
POST /accounts/{id}/transactionsis idempotent (same key, one booking) and that concurrent writers cannot lose an update. - Deploy an encrypted, private, TLS-forced RDS for PostgreSQL instance with a customer managed KMS key, an RDS-managed master secret and a least-privilege application role, using Terraform.
- Connect the API with IAM database authentication (and know the Secrets Manager alternative) and a database login that cannot
DELETEor change the schema. - Run a point-in-time restore drill, compare ledger fingerprints, record the time taken, and keep the evidence.
- Write the decision record (ADR) that justifies the data store, and tear everything down.
Prerequisites
This lab is the module project. It uses every lesson in the module that concerns a relational store:
- Database selection and SQL vs NoSQL: the ADR comes from this thinking
- Amazon RDS for SQL Server and PostgreSQL
- Multi-AZ, replicas, backups and PITR: the restore drill
- Encryption and database access: KMS, TLS, IAM authentication
- Connection pooling and RDS Proxy: pool sizing, and why the lab does not need a proxy
- EF Core on AWS: execution strategy, units of work, migrations
- Transactions, isolation levels and consistency: why the concurrency token works
- Tools: Docker Desktop. Optional: .NET 10 SDK (every command also runs in
mcr.microsoft.com/dotnet/sdk:10.0). For the AWS part only: AWS CLI v2 with thelearn-sandboxSSO profile, Terraform 1.10 or newer,jq, the VPC from Module 2 (two private subnets), and a way into the VPC (CloudShell VPC environment, an SSM session on a bastion, or an ECS task).
Why this matters in banking
A ledger is the one place where "mostly correct" is not acceptable. Two failures define the project:
- A customer clicks Pay twice, or a client retries after a timeout. Without idempotency, the account is debited twice. With it, the second call returns the first call's result and changes nothing.
- Two payments hit one account in the same millisecond. Without concurrency control, both read the balance £813.48, both subtract, and one debit silently vanishes (a lost update). The bank is short and nobody notices until reconciliation.
Around these sits the operational half: who can connect, whether data is encrypted with a key the bank controls, and whether the bank can prove it can restore. Regulators and internal audit care less about what you configured than about the evidence that it works: a restore drill report with a measured time and a matching fingerprint is that evidence.
The idea in plain language
The project is one small .NET API with four layers, each demonstrating something you can explain in an interview:
- Data model (
Data/): customers, accounts and transactions as EF Core entities,decimal(18,2)for money, snake_case columns, enums stored as readable text. - Correctness in the database (
Posting/): aUNIQUE (account_id, idempotency_key)index and an optimistic concurrency token onaccounts.version. The application code leans on these two guarantees instead of in-process locks, so it stays correct with many API instances. - Connectivity (
Connectivity/): oneNpgsqlDataSource, credentials from a password source that is either a connection string (local), a Secrets Manager secret, or a 15-minute IAM token. No secret in config. - Operations (
Restore/,scripts/,infra/): a fingerprint command, a restore drill script and Terraform for an encrypted private instance.
Analogy: The unique index is the receipt book that refuses to print a second receipt with the same number. The concurrency token is the rule "when you update an account card, show me the version you started from; if someone changed it in the meantime, start again". The fingerprint is a checksum of the whole ledger, like counting and weighing the vault contents before and after moving house.
What problem the AWS service solves
| Need | AWS service in the lab | Free alternative |
|---|---|---|
| Managed relational database with backups and point-in-time restore | Amazon RDS for PostgreSQL 17 (db.t4g.micro, single-AZ for the lab) | PostgreSQL 17 in Docker, or SQLite for the no-Docker path |
| Encryption with a key you control | AWS KMS customer managed key | Not applicable locally (explained, not exercised) |
| No static application password | IAM database authentication; RDS-managed master secret in Secrets Manager | Local connection string with a throwaway password |
| Least privilege | An IAM role scoped to one secret and one rds-db:connect resource | Database role ledger_app can be created in the local container too |
| Prove recoverability | RDS point-in-time restore + fingerprint | SQLite online backup in the unit tests |
The lab is split so that most of it is free:
| Part | Cost label | What runs |
|---|---|---|
| A. Unit tests | 🟢 free | Docker (or .NET SDK) only |
| B. Run the API on SQLite | 🟢 free | .NET only |
| C. PostgreSQL in Docker and a concurrency stress | 🟢 free | Docker Compose |
| D. Deploy to RDS, IAM auth, restore drill | 🟡 credits | RDS db.t4g.micro, KMS, Secrets Manager. About USD 0.02 to 0.03 per hour plus pro-rated monthly key and secret charges; destroy the same day |
| E. ADR and cleanup | 🟢 free | A text editor |
If you do not want to deploy anything, do parts A to C and read the Terraform and the restore script for part D (a free, design-only alternative).
Microsoft / Azure comparison
| You know (Microsoft/Azure) | Lab equivalent | What's different |
|---|---|---|
| Azure SQL Database with a managed identity login | RDS PostgreSQL with IAM database authentication | A 15-minute token generated locally; PostgreSQL, MySQL and MariaDB only, not SQL Server |
| TDE with a key in Key Vault | RDS storage encryption with a KMS customer managed key | Chosen at creation; covers backups, snapshots and replicas |
| Azure Key Vault secret for the SQL password | RDS-managed master secret in Secrets Manager | RDS creates, stores and rotates it for you |
| Geo-restore / point-in-time restore of Azure SQL | restore-db-instance-to-point-in-time | Always creates a new instance with the settings you pass; you must pass subnet group, security groups and parameter group |
dotnet ef database update on release | migrate command (migration job) | One deliberate step with its own identity, not application startup |
| Azure Private Link to the database | Private subnets, security group admitting only the app | No public endpoint at all |
Architecture
Project layout
labs/m06-banking-data-store/
├── BankingDataStore.slnx
├── docker-compose.yml local PostgreSQL 17 (127.0.0.1:55432)
├── seed/ customers.csv, accounts.csv, transactions.csv (fictional)
├── docs/adr-0001-ledger-data-store.md the decision record you will review and extend
├── src/Harbourline.Ledger.Api/
│ ├── Program.cs API + `migrate`, `seed`, `fingerprint` commands
│ ├── Data/ entities, LedgerDbContext, CSV seeder, EF Core migrations
│ ├── Posting/PostingService.cs idempotent, concurrency-safe posting
│ ├── Connectivity/ Npgsql data source, Secrets Manager and IAM-token password sources
│ ├── Restore/LedgerFingerprint.cs counts, totals and a SHA-256 digest for restore drills
│ └── Endpoints/LedgerEndpoints.cs minimal API with ProblemDetails errors
├── tests/Harbourline.Ledger.Api.Tests/ xUnit v3 on Microsoft.Testing.Platform (no AWS needed)
├── infra/terraform/ KMS key, subnet group, SG, parameter group, RDS, app IAM role
└── scripts/
├── create-app-user.sql least-privilege `ledger_app` login with rds_iam
└── restore-drill.sh PITR restore + fingerprint comparison + evidence fileStep-by-step implementation
Part A: run the tests (🟢 free)
From labs/m06-banking-data-store, with the .NET SDK:
dotnet test --solution BankingDataStore.slnxOr with only Docker (Git Bash; a private NuGet cache volume; build output deleted afterwards):
MSYS_NO_PATHCONV=1 docker run --rm --memory 3g \
-v "$(pwd -W):/src" -v cea-nuget-m06:/root/.nuget/packages -w /src \
mcr.microsoft.com/dotnet/sdk:10.0 \
sh -c "dotnet test --solution BankingDataStore.slnx; find . -type d \( -name bin -o -name obj \) -prune -exec rm -rf {} +"Expected: 46 tests, 0 failed. Without any environment variables, 44 pass and 2 are skipped: the AWS smoke test (needs RUN_AWS_TESTS=1) and the PostgreSQL integration test (needs HL_TEST_POSTGRES, see Part C). The suite covers the seed and model, posting rules, idempotency (including a retry "from a fresh process"), concurrency, credential sources with the Secrets Manager client mocked, IAM token shape, and the restore fingerprint.
Part B: run the API on SQLite (🟢 free)
cd labs/m06-banking-data-store/src/Harbourline.Ledger.Api
dotnet run # Development + SQLite: creates ledger.db and seeds it on first startIn a second terminal:
curl http://localhost:5080/accounts/ACC-1000000001
$key = [guid]::NewGuid()
$body = '{"amount":-42.50,"currency":"GBP","type":"transfer_out","channel":"api","counterpartyName":"Quayside Energy plc","counterpartyIban":"GB33BUKB20201555555555","reference":"ENERGY-NOV"}'
curl -i -X POST http://localhost:5080/accounts/ACC-1000000001/transactions -H "Content-Type: application/json" -H "Idempotency-Key: $key" -d $body
curl -i -X POST http://localhost:5080/accounts/ACC-1000000001/transactions -H "Content-Type: application/json" -H "Idempotency-Key: $key" -d $bodyThe first POST returns 201 Created with the booked transaction and a balance of 770.98. The second returns 200 OK, the same transactionId, the same balance and the header Idempotent-Replayed: true. Now change the amount but keep the key: you get 422 with the title Idempotency-Key reused, because the API stores a hash of the original request and refuses a different payment under an old key.
Part C: PostgreSQL in Docker (🟢 free)
cd labs/m06-banking-data-store
docker compose up -d
$env:Ledger__Database__Provider = "Postgres"
$env:Ledger__Database__ConnectionString = "Host=localhost;Port=55432;Database=ledger;Username=postgres;Password=local-only-password"
dotnet run --project src/Harbourline.Ledger.Api -- migrate # applies EF Core migrations
dotnet run --project src/Harbourline.Ledger.Api -- seed
dotnet run --project src/Harbourline.Ledger.Api -- fingerprintThe fingerprint of the seeded database reports 31 customers, 57 accounts, 1012 transactions and a 64-character SHA-256 digest. Keep it: it is the "before" evidence for the restore drill.
Run the real-database integration test (real migrations plus 20 concurrent postings on one account). It drops and recreates the database you point it at, so use a throwaway one:
$env:HL_TEST_POSTGRES = "Host=localhost;Port=55432;Database=ledger_it;Username=postgres;Password=local-only-password"
dotnet test --solution BankingDataStore.slnxStart the API against PostgreSQL (dotnet run --project src/Harbourline.Ledger.Api) and stress the idempotency guarantee with 20 simultaneous requests carrying the same key (PowerShell 7):
$key = [guid]::NewGuid().ToString()
$body = '{"amount":-10.00,"currency":"GBP","type":"transfer_out","channel":"api","counterpartyName":"Quayside Energy plc","counterpartyIban":"GB33BUKB20201555555555","reference":"STRESS-1"}'
1..20 | ForEach-Object -Parallel {
curl.exe -s -o NUL -w "%{http_code}`n" -X POST http://localhost:5080/accounts/ACC-1000000001/transactions `
-H "Content-Type: application/json" -H "Idempotency-Key: $using:key" -d $using:body
} -ThrottleLimit 20 | Sort-Object | Group-Object | Select-Object Name, Count
curl http://localhost:5080/accounts/ACC-1000000001/transactions?limit=5Expected: one 201 and the rest 200. Exactly one STRESS-1 transaction exists and the balance moved by £10.00 once. (Under extreme contention a few requests may return 409 with Retry-After. That is the API telling the client to retry, and it still never double-books.)
Part D1: deploy the infrastructure (🟡 credits, destroy the same day)
aws sso login --profile learn-sandbox
cd labs/m06-banking-data-store/infra/terraform
copy terraform.tfvars.example terraform.tfvars # fill in VPC, two private subnets, app SG, your name
terraform init
terraform plan -out tfplan
terraform apply tfplanRead the plan before you apply. It creates: a KMS key with rotation and a policy that only lets RDS and Secrets Manager use it via kms:ViaService; a DB subnet group; a security group admitting only your application's security group on 5432; a parameter group with rds.force_ssl=1 and log_min_duration_statement=500; a single-AZ, encrypted, non-public db.t4g.micro PostgreSQL 17 instance with IAM authentication enabled, an RDS-managed master password, 1-day backups and CloudWatch log export; and an application IAM role allowed to read one secret, decrypt through Secrets Manager only, and rds-db:connect as one database user.
Part D2: create the schema and the least-privilege login
The database is private, so run these from inside the VPC (CloudShell VPC environment, or an SSM session on a bastion):
- Download the RDS CA bundle:
curl -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem - Run
migrateonce with the master secret (Secrets Manager mode, userledger_admin,SecretId=terraform output master_secret_arn). - Run
scripts/create-app-user.sqlasledger_admin, thenseed.
The SQL creates the login the API will use. Read it line by line, it is the heart of least privilege:
-- Run ONCE as the master user (ledger_admin, password from the RDS-managed secret) after `migrate`.
-- Creates the least-privilege application login. The app never uses the master user.
--
-- psql "host=<db_endpoint> dbname=ledger user=ledger_admin sslmode=verify-full sslrootcert=global-bundle.pem" -f create-app-user.sql
-- IAM-authenticated login: no password stored anywhere; the app presents a 15-minute token.
CREATE ROLE ledger_app WITH LOGIN;
GRANT rds_iam TO ledger_app;
-- Only what the API needs: read everything, insert transactions, update account balances.
GRANT CONNECT ON DATABASE ledger TO ledger_app;
GRANT USAGE ON SCHEMA public TO ledger_app;
GRANT SELECT ON customers, accounts, transactions TO ledger_app;
GRANT INSERT ON transactions TO ledger_app;
GRANT UPDATE (balance, version, status) ON accounts TO ledger_app;
-- No DELETE anywhere: a ledger is append-only. Corrections are new reversing transactions.
-- No DDL: schema changes run as a separate migration step with a different identity.Part D3: run the API against RDS with IAM authentication
From inside the VPC, with the task role (or your SSO session for a quick test), run the API with these settings. None contains a password:
$env:Ledger__Database__Provider = "Postgres"
$env:Ledger__Database__AuthMode = "IamAuth"
$env:Ledger__Database__Host = "<terraform output db_endpoint>"
$env:Ledger__Database__Username = "ledger_app"
$env:Ledger__Database__RootCertificatePath = "global-bundle.pem"
dotnet run --project src/Harbourline.Ledger.ApiThen repeat the idempotent POST from Part B against this API. To check what the database sees, run the access probe from the encryption lesson: it must report TLS on, no rds_superuser, and no DELETE on transactions. The SecretsManager auth mode works the same way with Ledger__Database__SecretId.
Part D4: the restore drill
OWNER=<you> ./scripts/restore-drill.shThe script restores the source instance to its latest restorable time as a new instance with the same subnet group, security groups and parameter group (and never public), times how long it takes to become available, runs fingerprint against source and restore, compares them, writes drill-report-<timestamp>.json, then deletes the copy. For a quiet lab database both fingerprints are identical. If you post transactions after the restore point, the source differs by exactly those transactions, which is itself a useful thing to observe.
Keep the report. It records the restore point, how many seconds the restore took (your measured RTO for this database size) and both fingerprints.
Part E: write it down and tear it down
Review docs/adr-0001-ledger-data-store.md, which records why RDS PostgreSQL was chosen over RDS for SQL Server, Aurora PostgreSQL and DynamoDB, then complete the assignment below. Finish with the cleanup in the next sections.
AWS Console instructions
Use the console to look, not to build. The Terraform creates everything.
- Console → RDS → Databases → hl-m06-ledger → Configuration: confirm Encryption: Enabled, the KMS key is
alias/hl-m06-ledger(notaws/rds), Public access: No, IAM DB authentication: Enabled, backup retention 1 day. - Connectivity & security: check the certificate authority (default
rds-ca-rsa2048-g1) and the security group, which should list only your application's group on 5432. - Maintenance & backups: the Latest restore time is the upper bound for your drill.
- Console → KMS → Customer managed keys: open the key, confirm Key rotation: Enabled and read the key policy.
- Console → Secrets Manager: find the
rds!db-...secret, open Rotation and note it is managed by RDS. - Console → RDS → Snapshots → System: automated snapshots appear here. Nothing in the lab should create manual snapshots, and you must not leave any behind.
- Console → CloudWatch → Log groups → /aws/rds/instance/hl-m06-ledger/postgresql: the engine log, including any statement over 500 ms.
AWS CLI
PROFILE=learn-sandbox
REGION=us-east-1
DB=hl-m06-ledger
# What did Terraform create? Check the security posture in one query.
aws rds describe-db-instances --db-instance-identifier $DB \
--query 'DBInstances[0].{Class:DBInstanceClass,Encrypted:StorageEncrypted,Key:KmsKeyId,Public:PubliclyAccessible,IAMAuth:IAMDatabaseAuthenticationEnabled,Retention:BackupRetentionPeriod,LatestRestorable:LatestRestorableTime,Secret:MasterUserSecret.SecretArn}' \
--profile $PROFILE --region $REGION
# Is TLS enforced by the parameter group?
aws rds describe-db-parameters --db-parameter-group-name $DB \
--query "Parameters[?ParameterName=='rds.force_ssl'].[ParameterName,ParameterValue]" --output text \
--profile $PROFILE --region $REGION
# Generate an IAM authentication token by hand (valid 15 minutes) to test with psql.
export PGPASSWORD=$(aws rds generate-db-auth-token --hostname <db-endpoint> --port 5432 --username ledger_app \
--profile $PROFILE --region $REGION)
# What restore points exist? (The drill uses --use-latest-restorable-time.)
aws rds describe-db-instance-automated-backups --db-instance-identifier $DB \
--query 'DBInstanceAutomatedBackups[].[RestoreWindow.EarliestTime,RestoreWindow.LatestTime]' --output table \
--profile $PROFILE --region $REGION
# Read the master secret's metadata (never print the value into a shared terminal or log).
aws secretsmanager describe-secret --secret-id "$(terraform -chdir=infra/terraform output -raw master_secret_arn)" \
--query '{Name:Name,RotationEnabled:RotationEnabled,KmsKeyId:KmsKeyId}' --profile $PROFILE --region $REGIONInfrastructure as Code
The lab ships Terraform in infra/terraform/ (validated, not applied by the author). The decisions that matter are in the resources below. The excerpts are the real ones; open main.tf for the full file including the key policy, subnet group and security group.
resource "aws_db_instance" "ledger" {
identifier = local.name
engine = "postgres"
engine_version = "17"
instance_class = var.instance_class
allocated_storage = 20
max_allocated_storage = 50
storage_type = "gp3"
storage_encrypted = true
kms_key_id = aws_kms_key.ledger.arn
db_name = "ledger"
username = "ledger_admin"
# RDS generates the password, stores it in Secrets Manager (encrypted with our CMK) and rotates it.
manage_master_user_password = true
master_user_secret_kms_key_id = aws_kms_key.ledger.arn
iam_database_authentication_enabled = true
db_subnet_group_name = aws_db_subnet_group.ledger.name
vpc_security_group_ids = [aws_security_group.ledger_db.id]
publicly_accessible = false
parameter_group_name = aws_db_parameter_group.ledger.name
multi_az = var.multi_az
backup_retention_period = 1 # enough for a same-day PITR drill; production: 7-35 + AWS Backup
backup_window = "03:00-03:30"
maintenance_window = "sun:04:00-sun:04:30"
copy_tags_to_snapshot = true
enabled_cloudwatch_logs_exports = ["postgresql"]
auto_minor_version_upgrade = true
# ⚠ Learning shortcut: easy teardown. Production: deletion_protection = true and a final snapshot.
deletion_protection = false
skip_final_snapshot = true
delete_automated_backups = true
}resource "aws_iam_role_policy" "ledger_app" {
name = "ledger-database-access"
role = aws_iam_role.ledger_app.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Sid = "ReadOnlyTheLedgerSecret"
Effect = "Allow"
Action = "secretsmanager:GetSecretValue"
Resource = aws_db_instance.ledger.master_user_secret[0].secret_arn
},
{
Sid = "DecryptTheSecretViaSecretsManagerOnly"
Effect = "Allow"
Action = "kms:Decrypt"
Resource = aws_kms_key.ledger.arn
Condition = {
StringEquals = { "kms:ViaService" = "secretsmanager.${var.region}.amazonaws.com" }
}
},
{
Sid = "IamDatabaseAuthAsLedgerAppOnly"
Effect = "Allow"
Action = "rds-db:connect"
Resource = "arn:${data.aws_partition.current.partition}:rds-db:${var.region}:${data.aws_caller_identity.current.account_id}:dbuser:${aws_db_instance.ledger.resource_id}/ledger_app"
}
]
})
}CDK (C#): there is no CDK app in this lab on purpose, so you can compare tools in Module 10. The equivalents for the extra pieces are in the lessons: the RDS Proxy stack (needed only if you add Lambda callers), the rotated application secret stack, and the one-shot migration job.
Complete code
The full source is in labs/m06-banking-data-store. Read it in this order; each file is short and has comments.
Data/Entities.csandData/LedgerDbContext.cs: the model,IsConcurrencyToken()onAccount.Version, the unique index on(AccountId, IdempotencyKey), snake_case naming.Posting/PostingService.cs: the unit of work.Connectivity/DatabaseRegistration.cs,PasswordSources.cs,LedgerDatabaseOptions.cs: how credentials reach Npgsql.Restore/LedgerFingerprint.csand thefingerprintcommand inProgram.cs.tests/: how each guarantee is tested without AWS.
The excerpt below is the registration that ties pooling, retry and credentials together:
services.AddSingleton(sp => BuildDataSource(options, sp));
services.AddDbContext<LedgerDbContext>((sp, o) => o.UseNpgsql(
sp.GetRequiredService<NpgsqlDataSource>(),
npgsql => npgsql.EnableRetryOnFailure(
maxRetryCount: options.MaxRetryCount,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorCodesToAdd: null)));
// ...inside BuildDataSource:
var builder = new NpgsqlDataSourceBuilder(BuildConnectionString(options));
var source = CreatePasswordSource(options, sp);
if (source is not null)
{
// Called for new physical connections only; pooled connections keep working after a
// rotation because PostgreSQL checks the password at login, not per query.
builder.UsePeriodicPasswordProvider(
async (_, ct) => await source.GetPasswordAsync(ct),
options.CredentialRefreshInterval,
TimeSpan.FromSeconds(5));
}
return builder.Build();And the heart of the posting service: two defences against duplicates, one against lost updates:
// The execution strategy retries the WHOLE unit of work on transient failures (failover, dropped
// connection). That is why the idempotency check is inside the delegate: if a commit succeeded but the
// acknowledgement was lost, the retry finds the stored row and replays it instead of posting twice.
var strategy = db.Database.CreateExecutionStrategy();
return await strategy.ExecuteAsync(
(accountId, idempotencyKey, request, requestHash),
async (_, state, token) => await PostOnceAsync(state.accountId, state.idempotencyKey, state.request, state.requestHash, token),
verifySucceeded: null,
ct);
// ...and in PostOnceAsync, after the checks:
account.Balance = newBalance;
account.Version++;
db.Transactions.Add(transaction);
// One SaveChanges = one round of INSERT + UPDATE ... WHERE version = @old, inside our transaction.
await db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);Code explained line by line
AddSingleton(sp => BuildDataSource(...))creates oneNpgsqlDataSourcefor the process. It owns the connection pool and the credential refresh. EF Core then borrows connections from it, so the pool size you set inMaxPoolSizeis the real ceiling (see pooling).EnableRetryOnFailure(...)turns on the retrying execution strategy: five attempts, a 10-second maximum delay. Because it is on, every transaction must run insidestrategy.ExecuteAsync, or EF Core refuses to start it.UsePeriodicPasswordProvidercalls the password source (Secrets Manager or IAM token) when Npgsql needs a new physical connection. TheCredentialRefreshIntervalis 10 minutes, below the 15-minute token life. The 5-second argument is how soon to retry after a failure.BuildConnectionString(not shown) setsSslMode=VerifyFull, the CA bundle path,MaxPoolSize(default 20) andConnectionLifetime=900so connections retire and re-authenticate regularly.- In
PostAsync, the delegate passed toExecuteAsyncis the unit of work. If a transient failure occurs the whole delegate runs again, from a cleared change tracker, which is why the first thing it does is look up the idempotency key. account.Version++plus the concurrency token onVersionmakes EF Core emitUPDATE accounts SET balance = @p, version = @new WHERE account_id = @id AND version = @old. If another writer got there first, zero rows match and EF throwsDbUpdateConcurrencyException; the service re-reads and tries again, up to five times, then returns409.- The unique index covers the race that a pre-check cannot: two requests with the same key arrive together, both see "no such key", and both try to insert. PostgreSQL lets one succeed and raises
23505for the other, whichIsUniqueViolationcatches and converts into a replay of the winner's result. RdsSecret.ToString()(inPasswordSources.cs) printsPassword = ***, so an accidental log line never leaks it. Never log connection strings.LedgerFingerprint.ComputeAsynccounts customers, accounts and transactions, sums balances in C# (identical arithmetic on every provider), and hashes every transaction line (id, account, amount, status) in id order with SHA-256. Two databases with the same fingerprint contain the same ledger.restore-drill.shusesrestore-db-instance-to-point-in-timewith the source's subnet group, security groups and parameter group. Without those flags, a restore would come up with defaults (default parameter group, default security group) and be unreachable or unsafe, the exact mistake the backups lesson warns about.
Security considerations
⚠ Learning shortcut — not for production
The lab uses SQLite auto-create at startup in Development, local Terraform state, skip_final_snapshot, a 1-day backup retention, a single-AZ instance, and no API authentication (Modules 4 and 5 add JWT and WAF). A fixed password appears only in the local Docker compose file, for a container bound to 127.0.0.1. Each shortcut is marked in the code. Production adds Multi-AZ, 7 to 35 days of backups plus AWS Backup copies in a separate account, deletion protection, an encrypted remote Terraform backend, authentication and rate limiting on the API, and a dedicated migration identity.
- The app never uses the master user.
ledger_apphas noDELETEand no DDL. A ledger is append-only: corrections are new reversing transactions. - No static application password. IAM tokens last 15 minutes. The master password is RDS-managed and stays out of the repository, the task definition and the logs.
- Encryption at rest with your key. The CMK covers storage, automated backups, snapshots, logs and the master secret. Its policy lets only RDS and Secrets Manager in this account use it.
- TLS verified.
rds.force_ssl=1on the server;VerifyFullwith the RDS CA bundle on the client. - Private by default. No public endpoint. You reach the database from inside the VPC.
- Fictional data only. Customers and IBANs come from
datasets/samples. Never load real customer data into a lab. - Evidence handling. Drill reports contain counts, totals and a digest, not customer records. Still keep them in a controlled location.
- No compliance claim. Retention periods, erasure requests (UK GDPR right to erasure versus backups) and record-keeping duties need your compliance and data-protection teams. The ADR says so too.
Banking use case
Harbourline's retail ledger service stores current accounts, savings and card balances. This project is the smallest honest version of it:
- The mobile app posts a payment with an idempotency key it generates per user action. If the phone loses signal, the retry carries the same key.
- The payments service posts the debit exactly once even when the client, the load balancer and the SDK all retry.
- Operations can answer "what was the balance at 23:59 on date D?" with SQL joins across customers, accounts and transactions: the reason the ADR picked a relational store over DynamoDB for the system of record.
- Operational resilience gets its evidence from the drill report: restore point, measured restore time, identical fingerprint.
- A high-volume payment-status store (a good DynamoDB candidate, per the ADR) can sit beside the ledger. See the DynamoDB for banking correctness lesson.
Common mistakes
- Running the drill against a database that is still taking writes and expecting identical fingerprints. The source moves on after the restore point. Compare against the source's state at that time, or pause writes.
- Restoring without the subnet group, security groups and parameter group. The restored instance is reachable by the wrong things, or by nothing.
- Starting the migration with the application login. It has no DDL by design. Use the migration identity once, then run the app as
ledger_app. - Generating an IAM token once at startup. It expires in 15 minutes. The password provider must run per new connection (the lab's does).
- Using the instance name in the
rds-db:connectARN. It needs the DbiResourceId (terraform output db_resource_id). - Forgetting to delete the manual snapshots or the restored copy. They bill until deleted.
- Posting with a key but changing the payload. The API rejects it with
422: that is the designed behaviour, not a bug. - Expecting Docker PostgreSQL and RDS to behave identically under TLS. The local container has no TLS.
SslMode=VerifyFullis only for the AWS part.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
The configured execution strategy 'NpgsqlRetryingExecutionStrategy' does not support user-initiated transactions | BeginTransaction called outside strategy.ExecuteAsync | Wrap the whole unit of work in db.Database.CreateExecutionStrategy().ExecuteAsync(...) (see PostingService, CsvSeeder) |
28000: PAM authentication failed for user "ledger_app" | IAM token rejected: user lacks rds_iam, the role lacks rds-db:connect on that DB resource ID, or the token is older than 15 minutes | Check GRANT rds_iam, the policy ARN (dbuser:<DbiResourceId>/ledger_app), and that a token is generated per new connection |
no pg_hba.conf entry ... no encryption | rds.force_ssl=1 and the client did not use TLS | Use SSL Mode=VerifyFull with Root Certificate=global-bundle.pem |
The remote certificate is invalid | Missing or outdated RDS CA bundle | Download the current global bundle |
| Timeout connecting from your laptop | The database is private by design | Connect from inside the VPC |
AccessDeniedException on GetSecretValue | The role lacks the secret ARN, or kms:Decrypt via Secrets Manager | Compare with aws_iam_role_policy.ledger_app |
409 Concurrent update under heavy load | Many writers on one account exhausted the retry budget | Retry after Retry-After; for a hot account consider a queue per account |
terraform apply fails on the DB subnet group | Subnets in the same Availability Zone | Supply subnets in at least two AZs (the variable validates this) |
Restore drill prints different | The source took writes after the restore point | Pause writes, or expect the difference to be exactly the later transactions |
dotnet test reports 2 skipped | AWS smoke test and PostgreSQL integration test are opt-in | Set RUN_AWS_TESTS=1 or HL_TEST_POSTGRES as described above |
Cost and cleanup
The local parts cost nothing. The AWS part (🟡 credits) costs about USD 0.02 to 0.03 per hour for the db.t4g.micro instance plus storage, plus pro-rated monthly charges for the KMS key and the Secrets Manager secret, and a few cents for the drill's temporary extra instance (typically 15 to 30 minutes). Destroy everything the same day.
cd infra/terraform
terraform destroy
# Confirm nothing tagged for this lab remains (KMS keys show as PendingDeletion for 7 days: that is expected):
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab Key=Lab,Values=m06-project --profile learn-sandbox --region us-east-1
aws rds describe-db-snapshots --snapshot-type manual --profile learn-sandbox --region us-east-1 --query "DBSnapshots[?starts_with(DBSnapshotIdentifier, 'hl-m06')]"
aws rds describe-db-instances --profile learn-sandbox --region us-east-1 --query "DBInstances[?starts_with(DBInstanceIdentifier, 'hl-m06')].DBInstanceIdentifier"Local cleanup: docker compose down -v, and delete src/Harbourline.Ledger.Api/ledger.db and the bin/ and obj/ folders. Delete terraform.tfvars, tfplan and the state files when you have no further use for them. State can contain the secret ARN, and drill reports should go to your evidence location, not to a public repository.
Exercises
- 🟢 Guided: complete Parts A to C. Post the same payment twice and show that the balance moved once. Run the 20-request stress and report the status-code counts.
- 🟡 Challenge: add
GET /accounts/{accountId}/balance?asOf=2026-11-17that returns the balance at the end of a given booking date by summing transactions up to that date. Add a test with the seed data and an index review: doesix_transactions_account_id_booking_dateserve your query? Check withEXPLAINin the local PostgreSQL. - 🔴 Live problem: the business wants the ledger to survive loss of an Availability Zone with no more than 5 minutes of data loss and 15 minutes of downtime, and to survive a ransomware attack on the production account. Using what you measured in the drill, state what the current design achieves, which parts of the target it misses (Multi-AZ, cross-account backups, retention, key policy), the cost trade-offs, and what you would change. Write it as an ADR addendum.
Assignment
Deliver, in your portfolio repository: (1) a fork of the lab with your own notes; (2) docs/adr-0001-ledger-data-store.md, reviewed, with your challenge from this lab added as a "Follow-ups" item; (3) your drill-report-*.json (or, if you did not deploy, a written restore plan with expected RPO and RTO per the backups lesson); (4) a short README section titled "What I would change for production", listing at least five items from the Security notes; and (5) the output of your idempotency stress test.
Solutions
The first POST returns 201; the second, with the same key, returns 200 with Idempotent-Replayed: true, the same transactionId and the same balance. The stress test returns one 201 and nineteen 200: the requests that raced with the winner either found the stored row, or lost the insert to the unique index (23505) and were converted into a replay of the winner's result. The balance moves by exactly one payment. The reason it works under concurrency is that the database, not a lock in your process, decides which request wins, so it stays correct when you run ten API instances.
Query: SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE account_id = @id AND booking_date <= @asOf (an opening balance column is not in the model, so the seed's first transaction series defines the history; in a real ledger you would store an opening balance row). In EF Core: db.Transactions.Where(t => t.AccountId == id && t.BookingDate <= asOf).SumAsync(t => t.Amount). The existing index on (account_id, booking_date) serves both predicates; EXPLAIN shows an index range scan on ix_transactions_account_id_booking_date. If the balance must be consistent with the account row, read both in one transaction at REPEATABLE READ, as discussed in the isolation lesson.
Current design: single-AZ, 1-day retention. It survives nothing at the AZ level (RTO is a restore: tens of minutes) and its PITR window allows about 5 minutes of loss (transaction logs are uploaded about every 5 minutes ⚠ VERIFY) but only within one day. Gaps and changes: enable Multi-AZ (standby in a second AZ, failover typically 60 to 120 seconds, RPO near zero) to meet the AZ target; raise retention to the period your resilience plan requires; add AWS Backup with cross-account copies and Vault Lock in compliance mode for the ransomware case; use the CMK's key policy to allow the backup account to decrypt copies; add SCPs that deny key deletion and backup deletion to everyone but a break-glass role; alarm on DisableKey, ScheduleKeyDeletion and snapshot-sharing. Costs: Multi-AZ roughly doubles the instance cost, backup storage grows with retention, and cross-account copies add storage and transfer charges. Rehearse the restore in the backup account and record the time as the new measured RTO.
Knowledge check
Answer: The strategy re-runs the whole delegate after a transient failure. If a commit succeeded but its acknowledgement was lost, the retry finds the stored transaction by key and replays it instead of posting a second time. Outside the delegate, the retry would not re-check.
Answer: Lost updates. EF Core adds WHERE version = @original to the UPDATE, so two writers that read the same balance cannot both apply a change. The loser gets a concurrency exception and retries from a fresh read.
Answer: A ledger is append-only (corrections are reversing entries), so deletion is never needed, and a leaked application credential must not be able to remove history or change the schema. Migrations run under a different identity.
Answer: A point-in-time restore creates a new instance with default settings unless you specify them. Without them the copy could be in the wrong network, reachable by the wrong sources, or without forced TLS.
Answer: It proves the restored copy has the same customers, accounts, transactions, totals and digest as the source at the compared moment. It does not prove the application works against it, that indexes and grants were restored correctly, or that the time taken meets your RTO; the drill measures the last separately.
Interview questions
- How do you guarantee a payment is posted exactly once? Client idempotency key, unique index on
(account, key), request hash to detect reuse with a different payload, optimistic concurrency on the balance, and the unit of work inside the retrying execution strategy so that an ambiguous commit replays instead of duplicating. - How do you prove your backups work? Restore regularly into a new instance using production settings, measure the time, compare a fingerprint with the source, store the evidence, and delete the copy. Untested backups are not evidence.
- Why RDS PostgreSQL rather than DynamoDB for this ledger? Ad hoc relational queries across customers, accounts and transactions, unique constraints, familiar SQL and EF Core, balance-at-date queries, and a team without single-table design experience. DynamoDB is a strong fit for the high-volume payment-status store beside it.
- How does the app authenticate to the database without a password? It signs a 15-minute IAM token locally with its role credentials and presents it as the password for a user granted
rds_iam. Npgsql's periodic password provider refreshes it for new connections.
Certification practice
An application on Amazon ECS must connect to Amazon RDS for PostgreSQL. Company policy forbids long-lived database passwords in configuration. Which approach satisfies this with the LEAST operational effort?
A. Store the password in an environment variable in the task definition
B. Enable IAM database authentication, grant rds_iam to the application's database user and allow rds-db:connect for the task role
C. Store the password in a file in the container image
D. Use the master user and restrict the security group
Answer: B. The task role signs short-lived tokens, so no password exists to leak. A and C keep a password in configuration. D uses the master identity and violates least privilege.
A bank must show regulators that its RDS database can be restored to a point in time within 30 minutes. Which action BEST provides evidence?
A. Enable Multi-AZ B. Periodically restore to a new instance with production settings, time it, verify the data and keep the report C. Increase the backup retention period to 35 days D. Create a read replica
Answer: B. Only an actual, timed, verified restore is evidence. Multi-AZ and replicas protect against infrastructure failure, not data damage, and retention alone does not prove a restore works.
A payment API retries requests when a database failover causes a timeout. Customers are occasionally debited twice. Which change MOST directly fixes this?
A. Increase the connection timeout B. Require a client-supplied idempotency key enforced by a unique constraint C. Add a read replica D. Use a larger instance class
Answer: B. The retry is correct behaviour; the write must be idempotent. A, C and D do not prevent a duplicated insert.
Completion checklist
- I ran the test suite and understand what each group of tests proves
- I posted the same payment twice and explained the two responses
- I ran the concurrent stress test and explained the result
- I deployed (or read) the Terraform and can explain each security control in it
- I ran, or can describe, the restore drill and what the report contains
- I updated the ADR and wrote "what I would change for production"
- I ran
terraform destroy, deleted snapshots and restored copies, and confirmed nothing is still billing
Key takeaways
- Correctness lives in the database: a unique index for idempotency and a concurrency token for lost updates, not in-process locks.
- Wrap every transaction in the execution strategy, and put the idempotency check inside it.
- No static passwords: IAM tokens or rotated secrets, a least-privilege role, and a login that cannot delete or alter.
- Encrypt with a key you own, force TLS, verify the server, stay private.
- A restore you have not measured and verified is not a backup. Keep the drill report as evidence.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Database migration
Plan and rehearse a SQL Server migration to AWS: choose between RDS for SQL Server, Babelfish for Aurora PostgreSQL and a full conversion; convert schemas with DMS Schema Conversion; move data with AWS DMS full load plus CDC; and prove correctness with independent validation before cut-over.
Module 6 cheat sheet
One-page revision summary for Databases & Data Architecture: services, Azure mapping, CLI commands, limits, decision rules, exam traps and cost traps.