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