Data Journey: Borrower Name | PorchPass Medallion Architecture

📋

DigiFi LOS

Origin

→

🔌

Webhook

REST API

→

📮

Pub/Sub

Queue

→

⚙️

Consumer

Go Worker

→

🥉

GCS Bronze

Raw JSON

→

🥈

PostgreSQL

borrowers

→

🔄

Airflow

ETL DAG

→

🥇

BigQuery

Gold Views

→

📊

Superset

Dashboards

🎯

ORIGIN: External System

Data point creation in source system

📋

DigiFi LOS

Loan Origination System

Dealer employee enters borrower information into the loan application form. The first name "Jane" is captured in the DigiFi borrower object.

Data Captured

borrower.firstName: "Jane"
borrower.lastName: "Smith"
applicationId: "app_abc123"

🔌

Webhook POST

application.created event

DigiFi fires a webhook to PorchPass API when the application is created. The webhook contains the full application payload including borrower data.

Webhook Payload

POST /webhooks/digifi
Content-Type: application/json
{
  "event": "application.created",
  "data": {
    "borrower": {
      "firstName": "Jane",
      "lastName": "Smith"
    }
  }
}

📮

Cloud Pub/Sub

Message Queue

The REST API immediately pushes the webhook payload to a Pub/Sub queue. This ensures reliability - if processing fails, the message can be retried.

Queue Message

Topic: digifi-events
Message: Full webhook payload
Attributes: {event_type: "application.created"}

🥉

BRONZE LAYER: Raw Data

Unmolested source data preserved for audit trail

⚙️

Go Consumer Worker

Long-running Kubernetes pod

A Go consumer service pulls messages from Pub/Sub. First action: archive the raw JSON to GCS Bronze bucket before any processing.

Bronze Archive

gs://porchpass-bronze/webhooks/digifi/
2026/01/28/event_abc123.json

📦 GCS: webhooks/digifi/

🥈

SILVER LAYER: Cleansed & Conformed

Validated, transformed, and PII-encrypted data

🐘

PostgreSQL INSERT

borrowers table

Consumer parses the webhook JSON, encrypts PII fields using AES-256, and inserts into the borrowers table.

Data Transformation

INPUT: "firstName": "Jane"

↓ AES-256 Encryption

OUTPUT: pii_first_name: 0x8A3F...C721 (bytea)

🗄️ borrowers pii_first_name pii_last_name 🔐 PII ENCRYPTED

🔗

Related Tables

Foreign key relationships

The borrower record links to the application via application_id FK. When the loan funds, a loan_borrowers junction record is created.

Table Relationships

applications.id → borrowers.application_id
borrowers.id → loan_borrowers.borrower_id
loans.id → loan_borrowers.loan_id

🗄️ applications 🗄️ loan_borrowers 🗄️ loans

📖

Read Replica

Cloud SQL read replica

Data replicates to the read replica within seconds. BI tools and Airflow read from the replica to protect primary database performance.

Replication lag: < 1 second

🥇

GOLD LAYER: Business-Ready

Aggregated, denormalized data optimized for analytics

🔄

Airflow ETL

postgres_to_bigquery_sync DAG

Daily at 7:00 AM, Airflow extracts borrower data from Postgres read replica, decrypts PII (within secure boundary), and loads to BigQuery.

ETL Process

PostgreSQL: pii_first_name (encrypted bytea)

↓ Decrypt + Transform

BigQuery: first_name: "Jane" (STRING)

📊

BigQuery

gold.dim_borrowers view

Borrower name lands in BigQuery Gold layer, joined with loan data to create denormalized views for reporting.

Gold View: dim_loan_performance

SELECT
  l.loan_number,
  b.first_name || ' ' || b.last_name AS borrower_name,
  l.current_balance,
  l.delinquency_bucket
FROM gold.loans l
JOIN gold.borrowers b ON ...

📊 gold.borrowers 📊 gold.dim_loan_performance 📊 gold.investor_loan_tape

📈

Superset Dashboards

Business Intelligence

Business users access borrower data through Superset dashboards. The borrower name appears in loan performance reports, investor tapes, and operational dashboards.

Dashboard Usage

• Loan Portfolio Dashboard
• Delinquency Report (borrower name + status)
• Investor Loan Tape Export
• Dealer Performance Scorecard

🗃️ Tables Touched

borrowers Primary record (Silver)
applications Parent FK (Silver)
loan_borrowers Junction table (Silver)
gold.borrowers Denormalized (Gold)
gold.dim_loan_performance View (Gold)
gold.investor_loan_tape Export view (Gold)

🔄 Transformations Applied

🔐 AES-256 encryption (Bronze → Silver)
✅ Schema validation
🔗 FK relationship mapping
🔓 Decryption for BI (Silver → Gold)
📊 Denormalization for reporting

⏱️ Data Freshness

⚡ Origin → Bronze: Real-time (seconds)
⚡ Bronze → Silver: Real-time (seconds)
📖 Silver → Read Replica: ~1 second
🔄 Silver → Gold: Daily (7:00 AM)
📊 Gold → Superset: On-demand

Borrower Name Lifecycle

🗃️ Tables Touched

🔄 Transformations Applied

⏱️ Data Freshness