Finances Database Architecture

Decisión

Finances usa una base de datos propia, finances_db, y un modelo multitenant interno basado en tenant_id.

El tenant_id representa un espacio financiero: persona, pareja, familia, hogar o pequeño negocio. Los usuarios no son la frontera de seguridad; los accesos viven en finance_members.

El diccionario de datos exacto se genera desde PostgreSQL real en ../../backend/docs/03_REFERENCE/schema.md. Este documento explica el modelo y las decisiones.

Patrón De Aislamiento

Finances replica el patrón multitenant validado en Gastro:

Infra crea una DB y role propios para la vertical.
El backend resuelve tenant_id desde el bearer JWT.
TenantResolverMiddleware guarda el tenant en un ContextVar.
get_db inyecta app.current_tenant_id en la sesión SQL.
PostgreSQL RLS bloquea filas de otros tenants.
Los repositorios además filtran explícitamente por tenant_id.

flowchart LR
    Client[Cliente API] --> API[FastAPI]
    API --> Auth[Auth/RBAC]
    API --> Resolver[TenantResolverMiddleware]
    Resolver --> Context[ContextVar tenant_id]
    Context --> Session[get_db set_config]
    Session --> RLS[PostgreSQL RLS]
    RLS --> Repo[Repositorios tenant-scoped]
    Repo --> Data[(finance_* tables)]

Modelo Entidad-Relación

erDiagram
    finance_tenants ||--o{ finance_members : has
    finance_tenants ||--o{ finance_accounts : owns
    finance_tenants ||--o{ finance_categories : owns
    finance_tenants ||--o{ finance_transactions : owns
    finance_tenants ||--o{ finance_transaction_splits : owns
    finance_tenants ||--o{ finance_credit_cards : owns
    finance_tenants ||--o{ finance_card_purchases : owns
    finance_tenants ||--o{ finance_card_installments : owns
    finance_tenants ||--o{ finance_debts : owns
    finance_tenants ||--o{ finance_debt_payments : owns
    finance_tenants ||--o{ finance_audit_log : records

    finance_accounts ||--o{ finance_transactions : contains
    finance_accounts ||--o{ finance_credit_cards : backs
    finance_accounts ||--o{ finance_debt_payments : funds

    finance_categories ||--o{ finance_categories : parent
    finance_categories ||--o{ finance_transactions : classifies
    finance_categories ||--o{ finance_transaction_splits : classifies

    finance_transactions ||--o{ finance_transaction_splits : splits
    finance_transactions ||--o| finance_card_purchases : generated_by

    finance_credit_cards ||--o{ finance_card_purchases : charges
    finance_card_purchases ||--o{ finance_card_installments : schedules

    finance_debts ||--o{ finance_debt_payments : receives

    finance_tenants {
      uuid id PK
      string slug
      string name
      string persona_type
      boolean is_active
      timestamp deleted_at
    }

    finance_members {
      uuid id PK
      uuid tenant_id FK
      string email
      string role
      string status
      text password_hash
      text invitation_token_hash
      timestamp deleted_at
    }

    finance_accounts {
      uuid id PK
      uuid tenant_id FK
      string name
      string type
      string currency_code
      numeric current_balance
      boolean include_in_available_today
      boolean is_active
      timestamp deleted_at
    }

    finance_categories {
      uuid id PK
      uuid tenant_id FK
      uuid parent_id FK
      string name
      string type
      int level
      boolean is_active
      timestamp deleted_at
    }

    finance_transactions {
      uuid id PK
      uuid tenant_id FK
      uuid account_id FK
      uuid category_id FK
      numeric amount
      string currency_code
      timestamp occurred_at
      string source
      timestamp deleted_at
    }

    finance_transaction_splits {
      uuid id PK
      uuid tenant_id FK
      uuid transaction_id FK
      uuid category_id FK
      numeric amount
      timestamp deleted_at
    }

    finance_credit_cards {
      uuid id PK
      uuid tenant_id FK
      uuid account_id FK
      string nickname
      string last4
      numeric credit_limit
      int cut_off_day
      int payment_day
      boolean is_active
      timestamp deleted_at
    }

    finance_card_purchases {
      uuid id PK
      uuid tenant_id FK
      uuid credit_card_id FK
      uuid transaction_id FK
      string merchant_name
      numeric amount
      date purchase_date
      int installments_count
      timestamp deleted_at
    }

    finance_card_installments {
      uuid id PK
      uuid tenant_id FK
      uuid purchase_id FK
      int installment_number
      date due_date
      numeric amount
      string status
      timestamp deleted_at
    }

    finance_debts {
      uuid id PK
      uuid tenant_id FK
      string name
      string debt_type
      numeric original_amount
      numeric current_balance
      numeric interest_rate_tea
      string status
      timestamp deleted_at
    }

    finance_debt_payments {
      uuid id PK
      uuid tenant_id FK
      uuid debt_id FK
      uuid source_account_id FK
      numeric amount
      date payment_date
      timestamp paid_at
      timestamp deleted_at
    }

    finance_audit_log {
      uuid id PK
      uuid tenant_id FK
      uuid actor_id
      string action
      string resource_type
      uuid resource_id
      jsonb before_data
      jsonb after_data
    }

Diccionario De Datos

El diccionario de datos no se mantiene a mano. La fuente canónica es schema.md, generado con apps/finances/backend/scripts/generate_schema_docs.py contra PostgreSQL real.

Ese documento contiene por tabla:

Columnas.
Tipos.
Nulabilidad.
Defaults.
Índices.
Políticas RLS.

Reglas De Modelado

Toda tabla de negocio tenant-scoped lleva tenant_id.
Toda tabla tenant-scoped tiene RLS.
Toda entidad mutable lleva created_at, updated_at, deleted_at.
Los índices de lectura frecuentes empiezan por tenant_id o incluyen tenant_id.
Los únicos de negocio deben aislar por tenant_id o justificar por qué son globales.
finance_tenants no usa tenant_id; es la tabla raíz.
Tablas globales futuras, como tasas TRM oficiales, deben documentar explícitamente por qué no son tenant-scoped.

RLS

Cada tabla tenant-scoped debe tener una política null-safe:

ALTER TABLE finance_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON finance_transactions
USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

El estado generado actual de cada política vive en schema.md.

Infra

La base se levanta desde infra/docker-compose.yml usando postgres:16-alpine. El script infra/postgres/init/04_finances.sh crea el role de Finances y finances_db solo cuando Postgres inicializa un volumen nuevo.

En una instalación existente, usa el runbook Run Backend And Database Locally. No borres volúmenes para forzar scripts de init.