Phase 1: Ledger Service - Data Models & Database Layer

[MattiasGees]

Overview

Create the core data models and PostgreSQL database layer for the Ledger Service.

Tasks

• Create pkg/ledger/models.go with Account and Transaction structs
• Create pkg/ledger/postgres.go with database operations
• Create deploy/postgresql/init-trustbank.sql with schema
• Add seed data for demo accounts (Checking $5000, Savings $5000)
• Implement X.509 certificate authentication to PostgreSQL

Database Schema

CREATE TABLE accounts (
    id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE transactions (
    id UUID PRIMARY KEY,
    from_account UUID REFERENCES accounts(id),
    to_account UUID REFERENCES accounts(id),
    amount DECIMAL(15,2) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

Testing Requirements

• Unit tests for all database operations
• Test with mock database interface
• Test X.509 connection setup
• Test transaction rollback on failure

Acceptance Criteria

• Models are well-documented with JSON tags
• All CRUD operations work with PostgreSQL
• X.509 auth works via spiffe-helper certificates
• 90%+ test coverage on database layer

Labels

phase-1 backend database