# Database Design

| Field | Value |
|-------|-------|
| Document ID | DB-ECOM-001 |
| Version | 1.0 |
| Date | 2025-12-01 |
| Status | Approved |

---

## 1. Overview

Each service owns its own SQLite database file. There are **no cross-service foreign keys** and **no shared database**. Data references between services use logical IDs (e.g., `order_id` in payments is a logical reference, not a foreign key).

| Service | Database File | Tables |
|---------|--------------|--------|
| Order Service | order_service.db | orders, order_items |
| Payment Service | payment_service.db | payments, refunds |
| Notification Service | notification_service.db | notification_templates, notifications |
| Frontend Admin Dashboard | None | — (stateless SPA, no database) |

---

## 2. Entity-Relationship Diagram

```mermaid
erDiagram
    orders {
        int id PK
        string customer_email
        string customer_name
        enum status
        float total_amount
        string currency
        datetime created_at
        datetime updated_at
    }
    order_items {
        int id PK
        int order_id FK
        string product_name
        int quantity
        float unit_price
    }
    payments {
        int id PK
        int order_id UK "UNIQUE"
        float amount
        string currency
        enum status
        string payment_method
        string transaction_id UK
        datetime created_at
        datetime updated_at
    }
    refunds {
        int id PK
        int payment_id FK
        float amount
        string reason
        string status
        datetime created_at
    }
    notification_templates {
        int id PK
        string name UK
        string subject_template
        text body_template
        enum channel
        string language
    }
    notifications {
        int id PK
        int order_id "NOT UNIQUE"
        enum channel
        string template_name
        string recipient
        string subject
        text body
        enum status
        datetime sent_at
        datetime created_at
    }

    orders ||--o{ order_items : "has"
    payments ||--o{ refunds : "has"
    orders ||--|| payments : "logical ref (order_id)"
    orders ||--o{ notifications : "logical ref (order_id)"
```

---

## 3. Order Service Database

### 3.1 orders Table

| Column | Type | Nullable | Default | Constraint | Notes |
|--------|------|----------|---------|-----------|-------|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| customer_email | VARCHAR | No | | | |
| customer_name | VARCHAR | No | | | Japanese names supported |
| status | ENUM | No | "pending" | | OrderStatus enum |
| total_amount | FLOAT | No | | | Sum of items |
| currency | VARCHAR | No | "JPY" | | |
| created_at | DATETIME | No | now() | | UTC |
| updated_at | DATETIME | No | now() | | UTC, auto-update |

> **Missing columns for bulk operations:**
> - No `batch_id` — cannot group orders by import batch
> - No `csv_source` — cannot track which CSV file an order came from
> - No `bulk_import_group` — no batch correlation
> - No `import_row_number` — cannot map back to CSV row

### 3.2 order_items Table

| Column | Type | Nullable | Constraint |
|--------|------|----------|-----------|
| id | INTEGER | No | PRIMARY KEY |
| order_id | INTEGER | No | FOREIGN KEY → orders.id, CASCADE DELETE |
| product_name | VARCHAR | No | |
| quantity | INTEGER | No | > 0 |
| unit_price | FLOAT | No | > 0 |

**Indexes:** `ix_orders_id` on orders.id, `order_items.order_id` FK index.

---

## 4. Payment Service Database

### 4.1 payments Table

| Column | Type | Nullable | Constraint | Notes |
|--------|------|----------|-----------|-------|
| id | INTEGER | No | PRIMARY KEY | |
| order_id | INTEGER | No | **UNIQUE** | **1:1 with order — prevents batch grouping** |
| amount | FLOAT | No | | Min 100, Max 1,000,000 JPY |
| currency | VARCHAR | No | Default "JPY" | |
| status | ENUM | No | Default "pending" | PaymentStatus enum |
| payment_method | VARCHAR | No | Default "credit_card" | |
| transaction_id | VARCHAR | No | UNIQUE | UUID |
| created_at | DATETIME | No | now() | |
| updated_at | DATETIME | No | now() | |

> **CRITICAL CONSTRAINT:** The UNIQUE constraint on `order_id` enforces a strict 1:1 payment-to-order relationship. This means:
> - Each order can have exactly one payment record
> - There is no way to create a batch payment covering multiple orders
> - No `batch_id` or `payment_group_id` column exists

### 4.2 refunds Table

| Column | Type | Nullable | Constraint |
|--------|------|----------|-----------|
| id | INTEGER | No | PRIMARY KEY |
| payment_id | INTEGER | No | FOREIGN KEY → payments.id |
| amount | FLOAT | No | = payment.amount (full refund) |
| reason | VARCHAR | No | |
| status | VARCHAR | No | Default "completed" |
| created_at | DATETIME | No | now() |

---

## 5. Notification Service Database

### 5.1 notification_templates Table

| Column | Type | Nullable | Constraint |
|--------|------|----------|-----------|
| id | INTEGER | No | PRIMARY KEY |
| name | VARCHAR | No | UNIQUE |
| subject_template | VARCHAR | No | Jinja2 template |
| body_template | TEXT | No | Jinja2 template |
| channel | ENUM | No | EMAIL or SMS |
| language | VARCHAR | No | Default "ja" |

**Seed Data (3 records):**

| name | subject_template | language |
|------|-----------------|----------|
| ORDER_CONFIRMATION | ご注文確認 — 注文番号 #{{ order_id }} | ja |
| ORDER_SHIPPED | 発送のお知らせ — 注文番号 #{{ order_id }} | ja |
| ORDER_CANCELLED | ご注文キャンセルのお知らせ — 注文番号 #{{ order_id }} | ja |

### 5.2 notifications Table

| Column | Type | Nullable | Constraint | Notes |
|--------|------|----------|-----------|-------|
| id | INTEGER | No | PRIMARY KEY | |
| order_id | INTEGER | No | NOT unique | Multiple notifications per order |
| channel | ENUM | No | | EMAIL or SMS |
| template_name | VARCHAR | No | | Template used |
| recipient | VARCHAR | No | | Email or phone |
| subject | VARCHAR | Yes | | Rendered subject |
| body | TEXT | Yes | | Rendered body |
| status | ENUM | No | Default "pending" | PENDING, SENT, FAILED |
| sent_at | DATETIME | Yes | | Set when sent |
| created_at | DATETIME | No | now() | |

> **Note:** `order_id` is NOT unique — an order can have multiple notifications. However, there is **no batch_id column** for tracking grouped notification sends.

---

## 6. Data Integrity

- **No cross-service foreign keys.** Each service database is independent.
- **Eventual consistency.** If Payment Service records a payment but Order Service webhook fails, data may be temporarily inconsistent.
- **Logical references.** `order_id` in payments and notifications is a logical reference, not enforced by FK.

---

## 7. Schema Limitations for Bulk Operations

| Table | Missing for Bulk | Impact |
|-------|-----------------|--------|
| orders | No `batch_id`, `csv_source`, `bulk_import_group`, `import_row_number` | Cannot track which orders belong to a batch import |
| payments | Unique constraint on `order_id` prevents batch payment grouping. No `batch_payment_id` | Must create one payment per order |
| notifications | No `batch_id` for grouped sends. No `batch_notification_group` | Cannot correlate notifications from same import |
| (new table needed) | No `import_history` table exists | Cannot track import attempts, progress, success/failure counts |

Adding bulk order import would require:
1. **orders table:** Add `batch_id` (nullable), `csv_source`, `import_row_number` columns
2. **payments table:** Consider removing unique constraint or adding batch payment table
3. **notifications table:** Add `batch_id` column
4. **New table:** `import_history` (id, filename, total_rows, success_count, failure_count, status, created_at)