Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

intermediate⏱ 3 h 🟡 Low costSAA-C03DVA-C03SCS-C03

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}/transactions is 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 DELETE or 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:

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:

  1. Data model (Data/): customers, accounts and transactions as EF Core entities, decimal(18,2) for money, snake_case columns, enums stored as readable text.
  2. Correctness in the database (Posting/): a UNIQUE (account_id, idempotency_key) index and an optimistic concurrency token on accounts.version. The application code leans on these two guarantees instead of in-process locks, so it stays correct with many API instances.
  3. Connectivity (Connectivity/): one NpgsqlDataSource, 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.
  4. 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

NeedAWS service in the labFree alternative
Managed relational database with backups and point-in-time restoreAmazon 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 controlAWS KMS customer managed keyNot applicable locally (explained, not exercised)
No static application passwordIAM database authentication; RDS-managed master secret in Secrets ManagerLocal connection string with a throwaway password
Least privilegeAn IAM role scoped to one secret and one rds-db:connect resourceDatabase role ledger_app can be created in the local container too
Prove recoverabilityRDS point-in-time restore + fingerprintSQLite online backup in the unit tests

The lab is split so that most of it is free:

PartCost labelWhat runs
A. Unit tests🟢 freeDocker (or .NET SDK) only
B. Run the API on SQLite🟢 free.NET only
C. PostgreSQL in Docker and a concurrency stress🟢 freeDocker Compose
D. Deploy to RDS, IAM auth, restore drill🟡 creditsRDS 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🟢 freeA 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 equivalentWhat's different
Azure SQL Database with a managed identity loginRDS PostgreSQL with IAM database authenticationA 15-minute token generated locally; PostgreSQL, MySQL and MariaDB only, not SQL Server
TDE with a key in Key VaultRDS storage encryption with a KMS customer managed keyChosen at creation; covers backups, snapshots and replicas
Azure Key Vault secret for the SQL passwordRDS-managed master secret in Secrets ManagerRDS creates, stores and rotates it for you
Geo-restore / point-in-time restore of Azure SQLrestore-db-instance-to-point-in-timeAlways creates a new instance with the settings you pass; you must pass subnet group, security groups and parameter group
dotnet ef database update on releasemigrate command (migration job)One deliberate step with its own identity, not application startup
Azure Private Link to the databasePrivate subnets, security group admitting only the appNo 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 file

Step-by-step implementation

Part A: run the tests (🟢 free)

From labs/m06-banking-data-store, with the .NET SDK:

dotnet test --solution BankingDataStore.slnx

Or 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 start

In 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 $body

The 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 -- fingerprint

The 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.slnx

Start 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=5

Expected: 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 tfplan

Read 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):

  1. Download the RDS CA bundle: curl -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
  2. Run migrate once with the master secret (Secrets Manager mode, user ledger_admin, SecretId = terraform output master_secret_arn).
  3. Run scripts/create-app-user.sql as ledger_admin, then seed.

The SQL creates the login the API will use. Read it line by line, it is the heart of least privilege:

scripts/create-app-user.sql
-- 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.Api

Then 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.sh

The 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 (not aws/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 $REGION

Infrastructure 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.

infra/terraform/main.tf (excerpt: the instance)
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
}
infra/terraform/main.tf (excerpt: the application role policy)
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.

  1. Data/Entities.cs and Data/LedgerDbContext.cs: the model, IsConcurrencyToken() on Account.Version, the unique index on (AccountId, IdempotencyKey), snake_case naming.
  2. Posting/PostingService.cs: the unit of work.
  3. Connectivity/DatabaseRegistration.cs, PasswordSources.cs, LedgerDatabaseOptions.cs: how credentials reach Npgsql.
  4. Restore/LedgerFingerprint.cs and the fingerprint command in Program.cs.
  5. tests/: how each guarantee is tested without AWS.

The excerpt below is the registration that ties pooling, retry and credentials together:

Connectivity/DatabaseRegistration.cs (excerpt)
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:

Posting/PostingService.cs (excerpt)
// 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

  1. AddSingleton(sp => BuildDataSource(...)) creates one NpgsqlDataSource for the process. It owns the connection pool and the credential refresh. EF Core then borrows connections from it, so the pool size you set in MaxPoolSize is the real ceiling (see pooling).
  2. EnableRetryOnFailure(...) turns on the retrying execution strategy: five attempts, a 10-second maximum delay. Because it is on, every transaction must run inside strategy.ExecuteAsync, or EF Core refuses to start it.
  3. UsePeriodicPasswordProvider calls the password source (Secrets Manager or IAM token) when Npgsql needs a new physical connection. The CredentialRefreshInterval is 10 minutes, below the 15-minute token life. The 5-second argument is how soon to retry after a failure.
  4. BuildConnectionString (not shown) sets SslMode=VerifyFull, the CA bundle path, MaxPoolSize (default 20) and ConnectionLifetime=900 so connections retire and re-authenticate regularly.
  5. In PostAsync, the delegate passed to ExecuteAsync is 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.
  6. account.Version++ plus the concurrency token on Version makes EF Core emit UPDATE accounts SET balance = @p, version = @new WHERE account_id = @id AND version = @old. If another writer got there first, zero rows match and EF throws DbUpdateConcurrencyException; the service re-reads and tries again, up to five times, then returns 409.
  7. 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 23505 for the other, which IsUniqueViolation catches and converts into a replay of the winner's result.
  8. RdsSecret.ToString() (in PasswordSources.cs) prints Password = ***, so an accidental log line never leaks it. Never log connection strings.
  9. LedgerFingerprint.ComputeAsync counts 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.
  10. restore-drill.sh uses restore-db-instance-to-point-in-time with 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_app has no DELETE and 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=1 on the server; VerifyFull with 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:connect ARN. 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=VerifyFull is only for the AWS part.

Troubleshooting

SymptomLikely causeFix
The configured execution strategy 'NpgsqlRetryingExecutionStrategy' does not support user-initiated transactionsBeginTransaction called outside strategy.ExecuteAsyncWrap 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 minutesCheck 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 encryptionrds.force_ssl=1 and the client did not use TLSUse SSL Mode=VerifyFull with Root Certificate=global-bundle.pem
The remote certificate is invalidMissing or outdated RDS CA bundleDownload the current global bundle
Timeout connecting from your laptopThe database is private by designConnect from inside the VPC
AccessDeniedException on GetSecretValueThe role lacks the secret ARN, or kms:Decrypt via Secrets ManagerCompare with aws_iam_role_policy.ledger_app
409 Concurrent update under heavy loadMany writers on one account exhausted the retry budgetRetry after Retry-After; for a hot account consider a queue per account
terraform apply fails on the DB subnet groupSubnets in the same Availability ZoneSupply subnets in at least two AZs (the variable validates this)
Restore drill prints differentThe source took writes after the restore pointPause writes, or expect the difference to be exactly the later transactions
dotnet test reports 2 skippedAWS smoke test and PostgreSQL integration test are opt-inSet 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-17 that 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: does ix_transactions_account_id_booking_date serve your query? Check with EXPLAIN in 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

Knowledge check

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

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.

On this page