bank-statement-api / docs /DATABASE_SCHEMA.md
3v324v23's picture
Merge Conflict solved | Build error solved
205f25f
|
Raw History Blame Contribute Delete
11.8 kB
# BeFree Database Schema
This document details the database tables, indices, data types, and schemas used by the BeFree Bank Statement Processor backend.
---
## Database Architecture
BeFree operates with a two-database strategy:
1. **PostgreSQL Database** (`DATABASE_URL`): Houses user logins, session state tables, security logs, and the classification category/rules database.
2. **SQLite Database** (`data/logs/overwatch.db`): Serves as the high-speed WAL-enabled local store for pipeline stage events (Overwatch).
---
## Database 1: PostgreSQL (Auth & Rules DB)
### 1. Table: `users`
Represents the accounts authorized to access the system.
- **ORM Model**: `User` (`auth/models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Unique user identifier |
| `username` | `VARCHAR(100)`| No | None | Unique Index | Login name |
| `email` | `VARCHAR(200)`| Yes | None | Unique | User email address |
| `hashed_password`| `TEXT` | No | None | No | Bcrypt hash |
| `full_name` | `VARCHAR(200)`| Yes | None | No | Display name |
| `role` | `VARCHAR(50)` | No | `tester` | No | Security roles: `tester`, `manager`, `admin` |
| `is_active` | `BOOLEAN` | No | `true` | No | Account status |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Creation timestamp |
| `last_login_at` | `TIMESTAMP` | Yes | None | No | Last authentication time |
- **Relationships**: `sessions` (one-to-many `UserSession`), `audit_logs` (one-to-many `AuditLog`).
---
### 2. Table: `user_sessions`
Tracks active and revoked JWT credentials.
- **ORM Model**: `UserSession` (`auth/models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Session identifier |
| `user_id` | `VARCHAR` | No | FK `users.id` | Index | User linked to this session |
| `token_jti` | `VARCHAR(200)`| No | None | Unique | JWT identifier |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Login time |
| `expires_at` | `TIMESTAMP` | No | None | No | Expiration boundary |
| `is_revoked` | `BOOLEAN` | No | `false` | No | Active state flag |
| `ip_address` | `VARCHAR(50)` | Yes | None | No | User IP address |
| `user_agent` | `TEXT` | Yes | None | No | Browser signature |
---
### 3. Table: `audit_logs`
System security and user activity trail.
- **ORM Model**: `AuditLog` (`auth/models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `BIGINT` | No | Auto-increment | Primary Key | Log event sequence |
| `user_id` | `VARCHAR` | Yes | FK `users.id` | Index | Initiator ID |
| `username` | `VARCHAR(100)`| Yes | Denormalized | No | User snapshot name |
| `action` | `VARCHAR(200)`| No | None | No | Executed operation |
| `resource_type`| `VARCHAR(100)`| Yes | None | No | Target entity (e.g. `statement`) |
| `resource_id` | `VARCHAR(200)`| Yes | None | No | Target entity ID |
| `http_method` | `VARCHAR(10)` | Yes | None | No | REST method |
| `endpoint` | `VARCHAR(300)`| Yes | None | No | Request URL |
| `request_summary`| `TEXT` | Yes | None | No | Payload summary |
| `response_status`| `INTEGER` | Yes | None | No | HTTP status code |
| `ip_address` | `VARCHAR(50)` | Yes | None | No | Source IP |
| `user_agent` | `TEXT` | Yes | None | No | Browser signature |
| `occurred_at` | `TIMESTAMP` | No | `utcnow` | Index | Timestamp |
| `duration_ms` | `INTEGER` | Yes | None | No | Request execution time |
| `extra_data` | `TEXT` | Yes | JSON string | No | Additional diagnostic contexts |
| `session_duration_seconds`| `INTEGER`| Yes| None| No| Duration between login/logout |
---
### 4. Table: `classification_groups`
Top-level report sections (e.g., "Food and Groceries", "Utilities"). Used strictly for the Excel report tabs/layout structure.
- **ORM Model**: `ClassificationGroup` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Group ID |
| `name` | `VARCHAR(200)`| No | None | Unique Index | Group label |
| `display_order`| `INTEGER` | No | `0` | No | Tab sorting order in reports |
| `is_active` | `BOOLEAN` | No | `true` | No | Active flag |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Metadata creation date |
| `updated_at` | `TIMESTAMP` | No | `utcnow` | No | Modification date |
| `created_by` | `VARCHAR(200)`| Yes | None | No | Created by user |
| `updated_by` | `VARCHAR(200)`| Yes | None | No | Updated by user |
---
### 5. Table: `classification_categories`
Sub-categories assigned to transactions (e.g., "Fuel", "Pharmacy").
- **ORM Model**: `ClassificationCategory` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Category ID |
| `name` | `VARCHAR(200)`| No | None | Unique Index | Category label |
| `group_id` | `VARCHAR` | Yes | FK `classification_groups.id` | Index | Layout group association |
| `display_order`| `INTEGER` | No | `0` | No | Sorted ordering within group |
| `is_reportable`| `BOOLEAN` | No | `true` | No | Exclude from report if false |
| `sheet_name` | `VARCHAR(200)`| Yes | None | No | Targeted Excel sheet tab name |
| `sub_column` | `VARCHAR(200)`| Yes | None | No | Sub column designation |
| `is_active` | `BOOLEAN` | No | `true` | No | Active state flag |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Creation date |
| `updated_at` | `TIMESTAMP` | No | `utcnow` | No | Modification date |
| `created_by` | `VARCHAR(200)`| Yes | None | No | Created by user |
| `updated_by` | `VARCHAR(200)`| Yes | None | No | Updated by user |
---
### 6. Table: `classification_rules`
Keywords rule mappings for the rule engine.
- **ORM Model**: `ClassificationRule` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Rule ID |
| `name` | `VARCHAR(200)`| No | None | Index | Rule identifier name |
| `category_id` | `VARCHAR` | No | FK `classification_categories.id`| Index | Assigned category |
| `keywords_raw` | `TEXT` | No | `""` (newline sep) | No | Raw lowercase keywords |
| `priority` | `INTEGER` | No | `10` | Composite | Priority evaluation order |
| `match_on` | `VARCHAR(50)` | No | `description` | No | Match target field |
| `tx_type_filter`| `VARCHAR(20)` | Yes | `debit` or `credit` | No | Filter transaction type |
| `amount_min` | `FLOAT` | Yes | None | No | Lower bound threshold |
| `amount_max` | `FLOAT` | Yes | None | No | Upper bound threshold |
| `is_active` | `BOOLEAN` | No | `true` | No | Active status |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Creation date |
| `updated_at` | `TIMESTAMP` | No | `utcnow` | No | Modification date |
| `created_by` | `VARCHAR(200)`| Yes | None | No | Creator |
| `updated_by` | `VARCHAR(200)`| Yes | None | No | Modifier |
- **Indexes**: Composite Index `ix_classification_rules_category_priority` on `(category_id, priority)`.
---
### 7. Table: `learned_rule_logs`
Suggested database rules auto-learned from the LLM classifier awaiting admin promotion.
- **ORM Model**: `LearnedRuleLog` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Rule log ID |
| `keyword` | `VARCHAR(300)`| No | None | Index | Suggested keyword |
| `category` | `VARCHAR(200)`| No | None | No | Target category name string |
| `category_id` | `VARCHAR` | Yes | FK `classification_categories.id`| Index | Target category relation |
| `source` | `VARCHAR(50)` | No | None (`ml` or `llm`) | No | Originating classifier |
| `confidence` | `FLOAT` | Yes | None | No | Score |
| `example_description`| `TEXT` | Yes | None (newline sep) | No | Example transactions matched |
| `hit_count` | `INTEGER` | No | `1` | No | Match counter |
| `status` | `VARCHAR(50)` | No | `pending` (`pending`/`approved`/`rejected`) | No | Administrative status |
| `promoted_rule_id`| `VARCHAR` | Yes | None | No | Resulting active rule FK |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Creation date |
| `updated_at` | `TIMESTAMP` | No | `utcnow` | No | Modification date |
---
### 8. Table: `bpay_billers`
Standard lookup table for mapping BPAY codes to categories.
- **ORM Model**: `BpayBiller` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `VARCHAR` | No | UUIDv4 string | Primary Key | Biller record ID |
| `biller_code` | `VARCHAR(20)` | No | None | Unique Index | BPAY biller code string |
| `name` | `VARCHAR(200)`| No | None | No | Company name |
| `category_id` | `VARCHAR` | Yes | FK `classification_categories.id`| Index | Assigned category |
| `is_active` | `BOOLEAN` | No | `true` | No | Active state flag |
| `created_at` | `TIMESTAMP` | No | `utcnow` | No | Creation timestamp |
| `updated_at` | `TIMESTAMP` | No | `utcnow` | No | Modification timestamp |
---
### 9. Table: `classification_flat`
Flattened database category rules schema used for diagnostics and mappings.
- **ORM Model**: `ClassificationFlat` (`auth/rule_models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `id` | `UUID` | No | UUIDv4 | Primary Key | Row ID |
| `keyword` | `TEXT` | No | None | No | Keyword to match |
| `category` | `TEXT` | No | None | No | High level category label |
| `sub_category` | `TEXT` | Yes | None | No | Sub-category label |
| `is_active` | `BOOLEAN` | No | `true` | No | Active status |
| `created_at` | `TIMESTAMP` | No | `now()` | No | Creation date |
| `updated_at` | `TIMESTAMP` | Yes | None | No | Update date |
| `requires_review`| `BOOLEAN` | No | `false` | No | Requires audit verification |
| `risk_flags` | `TEXT` | Yes | `""` | No | CSV of risk indicators |
---
---
## Database 2: SQLite (Overwatch Observability DB)
The SQLite database file resides on the disk at `data/logs/overwatch.db`. It runs in WAL mode.
### 1. Table: `overwatch_events`
Stores structured logging data generated throughout extraction and classification pipeline steps.
- **ORM Model**: `OverwatchEventORM` (`overwatch/models.py`)
| Column Name | Data Type | Nullable | Default / Note | Index | Purpose |
| :--- | :--- | :--- | :--- | :--- | :--- |
| `event_id` | `TEXT` | No | UUIDv4 string | Primary Key | Unique log event ID |
| `document_id` | `TEXT` | No | None | Index | Associated statement ID |
| `document_name`| `TEXT` | No | None | Index | Filename at upload |
| `timestamp` | `TEXT` | No | ISO-8601 UTC timestamp | Index | Time generated |
| `log_level` | `TEXT` | No | `INFO`/`WARNING`/`ERROR` | Index | Severity level |
| `stage` | `TEXT` | No | None (e.g. `audit`, `normalisation`)| Index | Pipeline stage |
| `message` | `TEXT` | No | Max 500 characters | No | Log message content |
| `metadata` | `TEXT` | No | `"{}"` (JSON string) | No | Arbitrary metadata parameters |
- **ORM Metadata Attribute Mapping Note**: In Python, SQLAlchemy reserves `metadata` as a class keyword. The python attribute `event_metadata` is declared inside `OverwatchEventORM` to map to the database column explicitly named `metadata`.
- **Indices**:
- `idx_ow_timestamp` on `timestamp`
- `idx_ow_document_id` on `document_id`
- `idx_ow_document_name` on `document_name`
- `idx_ow_log_level` on `log_level`
- `idx_ow_stage` on `stage`