# Data Model

The data splits into two groups: **commerce entities Medusa already gives us** (don't
rebuild these) and **custom modules we add** for the AI/CMS/experiment features.

## 1. Commerce entities (from Medusa — use as-is)

Medusa ships these. We configure and extend them, not reinvent them.

| Entity | Purpose |
|---|---|
| **Product / ProductVariant** | Catalog items + their sizes/colors (e.g. a saree in 3 borders) |
| **ProductCollection / Category** | Groupings (Razai, Sarees, Bedsheets, Table Linen…) |
| **InventoryItem / StockLocation** | Stock levels per location |
| **Cart / LineItem** | Shopping cart |
| **Order / Fulfillment / Return** | Orders and their lifecycle |
| **Customer / CustomerGroup** | Buyers; groups for wholesale/boutique resellers |
| **Region / Currency** | India (INR) + international (USD/GBP/AUD) regions for the diaspora |
| **PriceList** | Region/customer-specific pricing, sales |
| **Promotion / Discount** | Coupons, automatic promos |
| **Payment / PaymentSession** | Razorpay/Stripe/PayPal payments |
| **ShippingOption / ShippingProfile** | Domestic + international shipping |
| **User / ApiKey** | Admin users + the copilot's scoped token |

These cover **Orders, Products, Discounts, Store** sidebar items directly.

## 2. Custom modules we add

Medusa v2 lets us register **custom modules** (each owns its tables + service). We add:

### 2a. `ai_agent_run` — migrates the existing marketing data

Mirrors the current `data/*.json` run shape exactly so **no marketing history is lost** and
the runner code barely changes.

| Field | Type | Notes |
|---|---|---|
| `id` | pk | |
| `agent_id` | text | matches `agents/manifest.json` id (e.g. `keyword-research`) |
| `ts` | timestamptz | run time |
| `mode` | text | `scheduled` \| `manual` \| `task` |
| `task` | text null | owner-supplied task, if any |
| `summary` | text | 1–2 sentences |
| `items` | jsonb | `[{title, detail, priority, type}]` |
| `next_actions` | jsonb | `["..."]` |

**Migration mapping** (today → new):

```text
data/<agent-id>.json  →  many ai_agent_run rows (one per element of runs[])
  runs[i].ts           → ai_agent_run.ts
  runs[i].mode         → ai_agent_run.mode
  runs[i].task         → ai_agent_run.task
  runs[i].summary      → ai_agent_run.summary
  runs[i].items        → ai_agent_run.items  (jsonb, shape identical)
  runs[i].next_actions → ai_agent_run.next_actions (jsonb)
agents/manifest.json   →  seed an `ai_agent` definition table (id, name, icon,
                          category, prompt, enabled) — replaces config/control.json flags
```

A one-time script reads each `data/*.json` and the n8n `jh_runs` table and inserts rows.

### 2b. `ai_agent` — agent definitions + on/off switches

Replaces `agents/manifest.json` + `config/control.json` enabled flags: `id`, `name`, `icon`,
`category`, `prompt`, `tools` (jsonb), `enabled` (bool), `model`. Lets the owner toggle/edit
agents from the admin instead of editing JSON.

### 2c. `content_block` — the Content section

Powers storefront pages/sections/blog. `id`, `type` (page|section|blog|banner), `slug`,
`title`, `body` (jsonb — structured blocks), `status` (draft|published), `locale`,
`updated_by`. AI-drafted copy lands here as drafts for owner review.

### 2d. `theme_setting` — the theme/design config

Key-value + JSON for the active theme: colors, fonts, logo, homepage section order,
hero content. Read by the Next.js storefront; edited from the admin. See `04-…`.

### 2e. `plugin_registry` — installed integrations

`id`, `key` (e.g. `razorpay`, `mailchimp`, `meta-feed`), `enabled`, `config` (jsonb,
secrets referenced by env not stored raw), `installed_at`. Drives the Integrations screen.

### 2f. `ab_experiment` — A/B testing (v2)

`id`, `name`, `target` (storefront element / price / copy), `variants` (jsonb: A/B and
weights), `metric` (CTR/conversion/AOV), `status`, `started_at`, `results` (jsonb). The
storefront assigns a visitor to a variant and reports the metric back.

### 2g. `support_setting` — support-agent config

`id`, `tone`, `system_prompt`, `escalation_rules` (jsonb), `office_hours`, `enabled`,
`channels` (jsonb: web chat / email / WhatsApp). Drives the storefront support agent
(Pillar 3) and is editable from Claude via the `configureSupportAgent` MCP tool.

### 2h. `channel_listing` — marketplace sync (v2)

Maps a Medusa product/variant to its external listing per channel: `id`, `product_id`,
`channel` (`amazon`|`flipkart`), `external_id`, `status` (draft|live|error), `last_synced_at`,
`payload` (jsonb). Keeps Amazon/Flipkart listings linked to the canonical Medusa catalog so
Claude can push and track them without divergence. (Generalized into the **Channel Manager**
interface — see `07-architecture-hardening.md §12`.)

### 2i. Hardening additions (review-driven — see `07-architecture-hardening.md`)

- **Versioning on theme/content.** `theme_setting` and `content_block` gain
  `state` (`draft`|`published`), `version` (int), and a `*_version` history table → enables
  **draft → preview → publish + rollback** (07 §5). Edits are validated against a strict
  **JSON Schema** before write (07 §6).
- **`is_test` flag** on `order` (extension) and `ai_agent_run` → test orders are filtered out
  of all revenue/conversion reporting (07 §10).
- **`ai_memory`** — business memory the AI always loads: `key`, `category` (brand-voice|policy|
  supplier|persona|campaign|shipping…), `value` (jsonb), `updated_at` (07 §9).
- **`role` / `permission`** — RBAC for staff + a least-privilege **AI role** (07 §10).
- **`asset`** — object-storage (S3/R2) refs: `id`, `url`, `type`, `alt`, `meta` (07 §11);
  binaries live on the CDN, not cPanel disk.
- **`event`** (append-only) + **`workflow`** definitions — the event bus + "when X then Y"
  automations (07 §7–8).
- **Agent Registry:** the `ai_agent` table (§2b) extends with `permissions`, `schedule`,
  `memory_ref`, `cost` (07 §13).

### 2j. Growth & sales modules (see `08-growth-and-sales-engine.md`)

- **`customer_segment`** — RFM / VIP / new-vs-repeat / festival / at-risk / wholesale; built on
  Medusa `CustomerGroup`.
- **`campaign`** — first-class: `goal`, `start`/`end`, `target_skus`, `kpis`, `channels`,
  `budget`, `status`; owned by the Campaign Coordinator (08 §10).
- **`bundle`** — gift-set / festival / office-wear collections (products + bundle price +
  margin); AI-generated (08 §6).
- **`referral_code`** — influencer/affiliate codes: `code`, `owner`, `channel`, `discount`,
  `uses`, `revenue`, `payout` (08 §9).
- **`attribution_event`** — append-only: `session`, `channel` (whatsapp|instagram|youtube|seo|
  influencer), `utm`, `order_id` → revenue-by-channel (08 §9).
- **`whatsapp_subscriber`** + **`whatsapp_template`** — opt-in contacts + approved Cloud-API
  templates for broadcasts/journeys (08 §4a).
- **`loyalty`/`vip`** — tier, points/perks per customer (08 §5).
- **`wholesale_lead`** — B2B lead capture: contact, business, requirement, status (08 §12).

Journeys (post-purchase, cart recovery, comeback) reuse the **workflow engine** (07 §8); all of
the above emit/consume **events** (07 §7).

## 3. Entity relationship sketch

```mermaid
erDiagram
    PRODUCT ||--o{ PRODUCT_VARIANT : has
    PRODUCT }o--o{ COLLECTION : in
    ORDER ||--o{ LINE_ITEM : contains
    ORDER }o--|| CUSTOMER : placed_by
    ORDER }o--|| REGION : in
    PRODUCT_VARIANT ||--o{ INVENTORY_ITEM : stocked_as
    AI_AGENT ||--o{ AI_AGENT_RUN : produces
    CONTENT_BLOCK }o--|| THEME_SETTING : rendered_with
    AB_EXPERIMENT }o--o{ CONTENT_BLOCK : tests
    PLUGIN_REGISTRY ||--o{ PAYMENT : provides
    PRODUCT ||--o{ CHANNEL_LISTING : listed_on
    CAMPAIGN ||--o{ AI_AGENT_RUN : drives
    CUSTOMER ||--o{ CUSTOMER_SEGMENT : in
    CUSTOMER ||--o{ ATTRIBUTION_EVENT : generates
    BUNDLE }o--o{ PRODUCT : groups
```

(PRODUCT, ORDER, CUSTOMER, REGION, INVENTORY_ITEM, PAYMENT, LINE_ITEM, COLLECTION = Medusa.
AI_AGENT, AI_AGENT_RUN, CONTENT_BLOCK, THEME_SETTING, AB_EXPERIMENT, PLUGIN_REGISTRY,
SUPPORT_SETTING, CHANNEL_LISTING, CAMPAIGN, CUSTOMER_SEGMENT, BUNDLE, REFERRAL_CODE,
ATTRIBUTION_EVENT, WHATSAPP_SUBSCRIBER, LOYALTY, WHOLESALE_LEAD = ours.)

## 4. Principles

- **Don't fork Medusa's core tables.** Extend via custom modules and module links, so we can
  upgrade Medusa cleanly.
- **Keep the run shape stable.** `ai_agent_run.items`/`next_actions` keep the exact JSON the
  current dashboard and `scripts/record-run.mjs` already produce → minimal rewrite.
- **Secrets out of the DB.** `plugin_registry.config` references env-var names; raw API keys
  live in server environment only.