File size: 2,871 Bytes
a8f9097 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | # 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`.
|