# Replenn — Database Schema Reference

**Version:** 1.0 · **Date:** August 17, 2026
**Database:** MySQL · **ORM:** Drizzle ORM
**Migration endpoint:** `POST /api/auth-migrate` (idempotent)

---

## Tables Overview

| Table | Purpose |
|---|---|
| `inventory_items` | Core inventory records |
| `categories` | Item categories with color labels |
| `storage_locations` | User-defined storage zones (Fridge, Pantry, etc.) |
| `stock_history` | Audit log of every quantity change |
| `shopping_list_items` | Shopping list entries |
| `user_subscriptions` | Stripe subscription state per user |
| `alert_preferences` | Per-user low-stock email alert settings |
| `households` | Household groups for sharing |
| `household_members` | Household membership + invite tracking |
| `ai_usage_log` | AI API call log for cost tracking |
| `user` | BetterAuth user records |
| `session` | BetterAuth active sessions |
| `account` | BetterAuth OAuth provider links |
| `verification` | BetterAuth email verification tokens |
| `two_factor` | BetterAuth TOTP secrets + backup codes |

---

## inventory_items

Primary inventory table. One row per tracked item per user.

```sql
CREATE TABLE inventory_items (
  id                  INT PRIMARY KEY AUTO_INCREMENT,
  user_id             VARCHAR(36)       NULL,           -- FK → user.id (NULL = legacy/anonymous)
  name                VARCHAR(255)      NOT NULL,
  category_id         INT               NULL,           -- FK → categories.id
  location_id         INT               NULL,           -- FK → storage_locations.id
  quantity            DECIMAL(10,2)     NOT NULL DEFAULT 0,
  unit                VARCHAR(50)       NOT NULL DEFAULT 'units',
  low_stock_threshold DECIMAL(10,2)     NOT NULL DEFAULT 2,
  notes               TEXT              NULL,
  photo_url           VARCHAR(500)      NULL,           -- /airo-assets/uploads/item-photos/…
  expiry_date         TIMESTAMP         NULL,
  created_at          TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP         DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

**Notes:**
- `quantity` and `low_stock_threshold` are stored as DECIMAL strings; parse with `parseFloat()` in application code.
- `photo_url` is written by `POST /api/items/:id/photo` after file upload to persistent storage.
- `expiry_date` drives the Expiring Soon tab and badge colors (expired / ≤3 days / ≤7 days).
- Status is derived at runtime: `quantity <= 0` → out, `quantity <= low_stock_threshold` → low, else → in.

---

## categories

```sql
CREATE TABLE categories (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  color      VARCHAR(20)  NOT NULL DEFAULT 'teal',
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
```

**Color values:** Tailwind color names — `teal`, `blue`, `amber`, `red`, `purple`, `green`, `orange`, `pink`.

---

## storage_locations

User-defined storage zones (e.g. Fridge, Pantry, Garage).

```sql
CREATE TABLE storage_locations (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  user_id    VARCHAR(36)  NULL,
  name       VARCHAR(100) NOT NULL,
  icon       VARCHAR(50)  NOT NULL DEFAULT 'box',   -- Lucide icon name slug
  color      VARCHAR(30)  NOT NULL DEFAULT 'teal',
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## stock_history

Immutable audit log. A row is written on every quantity change.

```sql
CREATE TABLE stock_history (
  id                  INT PRIMARY KEY AUTO_INCREMENT,
  inventory_item_id   INT           NOT NULL,  -- FK → inventory_items.id
  item_name           VARCHAR(255)  NOT NULL,  -- snapshot at time of change
  previous_quantity   DECIMAL(10,2) NOT NULL,
  new_quantity        DECIMAL(10,2) NOT NULL,
  delta               DECIMAL(10,2) NOT NULL,  -- positive = restock, negative = consumption
  unit                VARCHAR(50)   NOT NULL DEFAULT 'units',
  action              VARCHAR(50)   NOT NULL DEFAULT 'update',  -- 'add'|'remove'|'update'|'create'
  created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);
```

---

## shopping_list_items

```sql
CREATE TABLE shopping_list_items (
  id                INT PRIMARY KEY AUTO_INCREMENT,
  inventory_item_id INT           NULL,  -- FK → inventory_items.id (nullable = manual entry)
  name              VARCHAR(255)  NOT NULL,
  quantity          DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit              VARCHAR(50)   NOT NULL DEFAULT 'units',
  checked           BOOLEAN       NOT NULL DEFAULT FALSE,
  note              TEXT          NULL,
  created_at        TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);
```

---

## user_subscriptions

Tracks Stripe subscription state. One row per user.

```sql
CREATE TABLE user_subscriptions (
  id                              INT PRIMARY KEY AUTO_INCREMENT,
  user_id                         VARCHAR(36)   NULL,          -- BetterAuth user ID
  session_key                     VARCHAR(255)  NOT NULL UNIQUE, -- email or device key (legacy)
  stripe_customer_id              VARCHAR(255)  NULL,
  stripe_subscription_id          VARCHAR(255)  NULL,
  stripe_price_id                 VARCHAR(255)  NULL,
  status                          VARCHAR(50)   NOT NULL DEFAULT 'free',  -- 'free'|'active'|'canceled'
  item_limit                      INT           NOT NULL DEFAULT 50,
  recipe_explorer_active          BOOLEAN       NOT NULL DEFAULT FALSE,
  recipe_explorer_subscription_id VARCHAR(255)  NULL,
  current_period_end              TIMESTAMP     NULL,
  created_at                      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at                      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

**Plan → item_limit mapping:**

| Plan | item_limit |
|---|---|
| Free | 20 |
| Starter ($10/mo) | 100 |
| Growth ($20/mo) | 500 |
| Pro ($30/mo) | 999999 (unlimited) |

---

## alert_preferences

```sql
CREATE TABLE alert_preferences (
  id                INT PRIMARY KEY AUTO_INCREMENT,
  user_id           VARCHAR(36)  NOT NULL UNIQUE,
  alerts_enabled    BOOLEAN      NOT NULL DEFAULT TRUE,
  frequency         VARCHAR(20)  NOT NULL DEFAULT 'instant',  -- 'instant'|'daily'|'weekly'
  last_alert_sent_at TIMESTAMP   NULL,
  created_at        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## households

```sql
CREATE TABLE households (
  id             INT PRIMARY KEY AUTO_INCREMENT,
  owner_user_id  VARCHAR(36)  NOT NULL,
  name           VARCHAR(100) NOT NULL DEFAULT 'My Household',
  created_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## household_members

```sql
CREATE TABLE household_members (
  id            INT PRIMARY KEY AUTO_INCREMENT,
  household_id  INT          NOT NULL,  -- FK → households.id ON DELETE CASCADE
  user_id       VARCHAR(36)  NULL,      -- NULL until invite accepted
  invite_email  VARCHAR(255) NOT NULL,
  invite_token  VARCHAR(64)  NOT NULL UNIQUE,
  role          VARCHAR(20)  NOT NULL DEFAULT 'member',   -- 'owner'|'member'
  status        VARCHAR(20)  NOT NULL DEFAULT 'pending',  -- 'pending'|'accepted'|'declined'
  created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
);
```

---

## ai_usage_log

```sql
CREATE TABLE ai_usage_log (
  id                  INT PRIMARY KEY AUTO_INCREMENT,
  user_id             VARCHAR(36)    NULL,
  feature             VARCHAR(50)    NOT NULL,  -- 'scan'|'search'|'recipe_suggest'|'chat'
  model               VARCHAR(100)   NOT NULL DEFAULT 'gpt-4o-mini',
  prompt_tokens       INT            NOT NULL DEFAULT 0,
  completion_tokens   INT            NOT NULL DEFAULT 0,
  total_tokens        INT            NOT NULL DEFAULT 0,
  estimated_cost_usd  DECIMAL(10,6)  NOT NULL DEFAULT 0,
  created_at          TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);
```

---

## BetterAuth Tables

These tables are created and managed by BetterAuth. Do not modify directly.

### user

```sql
CREATE TABLE `user` (
  id                  VARCHAR(36)  PRIMARY KEY,
  name                VARCHAR(255) NULL,
  email               VARCHAR(255) NOT NULL UNIQUE,
  email_verified      BOOLEAN      DEFAULT FALSE,
  image               TEXT         NULL,
  is_admin            BOOLEAN      DEFAULT FALSE,
  two_factor_enabled  BOOLEAN      DEFAULT FALSE,
  created_at          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### session

```sql
CREATE TABLE `session` (
  id          VARCHAR(36)  PRIMARY KEY,
  expires_at  TIMESTAMP    NOT NULL,
  token       VARCHAR(255) NOT NULL UNIQUE,
  ip_address  VARCHAR(45)  NULL,
  user_agent  TEXT         NULL,
  user_id     VARCHAR(36)  NOT NULL,  -- FK → user.id ON DELETE CASCADE
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### account

```sql
CREATE TABLE `account` (
  id                        VARCHAR(36)  PRIMARY KEY,
  account_id                VARCHAR(255) NOT NULL,
  provider_id               VARCHAR(255) NOT NULL,  -- 'credential'|'google'
  user_id                   VARCHAR(36)  NOT NULL,  -- FK → user.id ON DELETE CASCADE
  access_token              TEXT         NULL,
  refresh_token             TEXT         NULL,
  id_token                  TEXT         NULL,
  access_token_expires_at   TIMESTAMP    NULL,
  refresh_token_expires_at  TIMESTAMP    NULL,
  scope                     TEXT         NULL,
  password                  VARCHAR(255) NULL,      -- bcrypt hash for credential provider
  created_at                TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at                TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### verification

```sql
CREATE TABLE `verification` (
  id          VARCHAR(36)  PRIMARY KEY,
  identifier  VARCHAR(255) NOT NULL,
  value       VARCHAR(255) NOT NULL,
  expires_at  TIMESTAMP    NOT NULL,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### two_factor

```sql
CREATE TABLE `two_factor` (
  id           VARCHAR(36)  PRIMARY KEY,
  secret       VARCHAR(255) NOT NULL,
  backup_codes TEXT         NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,  -- FK → user.id ON DELETE CASCADE
);
```

---

## Entity Relationships

```
user ──────────────────────────────────────────────────────────────────┐
 │                                                                      │
 ├── inventory_items (user_id)                                          │
 │    ├── categories (category_id)                                      │
 │    ├── storage_locations (location_id)                               │
 │    └── stock_history (inventory_item_id)                             │
 │                                                                      │
 ├── user_subscriptions (user_id)                                       │
 ├── alert_preferences (user_id)                                        │
 ├── households (owner_user_id)                                         │
 │    └── household_members (household_id) ── user (user_id)           │
 ├── ai_usage_log (user_id)                                             │
 │                                                                      │
 └── [BetterAuth]                                                       │
      ├── session (user_id) ────────────────────────────────────────────┘
      ├── account (user_id)
      ├── verification
      └── two_factor (user_id)
```