# Admin Panel for Package Form Submissions — Design

**Date:** 2026-06-27
**Status:** Approved (design)
**Owner decisions captured:** API scope, storage, auth, location, submit wiring, file
uploads, mail transport, first-user seeding.

---

## 1. Purpose

The package pages under `packages-html/` all funnel visitors to `checkout.html`, where a
3-step form collects contact details and package-specific answers. Today the final step is
a **stub** — nothing is sent anywhere. There is no backend.

This project delivers:

1. A **Node API** that receives checkout submissions, geolocates the submitter by IP,
   stores everything, and sends two emails per submission (auto-responder to the
   submitter, notification to the owner).
2. A **React admin panel** where owners log in and view all submissions **grouped by
   package**, with the submitter's location and full package/answer details.
3. A small **change to `checkout.html`** to POST real submissions to the API.

Both live under a new top-level `admin/` folder with two subfolders: `admin/api`
(Node) and `admin/web` (React).

---

## 2. Confirmed decisions

| Area | Decision |
|---|---|
| API scope | Lead capture + admin + dual email (auto-responder + admin notify) + configurable email templates & admin notify email. **No Stripe / payments.** |
| Storage | **SQLite** single-file DB (`better-sqlite3`). |
| Admin auth | **Email + password, JWT** sessions. |
| Location | **Server-side IP geolocation** (offline MaxMind GeoLite2 DB). |
| Submit wiring | checkout.html posts to a **configurable absolute API URL**; API allows the btwebgroup.com origin via **CORS**. |
| File uploads | CSV fields are **uploaded and stored** on disk; downloadable from the admin panel. |
| Mail transport | **SendGrid HTTP API** (`@sendgrid/mail`). API key in `.env`; from-address, admin-notify email, and templates configurable in the panel. |
| First user | **Seeded on first run** from env defaults: `admin@btwebgroup.com` / `admin123`. Owner must change password after first login. |

---

## 3. Architecture

```
admin/
  api/                      Node + Express + SQLite
    src/
      server.js             bootstrap: express app, CORS, route mounting, error handler
      config.js             env loading (PORT, JWT_SECRET, SENDGRID_API_KEY,
                            ALLOWED_ORIGIN, seed admin creds, paths)
      db.js                 better-sqlite3 connection + migration runner
      migrations/           ordered .sql (or .js) schema files
      routes/
        submissions.js      POST /api/submissions          (public, from checkout)
                            GET  /api/submissions          (auth; list, grouped/filtered)
                            GET  /api/submissions/:id       (auth; full detail)
                            PATCH /api/submissions/:id      (auth; update status)
                            GET  /api/submissions/:id/file  (auth; download CSV)
        auth.js             POST /api/auth/login
                            POST /api/auth/change-password  (auth)
                            GET  /api/auth/me               (auth)
        settings.js         GET  /api/settings              (auth)
                            PUT  /api/settings              (auth)
      services/
        geo.js              ip -> {city, region, country, lat, lon} via GeoLite2
        mailer.js           SendGrid send; renders templates with token substitution
        templates.js        default template bodies + {{token}} renderer
      middleware/
        auth.js             JWT verify -> req.user
        upload.js           multer: single CSV, size/type limits
      seed.js               create first admin if users table empty
    data/                   app.db, uploads/<id>-<filename>.csv, GeoLite2-City.mmdb
    .env.example
    package.json

  web/                      Vite + React admin panel
    src/
      main.jsx, App.jsx     router + auth guard
      api.js                fetch wrapper (injects JWT, handles 401 -> login)
      auth.js               token storage + context
      tokens.css            BT Web Group design tokens (gold/green/Montserrat)
      pages/
        Login.jsx
        Dashboard.jsx       submissions grouped by package (group cards)
        SubmissionDetail.jsx
        Settings.jsx        admin email, from-address, email templates
      components/           GroupCard, SubmissionRow, StatusBadge, Field, etc.
    index.html
    package.json
```

### 3.1 Data flow

1. Visitor finishes `checkout.html`. The page builds a payload from `state` (contact +
   dynamic package answers + package/tier/price snapshot + pay-vs-callback + assessment
   flag) and POSTs it to `API_URL/api/submissions`.
   - No CSV: `application/json`.
   - With CSV: `multipart/form-data` (JSON payload field + the file).
2. API resolves the submitter's IP (respecting a trusted-proxy header), runs `geo.js`,
   inserts a `submissions` row, saves any uploaded CSV under `data/uploads/`.
3. API fires two emails via SendGrid: auto-responder to the submitter, notification to the
   admin-notify address. Email failures are logged but do **not** fail the submission
   (the lead is already saved); the response still returns success.
4. API returns `201 { id }`. checkout.html shows its existing "done" pane.
5. Owner opens the React panel, logs in (JWT), and browses submissions grouped by
   package, drilling into detail and downloading CSVs.

---

## 4. Database schema (SQLite)

```sql
-- users: admin owners
CREATE TABLE users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  must_change_pw INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- submissions: one row per checkout submission
CREATE TABLE submissions (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  -- package snapshot (from checkout-packages.json at submit time)
  group_key     TEXT NOT NULL,      -- e.g. "service-seo"
  group_label   TEXT NOT NULL,      -- e.g. "SEO — Search Engine Optimization"
  tier_key      TEXT NOT NULL,      -- e.g. "service-seo-growth"
  tier_name     TEXT NOT NULL,      -- e.g. "Growth"
  billing       TEXT NOT NULL,      -- recurring | onetime | ai
  due_today     INTEGER,            -- amount due today (cents or whole $; see note)
  price_snapshot TEXT,              -- JSON: full tier object as shown to user
  -- contact (step 1)
  name          TEXT,
  business      TEXT,
  email         TEXT,
  phone         TEXT,
  -- intent
  action        TEXT,               -- "pay" | "callback"
  assessment    INTEGER,            -- 1 if free assessment kept
  -- dynamic per-package answers (step 2)
  answers       TEXT,               -- JSON object: {fieldName: value|[values]}
  csv_path      TEXT,               -- relative path under data/uploads, or NULL
  csv_original_name TEXT,
  -- geo (from IP)
  ip            TEXT,
  geo_city      TEXT,
  geo_region    TEXT,
  geo_country   TEXT,
  geo_lat       REAL,
  geo_lon       REAL,
  -- meta
  source_url    TEXT,               -- referring page URL if sent
  utm           TEXT,               -- JSON of any utm_* params
  status        TEXT NOT NULL DEFAULT 'new',  -- new | contacted | closed
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_submissions_group ON submissions(group_key);
CREATE INDEX idx_submissions_created ON submissions(created_at);
CREATE INDEX idx_submissions_status ON submissions(status);

-- settings: single-row key/value or one row; stores email config + templates
CREATE TABLE settings (
  id              INTEGER PRIMARY KEY CHECK (id = 1),
  admin_notify_email TEXT,          -- where admin notifications go
  from_email      TEXT,             -- verified SendGrid sender
  from_name       TEXT,             -- e.g. "Brian Evans - BT Web Group"
  autoresponder_subject TEXT,
  autoresponder_body    TEXT,       -- template w/ {{tokens}}
  notify_subject  TEXT,
  notify_body     TEXT,             -- template w/ {{tokens}}
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);
```

**Price note:** `due_today` is stored as the whole-dollar integer the checkout already
uses (prices in `checkout-packages.json` are whole dollars, not cents). No currency
conversion needed; documented to avoid the cents/dollars ambiguity.

`answers` and `price_snapshot` are stored as JSON text and parsed by the API when serving
detail. This keeps the schema stable as package fields change over time — the admin panel
renders whatever keys are present.

---

## 5. API endpoints

### Public
- `POST /api/submissions` — accepts JSON or multipart. Validates required contact fields
  (name, email or phone) and that `tier_key` resolves. Geolocates, stores, emails.
  Returns `201 { id }`. Rate-limited (basic IP throttle) to deter spam.

### Authenticated (JWT in `Authorization: Bearer`)
- `POST /api/auth/login` — `{ email, password }` -> `{ token, mustChangePassword }`.
- `POST /api/auth/change-password` — `{ currentPassword, newPassword }`.
- `GET /api/auth/me` — current user.
- `GET /api/submissions?group=&status=&from=&to=&q=` — list; supports grouping data for
  the dashboard (returns rows; the web groups them, or API returns group counts).
- `GET /api/submissions/:id` — full detail (answers + price_snapshot parsed).
- `PATCH /api/submissions/:id` — `{ status }`.
- `GET /api/submissions/:id/file` — streams the stored CSV as a download.
- `GET /api/settings`, `PUT /api/settings` — email config + templates.

CORS: only `ALLOWED_ORIGIN` (btwebgroup.com, plus localhost in dev) may call
`POST /api/submissions`. Admin endpoints are JWT-gated regardless of origin.

---

## 6. Email

- Transport: `@sendgrid/mail`, `SENDGRID_API_KEY` from `.env`.
- Two messages per submission, both rendered from DB templates with token substitution.
- **Token set** (available in both templates): `{{name}}`, `{{business}}`, `{{email}}`,
  `{{phone}}`, `{{package}}` (group label · tier), `{{tier}}`, `{{price}}` (due today,
  formatted), `{{action}}`, `{{location}}` (city, region, country), `{{date}}`,
  `{{answers}}` (formatted list of the package answers). Unknown tokens render empty.
- **Auto-responder** → submitter (`email`). On-brand confirmation. Must carry BT Web Group
  contact facts from CLAUDE.md §4 and the "modern AI/tech" positioning. Honors CAN-SPAM
  footer (physical address) since it is a triggered transactional email.
- **Admin notify** → `admin_notify_email`. Concise: who, package, due today, location,
  link to the submission in the panel.
- Failures are caught and logged; they never block lead capture.
- Sensible **default templates** ship in `templates.js` so the system works before the
  owner customizes anything.

---

## 7. Geolocation

- `geo.js` loads a bundled **GeoLite2-City.mmdb** at startup (via `maxmind`).
- Resolves the real client IP: trust `X-Forwarded-For` only when behind a known proxy
  (configurable `TRUST_PROXY`); otherwise use the socket IP.
- Returns `{ city, region, country, lat, lon }`; any missing field is null — lookup
  failure never blocks the submission.
- The `.mmdb` file is not committed (license/size); `.env.example` + README document how
  to obtain it (free MaxMind account). A startup warning logs if it is absent, and geo
  fields are simply left null.

---

## 8. checkout.html change

Minimal, paste-safe, preserves current UX:

1. Add `API_URL` to the existing `CO_CONFIG` block (and document it as the one value to
   set per environment).
2. Replace the body of `finish(kind)`: after `collect(panes[2])`, build the payload from
   `state` + step-1 contact, POST to `API_URL/api/submissions`
   (multipart if a CSV file input has a file, else JSON), then show the existing "done"
   pane. Keep the friendly copy. On network error, still show a graceful done state and
   log — do not trap the user.
3. Capture and forward any `utm_*` params and `document.referrer`/page URL for `source_url`
   + `utm`.
4. No `<html>/<head>/<body>`, stays scoped under `.btwx` — Beaver Builder paste-safe rules
   in CLAUDE.md §2.1 hold.

The real file (currently only its name) is sent when present; this is the one place the
checkout must read `input[type=file].files[0]` rather than the filename string it stores
in `state.data`.

---

## 9. Admin panel screens

1. **Login** — email + password. On `mustChangePassword`, force a change-password step.
2. **Dashboard** — submissions **grouped by package** as collapsible group cards (count +
   newest timestamp per group). Filters: status, date range, free-text search. Each row:
   name/business, tier, due today, location (city, country), status badge, time.
3. **Submission detail** — all contact fields, the per-package answers (rendered from the
   `answers` JSON with human labels where resolvable), price snapshot, pay-vs-callback,
   assessment flag, IP-derived location (city/region/country, optional map link), CSV
   download button, and a status selector (new / contacted / closed).
4. **Settings** — admin notify email, from-name/from-email, and editable auto-responder
   + admin-notify subject/body templates with a visible list of available `{{tokens}}`.

Styling reuses the BT Web Group tokens (Montserrat, gold primary / green secondary, light
surfaces) so the internal tool looks like part of the brand, but it is a normal SPA — not
subject to the Beaver Builder paste-safe constraints (those apply only to the public
pages).

---

## 10. Security & operational notes

- Passwords hashed with **bcrypt**; JWT signed with `JWT_SECRET` from `.env`.
- `admin123` seed is a known weak default → `must_change_pw=1` forces a reset on first
  login; README calls this out.
- Public submission endpoint: basic IP rate limiting + payload size cap + CSV
  type/size validation (multer) to limit abuse.
- Uploaded CSVs stored outside the web root, served only through the authenticated
  download route.
- `.env`, `data/app.db`, `data/uploads/`, and `*.mmdb` are git-ignored.
- `.env.example` documents every variable. README in `admin/` covers setup, seeding, and
  the one `API_URL` value to set in checkout.html.

---

## 11. Out of scope (YAGNI)

- Stripe / real payment processing (checkout pay path stays a stub/POST-only).
- Multi-user management, roles, invites (single seeded owner; password change only).
- Analytics dashboards, charts, exports beyond per-submission CSV download.
- Editing `checkout-packages.json` from the panel.
- Browser GPS geolocation.
```