Skip to content

Audit Trail

Automatic change tracking via PostgreSQL triggers, event-sourced loan replay, and immutable ledger entries.

django-pghistory

The system uses django-pghistory for automatic row-level change tracking via PostgreSQL triggers.

How It Works

  1. Models decorated with @pghistory.track() get auto-generated *Event tables
  2. PostgreSQL triggers (via pgtrigger) fire on INSERT and UPDATE
  3. Each trigger captures a full row snapshot with metadata (timestamp, operation type)
  4. Triggers run at the database level — changes are captured regardless of how they happen (ORM, raw SQL, migrations)

Tracked Models

App Model Event Table
borrowers Borrower borrowers_borrowerevent
loans Loan loans_loanevent
ledger JournalEntry ledger_journalentryevent
payments Payment payments_paymentevent
fees Fee fees_feeevent
providers ProviderConfig providers_providerconfigevent
cases Case cases_caseevent
compliance ComplianceCheck compliance_compliancecheckevent
programs LendingProgram programs_lendingprogramevent
collateral CollateralItem collateral_collateralitemevent
portfolio Portfolio portfolio_portfolioevent
contacts Address, EmailContact, PhoneContact Respective event tables

Usage

import pghistory

@pghistory.track()
class Loan(BaseModel):
    ...

The default trackers capture both InsertEvent and UpdateEvent with full row snapshots.

Querying Audit History

Event tables can be queried directly via the ORM:

# Get all changes to a specific loan
events = LoanEvent.objects.filter(pgh_obj_id=loan.id).order_by("pgh_created_at")

The admin dashboard exposes audit history via NestedAuditHistory components on resource detail views.

Event-Sourced Loan Replay

The apps/operations/ module (also called replay) provides event-sourced loan replay:

  • LoanEvent model stores structured business events (disbursement, payment, modification, etc.)
  • Events can be replayed to reconstruct loan state at any point in time
  • Used for reconciliation, debugging, and "what-if" analysis

Immutable Ledger Entries

The general ledger enforces immutability on posted entries:

State Mutable? Behavior
Draft (posted = False) Yes Can be modified before posting
Posted (posted = True) No Immutable — corrections only via reversing entries

Invariants

  • Every JournalEntry must satisfy SUM(debits) = SUM(credits)
  • Account balances are derived from journal lines, never stored directly
  • Duplicate source entries prevented by UniqueConstraint(fields=["source_type", "source_id"])

Communication Logging

Every communication sent through the system is logged:

  • CommunicationLog records channel, recipient, template, status, and timestamps
  • Failed deliveries include error details
  • Logs are queryable per borrower, per loan, and per template

Webhook Delivery Logging

Webhook deliveries are tracked with full request/response details:

  • Each delivery attempt is logged with status code, response body, and latency
  • Failed deliveries trigger retries (up to 3 attempts with exponential backoff)
  • 5 consecutive failures mark a subscription as dead-lettered

See Also