Confidential energy-sector registry architecture
Ledger integrity, historical migration strategy, and client-owned AWS architecture for a regulated certificate registry.
Lead architect on a regulated energy-sector certificate registry. Designed and built the replacement for a managed SaaS ledger platform with a client-owned PostgreSQL system. Five database-owned write functions hold the entire authoritative mutation surface. Append-only canonical ledger with a blockchain-style UTXO model and double-entry credit/debit postings, validated by a comprehensive integration test suite against real PostgreSQL.
Key Results
Authoritative ledger surface
5 stored functions covering the entire authoritative write surface
Integration test coverage
100+ integration tests across 8 suites, all against real PostgreSQL
Engagement
Lead Architect, 5 months (Oct 2025 to Feb 2026)
Where this engagement starts
The problem
A regulated energy-sector registry tracks certificates that represent units of energy. The certificates are not currency, but they are treated as financial assets: minted by audited facilities, transferred between organisation accounts, retired against claims, and bound by conservation rules that a regulator will check years after the fact.
The existing platform is a managed SaaS ledger product. The mandate of the engagement was to specify and build a replacement that the client owns end to end, with the authoritative write surface inside their PostgreSQL database, integration tests against the real database instead of mocks, and a sovereign AWS target architecture suitable for a national-scale regulated workload.
The system has to be auditable years after a transaction settles, tolerate concurrent operations across global organisations without deadlocks or phantom double-spends, and refuse to silently produce a mathematically inconsistent ledger row under any failure mode.
Engineering memo
Key decisions
What was decided, why, and what was deliberately not done.
Constrain the entire ledger write surface to five database-owned mutation functions, one per business event.
Rationale
The registry exposes exactly five operations on certificates: minting, transfer, retirement, regional re-assignment, and expiry. Each is implemented as a single SECURITY DEFINER PostgreSQL function. Those five functions are the entire authoritative write surface for the ledger.
An auditor can read every line of mutation logic in a single sitting. Application code, browser sessions, and other database roles cannot mutate ledger state through any other path. The privilege model enforces it: EXECUTE is REVOKEd from PUBLIC, anon, and authenticated. Mutations enter only via service-bound API paths that call the functions.
Alternative considered
A wider catalog of helper functions would have spread invariant logic across too big of a surface, negatively impacting auditability.
Use a blockchain-style UTXO consumption model on certificate blocks, with parent-child lineage recorded in append-only canonical tables.
Rationale
Every mutation consumes one or more parent blocks and produces child blocks. The link from each child back to its parents is recorded in a dedicated input-tracking table at the moment of the mutation. Triggers enforce the split invariants at commit time: child volumes must sum to parent volumes, child ranges must be contiguous and non-overlapping, and a consumed parent must not remain queryable as an unspent block.
A materialized derived table tracks the current set of unspent blocks for O(1) read access. Materialized account balances give the same property to the per-account total. Both are rebuildable from the canonical append-only tables by replaying the credits_debits ledger from genesis.
Alternative considered
An update-in-place balance model trades implementation simplicity for unacceptable auditing complexity. Reconstructing 'who held this certificate on date X' becomes an after-the-fact reporting problem instead of a property of the database.
Pair every mutation with double-entry credit and debit postings on a dedicated credits_debits table, validated by a deferred commit-time trigger.
Rationale
Every event writes paired credit and debit rows that sum to zero across the transaction. A private trigger verifies the sum at the commit boundary and aborts if the postings do not balance. The system layers over a hundred CHECK and UNIQUE constraints, dozens of trigger statements, and the five mutation functions to enforce volume positivity, range continuity, exactly-one-side posting, parent-child split integrity, and idempotency.
The database itself refuses to commit a mathematically inconsistent ledger row under any failure mode. Application bugs, deployment ordering issues, race conditions, and compromised application containers all collide with this gate: the transaction either honors every invariant or it rolls back.
Alternative considered
Application-side balance checks would have moved the invariant into business logic, where it competes with race conditions, deployment ordering, and developer attention. This is not the correct posturing for a regulated registry.
Plan the migration as a replay from time zero — chronological re-emission of every historical event into the new system's mutation functions, validated by the new invariants on every event.
Rationale
The existing managed SaaS ledger never enforced the invariants the new system exists to enforce. A naive copy would import historical inconsistencies and break the new system's commit-time guarantees. A replay engine reads every historical event in chronological order, calls the corresponding mutation function, and either accepts (invariants hold) or raises a named error code (invariants fail). Every rejection becomes a triage ticket and an audit-grade write-up of the source-side fix.
The plan iterates against a clean clone until a complete replay from time zero runs with zero rejections. Production cutover is then one final replay run against the canonical source, immediately followed by the reconciliation function as the post-cutover health check. The architectural decisions on this page — five mutation functions, double-entry posting, deterministic locking, reconciliation gate — exist in service of this migration, not the other way around.
Alternative considered
A bulk SQL load would populate tables with data. It would also import every silent inconsistency the old system tolerated, and the new system's first commit-time trigger would refuse to validate.
Outcomes
Outcomes (with sources)
Real metrics with attribution. Numbers without sources read as marketing. Every claim here is sourced.
Metric
Authoritative ledger write surface
Before
Existing platform: a managed SaaS ledger product. Invariants enforced inside the vendor's product, not under client control.
After
Five SECURITY DEFINER PostgreSQL functions own every state transition in the new system, fully owned and operable by the client at production cutover.
Source: Engagement deliverables: mint, transfer, retire, regional adjustment, and expiry stored functions
Metric
Production drift detection
Before
Drift detection on the SaaS platform required pulling exports and reconciling externally.
After
Read-only reconciliation function returns JSON describing any double-spend, balance drift, asset-conservation, or posting-imbalance violation. Lock-free, safe to run in production as a periodic health check.
Source: Engagement deliverables: reconciliation function (4 orthogonal drift checks, lock-free)
Metric
Production cloud topology
Before
Vendor-hosted SaaS with no client control over data residency, key custody, or incident-response authority.
After
Approved client-owned AWS Organization design: separate accounts for control plane, ledger workload, and audit / logging. GitHub OIDC to AWS in place of long-lived access keys. Approved as the production target architecture.
Source: Engagement deliverables: sovereign AWS target architecture and Terraform foundation
Metric
Migration strategy and cutover plan
Before
Existing managed SaaS ledger held all historical state. The new client-owned PostgreSQL system enforces stricter invariants. Direct copy would import silent inconsistencies and refuse to commit on the first transaction.
After
Replay-engine architecture, phased migration plan, rejection-triage workflow, cutover criteria, and rollback strategy designed and documented end to end. Implementation was scheduled to begin immediately after my engagement ended; the groundwork was complete and the architecture decisions on this page exist in service of the replay-from-time-zero plan.
Source: Architecture memo: data migration and cutover plan
Confidential: Energy-Sector Certificate Registry
The Role: Lead Architect (October 2025 to February 2026).
The Engagement: Design and build the authoritative replacement for a managed ledger platform: a sovereign, client-owned PostgreSQL system suitable for a regulated, global-scale energy-sector certificate registry.
Summary
I led the technical architecture for a regulated energy-sector certificate registry that required financial-ledger integrity, strict auditability, and production-grade security controls. The objective was to replace an existing managed SaaS ledger product with a system the client owns end to end, with the authoritative write surface inside their PostgreSQL database, integration tests against the real database instead of mocks, and a sovereign AWS target architecture suitable for a global-scale regulated workload.
I delivered a blockchain-style UTXO double-entry PostgreSQL ledger with deterministic concurrency behavior, database-enforced invariants, and reconciliation gates that will run as periodic production health checks after cutover. In parallel, I designed and presented a Sovereign Cloud Architecture memo that established a path toward client-owned AWS identity, storage, compute, data, and observability.
The production design direction was approved.
Every write to the registry flows through five SECURITY DEFINER PostgreSQL functions. EXECUTE is revoked from PUBLIC, anon, and authenticated roles; only the service role can invoke them. Direct writes to the canonical tables are blocked by the same privilege grant.
Architectural Scope & Deliverables
- Financial-grade ledger architecture and implementation in PostgreSQL.
- Deterministic write-path contracts for the authoritative ledger functions.
- Concurrency hardening and deadlock-resistant lock ordering.
- Security boundary enforcement at function privilege and role-grant level.
- Sovereign AWS target architecture across identity, storage, compute, data, and observability.
- Migration strategy, cutover controls, rollback criteria, and risk register for production adoption.
- Integration test suite that exercises every mutation function against a live PostgreSQL instance, including the concurrency model under contention.
- Read-only reconciliation function for production drift detection.
- Audit-grade documentation suite covering system architecture, function contracts, security and locking model, error code taxonomy, testing and operations, and remediation history.
The Certificate Model
Certificates represent units of energy issued by audited facilities. They behave like discrete, individually-identifiable assets: minted in blocks, transferred between organisation accounts, split where ownership changes partially, retired against claims, and re-assigned regionally where the registry's policy supports it. The data model records each minted batch as a contiguous block of consecutively-numbered certificates so the ledger stays performant at registry scale without losing per-certificate identity.
Operations work on whole or partial blocks. A partial transfer consumes the parent block and produces child blocks: a transferred portion in the destination account and a remainder back in the source. Provenance is a chain of consumptions and productions, durable in the database, traceable years later.
Why the model matters:
- Per-certificate audit posture. Reconstructing 'who held this certificate on date X' is straightforward, as it is a property of the database.
- Performant queries. Blocks compress the per-certificate space into per-batch rows without losing the per-certificate identity.
- Append-only canonical lineage. A consumed block is never mutated. New child blocks reference their parent block in a dedicated input-tracking table at the moment of the mutation.
Ledger System Delivered
Worked example of UTXO-style certificate block lineage: mint 100 units to account A, then transfer 30 units from A to B. The transfer consumes parent block 1 and produces two child blocks (block 2 in B with 30 units, block 3 in A with 70 units); commit-time triggers enforce volume conservation, range continuity, and the spent-input invariant.
Core Ledger Model
- Append-only canonical records for business events and lineage (transactions, transaction items, transaction item inputs, credits and debits, assets).
- Blockchain-style UTXO certificate-block consumption via leaves and inputs. A consumed parent block produces child blocks; the link is recorded in the input-tracking table.
- Double-entry credit and debit postings per transaction. A deferred trigger validates the transaction-level sum at the commit boundary.
- Materialized derived state for operational performance: current unspent leaves and per-account balances. Both are rebuildable from the canonical append-only tables by replaying the credits and debits ledger from genesis.
Authoritative Mutation Boundary
All ledger mutations run through five SECURITY DEFINER PostgreSQL functions. That is the entire authoritative write surface for the ledger.
These functions enforce:
- Request-state and actor validation. Each request must be in the right status and approved by the right actor before any write occurs.
- Account-type and commodity constraints. Source and destination account types are checked against the operation; commodity rules govern issuance, transfer, retirement, and regional re-assignment.
- Range and volume integrity. Every block has a positive volume, a valid contiguous range, and a child-volume sum that equals its parent on split.
- Balanced postings at the transaction level. Credits and debits sum to zero across the transaction. The deferred trigger aborts the commit if they do not.
- Atomic commit boundaries with rollback on exception. A failed mutation rolls back every write inside the transaction.
- Read-only reconciliation checks as integrity gates. A separate function provides drift detection across the ledger as a periodic production health check.
Database-Level Guardrails
The integrity story is not just five functions. The schema layer carries over a hundred CHECK and UNIQUE constraints and dozens of trigger statements that enforce volume positivity, range continuity, exactly-one-side double-entry posting, parent-child split integrity, and idempotency. The database itself refuses to commit a mathematically inconsistent transaction under any failure mode.
Application bugs, deployment ordering issues, race conditions, and compromised application containers all collide with the same wall: the transaction either honors every invariant or it rolls back. There is no path through the application tier that produces an unbalanced ledger row.
Double-entry posting and commit-time validation: every mutation function writes paired credit and debit rows on credits_debits that sum to zero per (transaction_id, asset). A deferred constraint trigger fires at commit, computes SUM(volume) per (txn, asset), and either passes (transaction commits) or raises POSTING_IMBALANCE (transaction rolls back).
Security and Concurrency Controls
- SECURITY DEFINER routines with execute revoked from PUBLIC, anon, and authenticated.
- Execution grants constrained to trusted service and database roles. Mutations enter only via service-bound API paths that call the functions.
- Deterministic UUID-ordered row locking helpers for accounts and balances. Concurrent transactions touching the same two accounts in opposite order serialize correctly instead of deadlocking.
- Commodity-scoped expiry locking with post-lock requery behavior. Expiry runs against a fresh snapshot of trading leaves under the lock.
- Block-consumption locked in window-function-ordered batches (smallest volume first) for predictable split behavior under contention.
- Domain-specific error contracts: an extensive named error-code taxonomy across the mutation functions and supporting helpers, organized into a documented taxonomy. Every reachable failure path is diagnosable from the code alone.
Confidential Client Project - Security & Compliance Analysis Diagram
![Deterministic locking eliminates cross-transaction deadlocks: parallel transfers A→B and B→A both call the lock helper which sorts UUIDs to identical [A, B] order; T1 acquires the locks first, T2 queues; T1 commits and releases; T2 then runs and either commits or rolls back with INSUFFICIENT_TOTAL_BALANCE. Concurrency suite asserts: two parallel 80-unit transfers from a 100-unit source serialize correctly with no phantom double-spend.](https://media.petertconti.com/media/04-deterministic-locking-2048x2777.webp)
Deterministic locking eliminates cross-transaction deadlocks: parallel transfers A→B and B→A both call the lock helper which sorts UUIDs to identical [A, B] order; T1 acquires the locks first, T2 queues; T1 commits and releases; T2 then runs and either commits or rolls back with INSUFFICIENT_TOTAL_BALANCE. Concurrency suite asserts: two parallel 80-unit transfers from a 100-unit source serialize correctly with no phantom double-spend.
Test Coverage Against the Live Database
Mocking the database is the wrong tradeoff for a ledger. The whole point of putting invariants inside PostgreSQL is that the database enforces them. A mock that shortcuts the database also shortcuts the safety properties.
The integration suite spins up real PostgreSQL containers, runs single-worker to avoid pool exhaustion, and asserts on actual rows produced. Eight suites cover minting, transfer, retirement, regional adjustment, expiry, concurrency, and the read-path views. 100+ integration tests, all green.
Sample concurrency assertions:
- Two parallel 80-unit transfers from a 100-unit source serialize correctly. One succeeds, one fails with an explicit INSUFFICIENT_TOTAL_BALANCE error. Final state verified: 20 units remain in source, 80 in destination.
- Two parallel mint requests on the same commodity allocate non-overlapping certificate number ranges, with end-of-run verification that ranges are contiguous and gap-free.
- Concurrent expiry and transfer touching the same trading account serialize via commodity-scoped locking; expiry re-queries leaves after lock acquisition to catch race changes.
Reconciliation as a Production Health Check
A single read-only function runs four orthogonal drift checks and returns a JSON report of any violations. The function is lock-free and safe to run in production as a periodic check.
- Spent-input invariant: verifies that no consumed parent block remains in the unspent set. Catches phantom double-spend.
- Balance drift: compares stored account balances against the computed sum of credits minus debits. Catches divergence between materialized derived state and the canonical ledger.
- Asset conservation: verifies that the sum of unspent block volumes equals the originally-minted volume per asset. Catches certificate leaks or duplication.
- Posting balance: verifies per-transaction credits equal debits. Belt-and-braces backstop on the deferred commit trigger.
Operations get a clear signal the day a divergence appears, instead of discovering it during the next audit cycle.
Read-only reconciliation function fired on a 5-minute CloudWatch schedule (and on demand from the operations CLI). Four orthogonal drift checks (spent-input, balance, asset-conservation, posting-balance) emit a JSON report; passing runs post a CloudWatch metric, failing runs page on-call.
Data Migration: Replay Engine from Time Zero
The new client-owned PostgreSQL system enforces invariants the existing managed SaaS ledger never had to. A naive copy of historical state would import inconsistencies the old system silently allowed and break the new system's commit-time guarantees on the first transaction. The replacement strategy could not be a one-shot ETL. It had to be a replay.
The replay engine. Read every historical event from the source ledger in chronological order. For each event, call the corresponding mutation function on the new system with the original actor, timestamp, and payload. The new system either accepts the event (because the invariants hold) or raises a named error code (because they do not). Any rejection is a real historical inconsistency that needs a human decision: data correction in the source, a documented variance, or a temporary escape valve in the replay map. There is no path forward that silently masks a divergence.
Phased migration plan. Run the replay against a clean clone first. Surface every rejected event into a triage queue. Resolve each rejection with an audit-grade write-up of the source-side fix or the variance accepted. Re-run the full replay end to end. Iterate until a complete replay from time zero produces a clean run with zero rejections. Only then is the system ready for production cutover.
Cutover and steady state. Production cutover becomes one final replay run against the canonical source as of a freeze timestamp, immediately followed by the reconciliation function described above as the post-cutover health check. The same reconciliation runs on a 5-minute schedule from then on; any divergence between the new ledger and the source-of-truth audit log pages on-call within minutes.
My role. I designed the phased migration plan, the rejection-triage workflow, the cutover criteria, and the rollback strategy. Five database-owned mutation functions, double-entry posting, deterministic locking, and the reconciliation gate all exist because the migration has to land without a single silent inconsistency.
Sovereign Cloud Target Architecture
To achieve financial-grade compliance and long-term operational control, I designed a full decoupling strategy across all critical planes. The target is a client-owned production environment with no vendor in the trust path of any sensitive operation.
- Identity plane: client-owned Cognito as the authoritative identity provider.
- Storage plane: client-owned S3 with KMS encryption, deny-by-default bucket policies, TLS-only access, and key rotation enabled.
- Compute plane: private-subnet ECS services behind controlled ingress, with horizontal scaling driven by CloudWatch metrics.
- Data plane: isolated RDS PostgreSQL with proxy-mediated access and account-scoped network boundaries.
- Trust plane: OIDC-based CI role assumption with no static cloud keys. Every deploy runs as a short-lived assumed role scoped to the operations the deploy actually needs.
- Governance plane: Terraform-managed environments with auditable drift control, rollback runbooks, and a separate audit account for CloudTrail, Config, and GuardDuty.
Diagram 1: Sovereign Target AWS Topology
Confidential Client Project - Production AWS toplogy mermaid Diagram
Diagram 2: CI/CD Access Pattern
Confidential Client Project - CICD Access Pattern Mermaid Diagram
Documentation as a Deliverable
Audit-grade documentation is itself a deliverable on a regulated workload. The engagement produced a set of canonical documents covering system architecture (data model, invariants, function design), authoritative function contracts (signatures, preconditions, effects), security and locking model (privilege grants, deterministic ordering, deadlock avoidance), error code taxonomy (a structured named error-code taxonomy organized by domain), testing and operations (suite structure, reconciliation usage, expiry operational notes), and remediation history (post-audit corrections and hardening passes).
The documentation is production-ready audit material. It captures the design intent, the invariants the system depends on, the failure modes operations needs to recognize, and the remediation history that explains why specific tightening passes were applied. A new engineer or external auditor can read it end to end and understand both how the system works today and why it works that way.
Outcomes
- Delivered a financial-grade ledger core with deterministic, test-backed behavior. 100+ integration tests, 100% green, all running against real PostgreSQL containers.
- Designed the authoritative write surface that will replace the SaaS ledger product on cutover. Five SECURITY DEFINER PostgreSQL functions own every state transition; the database itself enforces invariants the application tier cannot circumvent.
- Raised platform posture to sovereign-cloud design standards. Approved client-owned AWS Organization design with separate accounts for control plane, ledger workload, and audit/logging.
- Established enforceable least-privilege and identity trust boundaries. EXECUTE revoked from browser-facing roles; OIDC-based CI replaces long-lived access keys.
- Created an executable production blueprint that aligns engineering speed with compliance-grade control. Migration strategy includes rehearsals, production-clone validation, idempotent scripts, and explicit business rules for ambiguous records.
This engagement demonstrates strength in:
- Transactional systems engineering (ledger correctness, concurrency, invariants, double-entry posting, deterministic locking).
- Enterprise cloud architecture (sovereignty, zero trust, IAM discipline, infrastructure governance).
- Executive technical decision-making (risk framing, migration design, operational control strategy, audit-grade documentation).
If your platform sits where this one did
Regulated workloads, ledger-grade integrity, sovereign cloud requirements — the kind of work where the database has to hold the line and the audit trail has to survive the next decade. Thirty minutes is enough to know whether the practice is a fit.