Lab: inspect MicroBank databases and plan their operation

Return to the existing application

Stop the separate PostgreSQL fixture first. Resume the DevOps Foundations Compose implementation in your chosen MicroBank checkout. Use its wrapper, configuration, LocalStack initialization, and existing transaction probe. Do not replace your current work with a reset or paste the teaching schema into either service database.

The source observations below refer to MicroBank revision f75da68↗. Record your actual revision and local changes; use runtime inspection to check where your checkout differs from the pinned code.

1. Identify the data owners

Inspect source without changing it:

git rev-parse HEAD
git status --short
rg -n '__tablename__|ForeignKey|UniqueConstraint|idempotency_key|published_at' services/accounts/app
rg -n 'Table|Column|unique|Transactional|ddl-auto' services/ledger/src

At the pinned revision, the Accounts transaction model declares a foreign key to accounts and a non-null idempotency key, but no composite uniqueness on account/key. Ledger declares tx_id unique. Verify actual constraints before reporting either runtime guarantee. See the Accounts model↗ and Ledger entity↗.

The separate fixture used signed entries. MicroBank has its own kind/amount contract; do not equate a raw sum of every amount with its balance endpoint.

2. Inspect each database read-only

From your MicroBank checkout:

./scripts/study/compose.sh exec accounts-db psql -X -U postgres -d accounts_dev

Inside the session:

BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SELECT current_database(), current_user, version();
\dt public.*
\d public.transactions
SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
ORDER BY table_name, constraint_name;
ROLLBACK;
\q

Then inspect Ledger separately:

./scripts/study/compose.sh exec ledger-db psql -X -U postgres -d ledger_dev

BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
\d public.ledger_entries
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'ledger_entries';
ROLLBACK;
\q

If table names differ or an expected table is absent, investigate application initialization and the selected revision. Resolve the difference before continuing; changing the schema just to match the lesson could hide an initialization problem. Information-schema constraints↗, index view↗.

3. Connect one request to persisted evidence

Use the existing Python transaction probe and its newly created fixture case. Retain its actual account/transaction IDs locally. Query only those rows, inside read-only transactions with a statement timeout, using psql variables or application-driver parameters.

For example, after connecting to Ledger and inspecting its schema:

\prompt 'Transaction UUID from your saved probe case: ' tx_id
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SELECT tx_id, account_id, kind, amount_cents
FROM public.ledger_entries
WHERE tx_id = :'tx_id'::uuid;
ROLLBACK;

In psql, :'tx_id' quotes the variable as a SQL literal. Compare the row with the probe result and the Ledger read endpoint. The Foundations acceptance is one matching Ledger entry and a 1,000-cent balance for a fresh fixture account. Accounts may still report pending at the inspected baseline. Do not “fix” that by manually editing the database. Record a failed acceptance as a failed observation. Frontend mock values and process-health endpoints are not substitutes.

4. Write the database operating plan

Extend your existing MicroBank design record with:

• Schema/migration ownership for each service, actual constraints, and timestamp/amount types.
• Measured pool behavior and a connection budget including rollout overlap and consumers.
• One important query, its inspected indexes, and a plan captured on appropriate local test data.
• One compatible schema-change proposal, bounded lock behavior, and application rollback limits.
• Separate backup/restore procedures for each database, role grants, message reconciliation, and a test of an existing transaction after restore.

The pinned Ledger configuration uses ddl-auto: create-drop. The study Compose and Kubernetes labs override it with SPRING_JPA_HIBERNATE_DDL_AUTO=update. Check the effective setting: neither automatic mode replaces reviewed, versioned migrations for real releases. Introducing a migration framework and running a distributed MicroBank restore are follow-on implementation tasks; this module does not perform them. Pinned configuration↗.

Handoff

Finish with your SQL fixture restore evidence, actual MicroBank schema observations, probe result, and open data risks. Stop the Compose profile while retaining needed volumes before Architecture and kind.

Kubernetes storage, readiness, deployment surge, and recovery decisions should now refer to this evidence. A PVC is not a backup; increasing database replicas does not create safe PostgreSQL replication. Reuse the existing Application and data diagnosis interview round to practise explaining the findings.

Compare source and runtime carefully

Your final table should have three columns: rule declared in source, rule observed in the database, and user behavior verified by the probe. A declared model constraint, an actual unique index, and a sequential retry result are related evidence with different limits. If the columns disagree, inspect initialization/migration history before modifying anything.

The handoff is ready when you can locate the accepted transaction's Ledger entry, explain the amount/kind interpretation, identify connection and migration owners, and name the restore work still missing. Kubernetes will change packaging and process placement; it will not add the missing data guarantees automatically.