# ShipRex CMS/API — Database Guide

> MySQL via **Sequelize** (ORM + migrations). This guide is the source of truth
> for the schema, the conventions, and **exactly how to add or change columns**.
> Every schema change is a **migration** that mirrors a **model** change — they
> ship together in the same commit.

---

## 1. Conventions (every table follows these)

| Rule | Value |
|------|-------|
| Naming | tables `snake_case` plural (`kb_articles`); columns `snake_case` |
| `underscored` | **true** globally (`src/config/database.js` + `config/config.js`) |
| Charset / collation | `utf8mb4` / `utf8mb4_unicode_ci` |
| Primary key | `id` — `INTEGER.UNSIGNED` autoincrement (or `BIGINT.UNSIGNED` for high-volume rows: messages, logs, replies) |
| Timestamps | `created_at` / `updated_at` on every table (Sequelize manages them) |
| Booleans | `BOOLEAN` with explicit `defaultValue` |
| Enums | `ENUM(...)` for closed sets (status, role, priority) |
| JSON blobs | `JSON` column (`meta`) for loose, non-queried structure |
| Money | *(none yet)* — store **minor units as INTEGER**, never floats, when introduced |
| Foreign keys | `<entity>_id`, with `onDelete` set deliberately (`CASCADE` for children, `SET NULL` for soft links) |

There is **no separate ORM config** to keep in sync: the runtime app uses
`src/config/database.js`; the Sequelize CLI uses `config/config.js`. Both read the
same `.env`, so there is one source of truth for credentials.

---

## 2. Current schema (10 tables + runtime `sessions`)

```
admin_users ─┬─< kb_articles (author_id)            kb_categories ─< kb_articles (category_id)
             ├─< support_tickets (assigned_admin_id)
             ├─< ticket_replies (admin_id)
             └─< contact_requests (handled_by_id)

api_clients ─< chat_conversations (api_client_id) ─< chat_messages (conversation_id)  [CASCADE]
support_tickets ─< ticket_replies (ticket_id)  [CASCADE]
email_logs        (standalone audit; loose related_type/related_id link)
```

| Table | Purpose | Key columns | Notable |
|-------|---------|-------------|---------|
| `admin_users` | portal logins | `email`(uniq), `password_hash`, `role` enum, `is_active` | `defaultScope` hides `password_hash`; `withSecret` scope reveals it |
| `api_clients` | website/integrator API keys | `key_prefix`, `key_hash`(uniq, SHA-256), `scopes` CSV, `is_active` | plaintext key shown once; only the hash is stored |
| `kb_categories` | KB taxonomy | `name`, `slug`(uniq), `sort_order`, `is_active` | |
| `kb_articles` | KB content | `slug`(uniq), `body_md`, `body_text`, `status` enum, `is_chatbot_visible` | **FULLTEXT** index `ft_kb_articles(title, body_text)` for chatbot retrieval |
| `chat_conversations` | chatbot threads | `public_id`(uniq), `channel` enum, visitor fields, `meta` JSON | |
| `chat_messages` | chatbot turns | `role` enum, `content`, `kb_article_ids`, `tokens` | `BIGINT` id; CASCADE on conversation |
| `contact_requests` | "call me back" leads | contact fields, `reason`/`status` enums, `brevo_contact_id` | synced to Brevo |
| `email_logs` | transactional email audit | `to_email`, `subject`, `type`, `status` enum, `error` | written by `sendAndLog` |
| `support_tickets` | client tickets | `reference`(uniq, `SRX-…`), requester fields, `category`/`priority`/`status` enums, `meta` JSON | |
| `ticket_replies` | ticket thread | `author_type` enum, `body`, `is_internal_note` | internal notes never returned via public API; CASCADE on ticket |
| `sessions` | admin sessions | managed by `connect-session-sequelize` | created at runtime, **not** in a migration |

The authoritative column-by-column definition is the initial migration:
`migrations/20260101000000-init-schema.js`. Read it before changing anything.

---

## 3. Model ↔ migration lockstep (the rule)

- The **model** (`src/models/<x>.js`) is the **runtime** truth (what the app reads/writes).
- The **migration** (`migrations/*.js`) is the **deploy-time** truth (what builds the DB).
- They must **always agree**. A column that exists in one but not the other is a
  bug that ships broken to cPanel (where we never use `sync()` — migrations only).
- **Never** rely on `sequelize.sync()` to alter production. The only auto-created
  table is `sessions` (by the session store).

> When you add a column, you edit **both** the model and a **new** migration in the
> same commit. When in doubt, run the migration on a scratch DB and diff
> `SHOW CREATE TABLE` against the model.

---

## 4. How to ADD A COLUMN (step by step)

Example: add `seo_title VARCHAR(160) NULL` to `kb_articles`.

**1. Create the migration**
```bash
npx sequelize-cli migration:generate --name add-seo-title-to-kb-articles
# → migrations/<timestamp>-add-seo-title-to-kb-articles.js
```

**2. Write `up`/`down`** (reversible):
```js
'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('kb_articles', 'seo_title', {
      type: Sequelize.STRING(160),
      allowNull: true,
      after: 'title', // MySQL-only nicety; optional
    });
  },
  async down(queryInterface) {
    await queryInterface.removeColumn('kb_articles', 'seo_title');
  },
};
```

**3. Mirror it in the model** (`src/models/kbArticle.js`):
```js
seo_title: { type: DataTypes.STRING(160), allowNull: true },
```

**4. Surface it** where relevant: the service (read/write), the zod schema in the
controller (validation), the admin form/show EJS, and the **Postman** example if
it appears in an API response.

**5. Run & verify**
```bash
npx sequelize-cli db:migrate
node -e "require('dotenv').config(); require('./src/models'); console.log('models OK')"
```

**6. Commit** model + migration + surface changes together:
`PRP-XXX Task N: add seo_title to kb_articles`.

---

## 5. How to CHANGE / RENAME / DROP a column

> ⚠️ Altering or dropping columns is **destructive**. Prefer additive changes.
> For renames, prefer **add-new → backfill → drop-old** across two deploys rather
> than an in-place rename, so a rollback never loses data.

**Change a column's type/nullability** (`changeColumn`):
```js
async up(queryInterface, Sequelize) {
  await queryInterface.changeColumn('contact_requests', 'phone', {
    type: Sequelize.STRING(60), allowNull: false,
  });
},
async down(queryInterface, Sequelize) {
  await queryInterface.changeColumn('contact_requests', 'phone', {
    type: Sequelize.STRING(40), allowNull: true,
  });
},
```

**Rename a column** (`renameColumn`) — only when you're sure nothing else reads
the old name yet:
```js
await queryInterface.renameColumn('kb_articles', 'excerpt', 'summary');
```

**Change an ENUM** (MySQL): the safe path is `changeColumn` with the full new enum
list. Adding a value:
```js
await queryInterface.changeColumn('support_tickets', 'status', {
  type: Sequelize.ENUM('open','in_progress','waiting_customer','resolved','closed','reopened'),
  allowNull: false, defaultValue: 'open',
});
```
Update the model's enum **and** every place that switches on it (services, EJS
badges, Postman docs). Removing an enum value requires migrating existing rows
off it first.

**Drop a column** (last resort): `removeColumn` in `up`, re-`addColumn` in `down`
(a true rollback can't restore data — document the loss in the PRP's Rollback).

**Indexes**
```js
await queryInterface.addIndex('contact_requests', ['status']);              // btree
await queryInterface.addIndex('kb_articles', {                              // fulltext
  name: 'ft_kb_articles', fields: ['title', 'body_text'], type: 'FULLTEXT',
});
await queryInterface.removeIndex('kb_articles', 'ft_kb_articles');
```
> **FULLTEXT** is InnoDB/MySQL 5.6+. KB chatbot search uses it and **falls back to
> `LIKE`** if missing (`kb.service.js`), so a missing index degrades, not breaks.

---

## 6. Adding a NEW TABLE (new module)

1. Model file `src/models/<entity>.js` (`(sequelize, DataTypes) => Model`).
2. Require + register it in `src/models/index.js`, and declare associations there.
3. Migration with `createTable` (copy the style and column order from
   `20260101000000-init-schema.js`: `id`, FKs with `references/onDelete`, fields,
   enums, `created_at`/`updated_at`, then `addIndex`).
4. Add FK indexes and any search indexes.
5. `down` drops the table (respect dependency order if multiple).
6. Optional seeder for sample/bootstrap rows (idempotent — check existence first,
   like `seeders/20260101000100-bootstrap.js`).

---

## 7. Running migrations & seeders

```bash
npm run db:migrate          # apply pending migrations
npm run db:migrate:undo     # roll back the last migration
npm run db:seed             # run all seeders
npm run db:seed:undo        # undo seeders
npm run db:reset            # undo all → migrate → seed   (DEV ONLY — destroys data)
```

On **cPanel**, run these over SSH inside the Node virtualenv with `.env` present
(it supplies DB creds to the CLI) — see `DEPLOY-GITHUB-CPANEL.md` Phase 9.
Migrations are tracked in the `sequelizemeta` table, so re-running is a no-op for
already-applied ones (safe on every deploy).

### Scratch DB for testing a migration
```bash
node -e "const m=require('mysql2/promise');m.createConnection({host:'127.0.0.1',user:'root',password:''}).then(async c=>{await c.query('CREATE DATABASE IF NOT EXISTS shiprex_cms_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');await c.end();})"
DB_NAME=shiprex_cms_test DB_USER=root DB_PASSWORD= npx sequelize-cli db:migrate
# …verify…
DB_NAME=shiprex_cms_test DB_USER=root DB_PASSWORD= npx sequelize-cli db:migrate:undo:all
```

---

## 8. Data-change checklist (paste into the PRP task)

- [ ] New **migration** created (`up` **and** reversible `down`).
- [ ] **Model** updated to match the migration exactly (type, null, default, enum).
- [ ] Associations updated in `src/models/index.js` (if FKs changed).
- [ ] Service reads/writes the new field; zod schema validates it.
- [ ] Admin EJS form/show updated; Postman example updated if it's in an API response.
- [ ] Migration runs clean on a scratch DB; `down` reverses it.
- [ ] Model + migration + surface changes in **one commit**.
- [ ] Destructive changes (drop/rename/enum-shrink) documented in the PRP Rollback.

---

## 9. Anti-patterns

- ❌ Changing a model without a migration (works locally via cache, breaks on deploy).
- ❌ Using `sync({ alter: true })` against a real DB.
- ❌ In-place rename/drop without a backfill/rollback plan.
- ❌ Floats for money (use integer minor units).
- ❌ Storing secrets/PII unhashed (keys are SHA-256; passwords are bcrypt).
- ❌ Raw string interpolation into SQL (use Sequelize or `replacements`).
- ❌ Forgetting FK `onDelete` semantics (orphans or accidental cascades).