Spaces:
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
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:
- PostgreSQL Database (
DATABASE_URL): Houses user logins, session state tables, security logs, and the classification category/rules database. - 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-manyUserSession),audit_logs(one-to-manyAuditLog).
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_priorityon(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
metadataas a class keyword. The python attributeevent_metadatais declared insideOverwatchEventORMto map to the database column explicitly namedmetadata. - Indices:
idx_ow_timestampontimestampidx_ow_document_idondocument_ididx_ow_document_nameondocument_nameidx_ow_log_levelonlog_levelidx_ow_stageonstage