Spaces:
Sleeping
Sleeping
|
Download docs/DATABASE_SCHEMA.md from Coeinterns/bank-statement-api: direct link, hf CLI and curl.
- Browser
- Download file 11.8 kB
-
https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/docs/DATABASE_SCHEMA.md
- Command line
-
hf download hf://spaces/Coeinterns/bank-statement-api/docs/DATABASE_SCHEMA.md
-
curl -L -o DATABASE_SCHEMA.md https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/docs/DATABASE_SCHEMA.md
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` | |