Database

OptimaAi uses PostgreSQL with SQLAlchemy as the ORM. Tables are created automatically on startup via init_db().

Connection

The connection string is read from the DATABASE_URL environment variable:

DATABASE_URL=postgresql://optimaai:optimaai123@localhost:5432/optimaai_db

Default: postgresql://optimaai:optimaai123@localhost:5432/optimaai_db

Tables

kpi_snapshots

Stores a timestamped snapshot every time KPIs are loaded from ML artefacts.

Column	Type	Description
id	Integer PK	Auto-increment primary key
created_at	DateTime	Snapshot creation timestamp
model_version	String(50)	Optional model version tag
revenue_mape	Float	Revenue model MAPE %
revenue_r2	Float	Revenue model R²
revenue_mae	Float	Revenue model MAE
forecast_bias	Float	Signed forecast bias
churn_roc_auc	Float	Churn model ROC-AUC
churn_f1	Float	Churn model F1 score
churn_rate_pct	Float	Dataset churn rate %
growth_mape	Float	Growth model MAPE %
growth_r2	Float	Growth model R²
prophet_mape	Float	Prophet MAPE %
revenue_forecast_12	JSON	12-period revenue forecast array
growth_forecast_12	JSON	12-month growth forecast array

---

recommendations

Stores every LLM-generated recommendation.

Column	Type	Description
id	Integer PK	Auto-increment primary key
created_at	DateTime	Generation timestamp
role	String(50)	User role (e.g. Sales Manager)
recommendation	Text	Full recommendation text
model_used	String(100)	LLM model identifier
kpi_snapshot_id	Integer	FK → kpi_snapshots.id

---

bmc_results

Stores every generated Business Model Canvas.

Column	Type	Description
id	Integer PK	Auto-increment primary key
created_at	DateTime	Generation timestamp
platform_name	String(100)	Platform label
bmc_text	Text	Raw markdown of the full BMC
bmc_blocks	JSON	Parsed dict of 9 canvas sections
model_used	String(100)	LLM model identifier
kpi_snapshot_id	Integer	FK → kpi_snapshots.id

---

uploads (dynamic)

Created by datasets_routes.py at runtime. Tracks every file uploaded via the Datasets page.

Column	Type	Description
id	Integer PK	Auto-increment
created_at	DateTime	Upload timestamp
filename	String	Original filename
category	String	User-assigned category tag
user_id	Integer	Uploader ID
row_count	Integer	Rows after preprocessing
quality_score	Float	Data quality score (0–100)
cleaned_table	String	Name of the cleaned data table
kb_ingested	Boolean	Whether it was embedded into ChromaDB

Session Management

A SQLAlchemy session is injected into every route via FastAPI's dependency injection:

from app.database import get_db
from sqlalchemy.orm import Session
from fastapi import Depends
 
@router.get("/example")
def my_route(db: Session = Depends(get_db)):
    # db is automatically closed after the request
    ...

Manual Table Creation

from app.database import init_db
init_db()  # Creates all tables if they don't exist