Bank Sync API

A FastAPI service for securely ingesting bank transaction webhooks, verifying signatures, enforcing idempotency, and running reconciliation workflows.

Context

This project is a lightweight showcase extracted from a much larger internal platform built at Briclinks Africa Plc (BTEL), a telecom MVNO operator using Airtel Nigeria infrastructure.

The full production system supports telecom billing, financial integrations, employee operations, and customer management. Due to NDA and client confidentiality agreements, only a subset of the features are shared here.

Features

• Webhook Processing: Accepts bank transaction webhooks with HMAC signature verification
• Idempotency: Guaranteed via database-level unique constraints on event IDs
• Reconciliation: Matches transactions to invoices by reference
• Database: Uses SQLAlchemy 2.0 ORM with Alembic migrations
• Testing: Comprehensive test suite with Pytest

Prerequisites

• Python 3.11+
• PostgreSQL (via local Supabase Docker stack)
• Virtual environment (venv)

Setup

1. Create Virtual Environment

python -m venv venv

On Windows:

venv\Scripts\activate

On macOS/Linux:

source venv/bin/activate

2. Install Dependencies

pip install -e ".[dev]"

3. Configure Environment

Copy .env.example to .env:

cp .env.example .env

Edit .env and set your values:

DATABASE_URL=postgresql+psycopg2://postgres:postgres@localhost:54322/postgres
WEBHOOK_SECRET=your-secret-key-change-this-in-production

4. Run Database Migrations

alembic upgrade head

5. Seed Demo Invoices (Optional)

You can seed demo invoices using the following SQL (run in your PostgreSQL client):

INSERT INTO invoices (id, invoice_number, amount_due, currency, status)
VALUES
    (gen_random_uuid(), 'INV-001', 5000.00, 'NGN', 'pending'),
    (gen_random_uuid(), 'INV-002', 10000.00, 'NGN', 'pending'),
    (gen_random_uuid(), 'INV-003', 7500.00, 'NGN', 'paid')
ON CONFLICT (invoice_number) DO NOTHING;

Or use the provided Python script:

python scripts/seed_invoices.py

6. Start the Server

Important: Make sure you've run the database migrations (step 4) before starting the server.

uvicorn app.main:app --reload

The API will be available at http://localhost:8000

The application will automatically check database connectivity and table existence on startup. If tables are missing, you'll see a clear error message directing you to run migrations.

Quick Demo

1. Start Supabase locally:

   npx supabase start

2. Run migrations:

   alembic upgrade head

3. Seed invoices:

   python scripts/seed_invoices.py

4. Start API:

   uvicorn app.main:app --reload

5. Send webhook (generate signature first - see "Generating Webhook Signatures" section):

   curl.exe -X POST http://127.0.0.1:8000/webhooks/bank/transactions \
     -H "Content-Type: application/json" \
     -H "X-Event-Id: evt_001" \
     -H "X-Signature: sha256=<your-signature>" \
     -d '{"transaction_id":"txn_001","amount":5000,"currency":"NGN","occurred_at":"2026-02-27T12:00:00Z","reference":"INV-001","description":"Transfer"}'

6. Run reconciliation:

   curl.exe -X POST http://127.0.0.1:8000/reconcile/run

API Endpoints

Health Check

GET /health

Returns: { "ok": true }

Webhook: Receive Bank Transaction

POST /webhooks/bank/transactions

Headers:

• X-Event-Id: Unique event identifier (required)
• X-Signature: HMAC SHA256 signature (required)

Body:

{
  "transaction_id": "txn_001",
  "amount": 5000,
  "currency": "NGN",
  "occurred_at": "2026-02-27T12:00:00Z",
  "reference": "INV-001",
  "description": "Transfer"
}

Response:

{
  "ok": true,
  "event_id": "evt_001",
  "status": "stored",
  "deduped": false
}

Run Reconciliation

POST /reconcile/run

Matches transactions to invoices by reference and returns summary.

Response:

{
  "id": "uuid",
  "started_at": "2026-02-27T12:00:00Z",
  "finished_at": "2026-02-27T12:00:01Z",
  "matched_count": 1,
  "unmatched_count": 0,
  "matched_transaction_ids": ["uuid"],
  "unmatched_transaction_ids": []
}

Get Reconciliation Run

GET /reconcile/runs/{run_id}

Returns details of a specific reconciliation run.

Generating Webhook Signatures

To generate a valid signature for testing, use this Python snippet:

import hmac
import hashlib
import json

def generate_signature(body: dict, secret: str) -> str:
    body_bytes = json.dumps(body).encode("utf-8")
    signature = hmac.new(
        secret.encode("utf-8"),
        body_bytes,
        hashlib.sha256
    ).hexdigest()
    return f"sha256={signature}"

# Example usage
payload = {
    "transaction_id": "txn_001",
    "amount": 5000,
    "currency": "NGN",
    "occurred_at": "2026-02-27T12:00:00Z",
    "reference": "INV-001",
    "description": "Transfer"
}

secret = "your-webhook-secret"
signature = generate_signature(payload, secret)
print(f"X-Signature: {signature}")

Testing

Run the test suite:

pytest

Run with verbose output:

pytest -v

Run specific test file:

pytest tests/test_webhooks.py
pytest tests/test_reconciliation.py

Screenshots

Database Schema

The complete database schema showing all tables and their relationships:

Database schema diagram showing the relationships between raw_events, transactions, invoices, reconciliation_runs, and transaction_matches tables.

API Documentation

Interactive API documentation available at /docs endpoint:

FastAPI's automatic interactive API documentation showing the webhook endpoint with required headers and parameters.

Webhook Testing

Example webhook requests showing idempotency in action:

Terminal output demonstrating webhook idempotency - first request returns deduped: false, second identical request returns deduped: true.

Architecture

See ARCHITECTURE.md for system design including:

• Bank webhook flow
• Idempotency layer
• Reconciliation pipeline
• Database schema

Database Schema

Tables

• raw_events: Stores raw webhook payloads with event IDs for idempotency
• transactions: Normalized bank transaction records
• invoices: Invoice records for reconciliation
• reconciliation_runs: Tracks reconciliation execution
• transaction_matches: Stores transaction-to-invoice matching results

Development

Makefile Commands (Optional)

If you have make installed, you can use:

.PHONY: dev test db-migrate

dev:
	uvicorn app.main:app --reload

test:
	pytest -v

db-migrate:
	alembic upgrade head

db-migrate-create:
	alembic revision --autogenerate -m "$(msg)"

Creating Migrations

alembic revision --autogenerate -m "Description of changes"
alembic upgrade head

Deployment

This service can be deployed on:

• Render: Simple PostgreSQL + FastAPI deployment
• Railway: One-click PostgreSQL and app deployment
• AWS ECS: Containerized deployment with RDS
• Docker Compose: Local or production container orchestration

Requirements:

• PostgreSQL database (version 12+)
• Environment variables configured (see .env.example)
• Webhook secret for HMAC verification
• Python 3.11+ runtime

Example Docker Compose:

version: '3.8'
services:
  api:
    build: .
    ports:
      - "8000:8000"
    environment:
      - DATABASE_URL=postgresql://user:pass@db:5432/dbname
      - WEBHOOK_SECRET=${WEBHOOK_SECRET}
    depends_on:
      - db
  db:
    image: postgres:15
    environment:
      - POSTGRES_DB=bank_sync
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres

Example Use Cases

• MVNO billing reconciliation: Match customer payments to telecom invoices
• Bank statement ingestion: Process daily bank transaction feeds
• Payment confirmation matching: Automatically link payments to outstanding invoices
• Automated telecom invoice settlement: Reconcile operator invoices with bank transfers
• Financial integrations with banking APIs: Secure webhook processing for banking partners

Security

• Webhook signatures are verified using HMAC SHA256 with constant-time comparison
• Idempotency is enforced at the database level using UNIQUE constraints
• Environment variables are loaded from .env (never commit .env to version control)

Troubleshooting

Database Tables Don't Exist

Error: UndefinedTable: relation "raw_events" does not exist or similar

Solution:

1. Run the database migrations:

   alembic upgrade head

2. Verify tables were created:

   alembic current

3. Restart the server

The application includes startup checks that will warn you if tables are missing. If you see a startup error about missing tables, run alembic upgrade head and restart the server.

Database Connection Errors

Error: Failed to connect to database or connection timeout

Solution:

1. Verify your Supabase Docker stack is running:

   # If using Supabase CLI
   supabase status

2. Check your .env file has the correct DATABASE_URL
3. Verify the database is accessible:

   # Test connection (replace with your actual connection string)
   psql postgresql://postgres:postgres@localhost:54322/postgres -c "SELECT 1"

Migration Errors

Error: Target database is not up to date or migration conflicts

Solution:

1. Check current migration status:

   alembic current
   alembic history

2. If migrations are out of sync, you may need to:
   • Review the migration history
   • Manually resolve conflicts
   • Or reset and reapply (⚠️ WARNING: This will delete all data):

     alembic downgrade base
     alembic upgrade head

Error: Migration is marked as applied but tables don't exist

Symptoms: alembic current shows a version, but startup check reports missing tables.

Solution: This happens when a migration was stamped without actually running. Fix it by:

# Downgrade to base (removes the stamp, safe if tables don't exist)
alembic downgrade base

# Then upgrade to actually create the tables
alembic upgrade head

You should see output like:

INFO  [alembic.runtime.migration] Running upgrade  -> 001_initial, Initial migration

Webhook Signature Verification Fails

Error: 401 Invalid signature

Solution:

1. Verify your WEBHOOK_SECRET in .env matches the secret used to generate the signature
2. Ensure you're using the raw request body (not parsed JSON) when generating the HMAC
3. Check that the signature header format is correct: sha256=<hex_string>

Common Errors

psycopg2 not found

Error: ModuleNotFoundError: No module named 'psycopg2'

Solution:

pip install psycopg2-binary

raw_events table missing

Error: UndefinedTable: relation "raw_events" does not exist

Solution:

alembic upgrade head

Supabase port already allocated

Error: Port conflict when starting Supabase

Solution:

1. Stop other Supabase projects: npx supabase stop
2. Or edit supabase/config.toml to use different ports
3. Check for running containers: docker ps

Webhook returns 422

Error: 422 Unprocessable Entity

Solution: Ensure you're sending required headers:

• X-Event-Id: Unique event identifier
• X-Signature: HMAC SHA256 signature in format sha256=<hex>

Example:

curl -X POST http://127.0.0.1:8000/webhooks/bank/transactions \
  -H "X-Event-Id: evt_001" \
  -H "X-Signature: sha256=..." \
  -H "Content-Type: application/json" \
  -d '{...}'

License

MIT

---

⚠️ NOTE: Local Supabase credentials and API keys are NOT included in this repo. Run npx supabase start to generate your own local credentials.