Database Architecture

Prost Database Architecture

┌──────────────────────────────────────────────────────────┐
│                       ProstDB Class                      │
│                  (Main Database Manager)                 │
└──────────────────────────────────────────────────────────┘
                              │
        ┌─────────────────────┼─────────────────────┐
        │                     │                     │
        ▼                     ▼                     ▼
┌──────────────┐      ┌──────────────┐      ┌──────────────┐
│     User     │      │  DrinkType   │      │    Order     │
│  Operations  │      │  Operations  │      │  Operations  │
└──────────────┘      └──────────────┘      └──────────────┘
        │                     │                     │
        │                     │                     │
        ▼                     ▼                     ▼
┌──────────────┐      ┌──────────────┐      ┌──────────────┐
│   Purchase   │      │    Stock     │      │  Repayment   │
│  Operations  │      │  Operations  │      │  Operations  │
└──────────────┘      └──────────────┘      └──────────────┘

Domain Classes (Data Models)

@dataclass
class User:
    user_id: Optional[int]
    name: str
    email: str
    balance: float
    
    # Helper methods
    is_in_debt() -> bool
    is_owed() -> bool
    from_db_row(row) -> User

@dataclass
class DrinkType:
    drink_type_id: Optional[int]
    name: str
    brand: str
    
    from_db_row(row) -> DrinkType

@dataclass
class StockBatch:
    batch_id: Optional[int]
    drink_type_id: int
    order_id: int
    orderer_id: int
    cost_per_item: float
    initial_qty: int
    remaining_qty: int
    date_added: Optional[str]
    
    is_depleted() -> bool
    from_db_row(row) -> StockBatch

# ... and more (Order, Purchase, Repayment)

Database Operations Flow

Adding a Purchase (Example)

User Action: "Bob buys a Cola"
    │
    ▼
db.add_purchase("Bob", "Cola")
    │
    ├─► Lookup Bob's user_id
    │
    ├─► Lookup Cola's drink_type_id
    │
    ├─► Find oldest batch with stock
    │
    ├─► Create purchase record
    │
    ├─► Decrement batch quantity
    │
    ├─► Update Bob's balance (-)
    │
    └─► Update orderer's balance (+)

Class Hierarchy

sqlite.db (Database File)
    │
    └─► ProstDB Instance
            │
            ├─► User Management
            │   ├─ add_user()
            │   ├─ get_user_by_id()
            │   ├─ get_user_by_name()
            │   ├─ get_all_users()
            │   └─ get_user_balance()
            │
            ├─► Drink Type Management
            │   ├─ add_drink_type()
            │   ├─ get_drink_type_by_id()
            │   ├─ get_drink_type_by_name()
            │   └─ get_all_drink_types()
            │
            ├─► Stock & Orders
            │   ├─ stock_new_drinks()
            │   └─ get_stock_status()
            │
            ├─► Purchases
            │   ├─ add_purchase()
            │   └─ get_recent_purchases()
            │
            ├─► Repayments
            │   ├─ add_repayment()
            │   └─ get_user_debts()
            │
            └─► Internal Helpers
                ├─ _get_connection()
                └─ setup_database()

Database Schema

users
├── user_id (PK)
├── name
├── email (UNIQUE)
└── balance

drink_types
├── drink_type_id (PK)
├── name
└── brand

orders
├── order_id (PK)
├── orderer_id (FK -> users)
├── order_date
└── total_cost

stock_batches
├── batch_id (PK)
├── drink_type_id (FK -> drink_types)
├── order_id (FK -> orders)
├── orderer_id (FK -> users)
├── cost_per_item
├── initial_qty
├── remaining_qty
└── date_added

drink_purchases
├── purchase_id (PK)
├── user_id (FK -> users)
├── batch_id (FK -> stock_batches)
├── cost
├── charged_to_orderer_id (FK -> users)
└── purchase_date

repayments
├── repayment_id (PK)
├── payer_id (FK -> users)
├── receiver_id (FK -> users)
├── amount
└── payment_date

Key Design Principles

1. Single Responsibility: Each class has one clear purpose
2. DRY (Don't Repeat Yourself): Connection management in one place
3. Encapsulation: Database details hidden inside ProstDB
4. Type Safety: Dataclasses provide structure and validation

Usage Patterns

Pattern 1: Single Database (Most Common)

from prost.db import ProstDB

# Create once at app startup
db = ProstDB()

# Use throughout application
db.add_user(...)
db.add_purchase(...)

Pattern 2: Multiple Databases

from prost.db import ProstDB

# Different databases for different purposes
prod_db = ProstDB("production.db")
test_db = ProstDB("test.db")

prod_db.add_user(...)
test_db.add_user(...)

Pattern 3: Context Manager (Future Enhancement)

# Potential future enhancement
with ProstDB("my.db") as db:
    db.add_user(...)
    db.add_purchase(...)
# Automatic cleanup