# Database Schema Documentation

The RiskOS Marketplace Intelligence system uses an embedded SQLite database (`marketplace.db`) to store transaction, product, and customer data.

## 1. Entity-Relationship Overview

```mermaid
erDiagram
    customers ||--o{ orders : "places"
    products ||--o{ orders : "contained_in"
    orders ||--o{ returns : "results_in"
    customers ||--o{ fraud_events : "involved_in"
    orders ||--o{ fraud_events : "linked_to"
```

## 2. Table Definitions

### `products`
Stores information about the marketplace inventory.
- `product_id` (TEXT, PK): Unique identifier (e.g., P0001).
- `name` (TEXT): Full product name.
- `category` (TEXT): High-level category (Electronics, Clothing, etc.).
- `subcategory` (TEXT): Specific type (Laptop, Sneakers).
- `brand` (TEXT): Manufacturer brand.
- `unit_price` (REAL): Selling price in USD.
- `cost_price` (REAL): Procurement cost in USD.
- `stock_quantity` (INTEGER): Current inventory levels.
- `supplier_country` (TEXT): Origin of the product.

### `customers`
Stores profile and risk metadata for users.
- `customer_id` (TEXT, PK): Unique identifier (e.g., C0001).
- `country` (TEXT): Customer residency.
- `region` (TEXT): Geographical region (North America, Europe, etc.).
- `customer_segment` (TEXT): retail, wholesale, or enterprise.
- `account_age_days` (INTEGER): Days since account creation.
- `total_lifetime_value` (REAL): Aggregated historical spend.
- `risk_score` (REAL): Probability of fraud (0.0 to 1.0).

### `orders`
The central transaction log.
- `order_id` (TEXT, PK): Unique identifier.
- `customer_id` (TEXT, FK): Reference to `customers`.
- `product_id` (TEXT, FK): Reference to `products`.
- `quantity` (INTEGER): Units purchased.
- `total_amount` (REAL): Final transaction value.
- `order_status` (TEXT): completed, returned, cancelled, or pending.
- `payment_method` (TEXT): card, bank_transfer, crypto, or cash.
- `is_flagged` (INTEGER): Binary flag for suspicious activity (0 or 1).
- `order_date` (TEXT): Timestamp in 'YYYY-MM-DD HH:MM:SS' format.

### `returns`
Logs for returned items and refunds.
- `return_id` (TEXT, PK): Unique identifier.
- `order_id` (TEXT, FK): Reference to `orders`.
- `reason` (TEXT): defective, wrong_item, changed_mind, etc.
- `refund_amount` (REAL): Amount returned to the customer.

### `fraud_events`
Audit trail for confirmed or suspected fraud.
- `event_id` (TEXT, PK): Unique identifier.
- `event_type` (TEXT): chargeback, return_fraud, identity_theft, etc.
- `amount_at_risk` (REAL): Potential or actual loss value.
- `resolved` (INTEGER): Closure status (0 or 1).

## 3. Data Integrity
- **Stateless Seeding**: Use `python scripts/setup_db.py` to recreate the database from scratch.
- **Foreign Keys**: Enforced via SQLite `PRAGMA foreign_keys = ON`.
- **Dates**: Stored as ISO-8601 strings for compatibility with SQLite `strftime`.