# 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`