ADR-0018: Workflow, migration & reporting

Status: Proposed Date: 2026-05-31 Deciders: Ramón Kamibayashi + Claude Opus 4.7 Related: ADR-0010, ADR-0013, ADR-0014, ADR-0015, ADR-0016, ADR-0017

Story: Three small clusters that round out the operator experience: - Workflow (J) — staged changes, scheduled apply, approvals, git-style branches. - Migration (L) — bring sites IN (from ZIP / fresh install / DNS auto-setup). - Reporting (M) — generate the customer-facing artifacts (PDF report, public status page, feedback inbox).

---

J — Workflow

#	Feature	UX
J1	Staged changes viewer	The pending-changes modal (from the unified flow) already lists changes per category. Promote it to a standalone page /sites/[slug]/staging that's the operator's "review before apply" surface. Show diffs for files (unified diff per file), DB before/after for option/post changes, plugin/theme deltas. Same content the modal shows, but full-screen with searchable file list and key bindings.
J2	Scheduled apply	Operator picks a time ("apply pending at 02:00 tonight" or "every Monday 03:00"). Cron-fired job runs the apply at that time. Useful for maintenance windows.
J3	Approval workflow	Optional toggle per site: "Require approval before apply to prod". When ON, the apply request creates an approvals row → operator (or client) must approve from a magic-link email/Telegram → only then runs. Slack/Telegram integration reuses ADR-0010 alert wiring.
J4	Git-style branches	The worktree is already a git repo. Expose multiple "branches" — operator works on an experimental branch without touching main. On confidence, cherry-pick or merge to main then apply. Requires lightweight branch UI + branch-switching that preserves the docker stack mount.

Data model (alembic 025)

CREATE TABLE scheduled_applies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id uuid REFERENCES sites(id) ON DELETE CASCADE,
  scheduled_for timestamptz NOT NULL,
  cron_expr text,                     -- NULL = one-shot
  categories jsonb DEFAULT '["files","quarantine","db"]'::jsonb,
  created_by text,
  status text NOT NULL,               -- "pending|fired|cancelled"
  created_at timestamptz DEFAULT now(),
  fired_at timestamptz,
  result jsonb                         -- apply result for telemetry
);

CREATE TABLE approvals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id uuid REFERENCES sites(id) ON DELETE CASCADE,
  apply_request jsonb NOT NULL,        -- pending categories + summary
  approver text,                       -- email / telegram username
  token text UNIQUE NOT NULL,          -- magic-link
  status text NOT NULL,                -- "pending|approved|rejected|expired"
  expires_at timestamptz NOT NULL,
  decided_at timestamptz,
  apply_push_ids jsonb                  -- populated on approval
);

CREATE TABLE site_branches (
  site_id uuid REFERENCES sites(id) ON DELETE CASCADE,
  name text NOT NULL,                   -- "main", "exp-update-yoast", ...
  base_commit text,                     -- git rev
  created_at timestamptz DEFAULT now(),
  PRIMARY KEY (site_id, name)
);

Also sites.require_approval boolean default false, sites.current_branch text default 'main'.

API

Scheduled

• POST /api/v1/sites/{slug}/scheduled-applies body {scheduled_for, cron_expr?, categories?} → 201 with id.
• GET /api/v1/sites/{slug}/scheduled-applies → list pending + fired.
• DELETE /api/v1/sites/{slug}/scheduled-applies/{id} → cancel.
• Cron scheduled_applies_tick (extend health_cron_tick) fires due rows.

Approval

• PATCH /api/v1/sites/{slug} body {require_approval: bool}.
• When apply-all-to-prod hits a site with require_approval=true: creates approval row, sends magic-link via Telegram/email, returns 202 with approval_id.
• Public GET /api/v1/approvals/{token} (no auth) → renders a small page with summary + Approve/Reject buttons.
• POST /api/v1/approvals/{token}/decide body {decision} → executes the apply on approval.

Branches

• GET /api/v1/sites/{slug}/branches → list.
• POST /api/v1/sites/{slug}/branches body {name, from_branch?} → creates branch.
• POST /api/v1/sites/{slug}/branches/{name}/switch → checks out, updates sites.current_branch, restarts docker stack against the worktree at the new branch.
• POST /api/v1/sites/{slug}/branches/{name}/merge-to-main → fast-forward (or 3-way) merge.

UI

• New page /sites/[slug]/staging (J1).
• Section in SiteHero showing "Scheduled apply: tonight 02:00 [Cancel]" (J2).
• Settings toggle "Require approval before apply" + history of approvals (J3).
• Branch switcher in TopBar (when on a site page) showing current branch + dropdown (J4).
• Components: StagingPage.tsx, ScheduledApplyCard.tsx, ApprovalsPage.tsx, ApprovalDecidePage.tsx (public, no chrome), BranchSwitcher.tsx.

Edge cases

• Scheduled apply fires but worktree has been mutated since scheduling → ABORT, log to result, alert operator. Don't apply blindly.
• Approval expires before decide → auto-reject.
• Branch switch with dirty worktree → refuse OR stash. v1: refuse with clear error.

---

L — Migration

#	Feature	UX
L1	Migration import from ZIP	Operator uploads a Duplicator / All-in-One Migration / UpdraftPlus ZIP → WP-Pulse extracts → creates a new site + worktree pre-populated. Final step: operator points the prod hostname at WP-Pulse-managed hosting and pushes.
L2	Empty WP install wizard	Provision a fresh WP install in a new local stack (without pulling from anywhere). Operator picks WP version, theme, starter plugins. Use case: developer starting a new project locally before pushing to a chosen host.
L3	DNS auto-setup	Operator gives a domain → WP-Pulse hits Cloudflare API → creates A record + tunnel + Caddy snippet automatically. Saves the 4 manual steps it takes today. Same flow during L1 import + L2 fresh install.

Data model

Reuse existing sites table. New columns: - sites.import_status text — "imported|fresh|enrolled" (legacy default "enrolled") - sites.import_metadata jsonb — source ZIP filename, size, plugin counts, etc.

(alembic 026)

API

• POST /api/v1/sites/import-zip multipart {file} → 202 + job_id. Background job extracts, validates (must contain a SQL dump + wp-content), creates the site row + worktree.
• GET /api/v1/sites/imports/{job_id} → status.
• POST /api/v1/sites/create-fresh body {slug, wp_version, theme, plugins: [], domain?} → provisions.
• POST /api/v1/dns/setup body {domain, target_ip?} → CF API call.

UI

• New page /onboard (extend existing) with three tabs:
• "Enroll existing" (today's flow)
• "Import ZIP" (L1)
• "Fresh install" (L2)
• DNS step in each that calls L3.
• Components: ImportZipDropzone.tsx, ImportProgressPanel.tsx, FreshInstallWizard.tsx, DnsSetupStep.tsx.

Edge cases

• Importer must validate plugin list against the agent compatibility (some legacy plugins won't run on PHP 8.x — flag).
• Duplicator ZIP format vs AIO Migration format — different layouts. Detect by file presence.
• DNS setup: refuse if domain already exists in CF; show conflict.

---

M — Reporting

#	Feature	UX
M1	Monthly client report PDF	First of each month, cron generates one PDF per site covering: uptime %, push count, plugin updates applied, security patches, performance trend (Lighthouse), backup count. Auto-emails to the client contact OR offers download.
M2	Public status page per site	status.client-domain.com rendered by WP-Pulse with: uptime stat, last maintenance, next scheduled apply. Read-only, no auth. Branded with client logo.
M3	Customer feedback inbox	Optional JS widget that sites can embed (<script src="https://wp-pulse-app.monxas.casa/widget.js?slug=...">) — shows a small "Report a bug" button. Submissions land in operator's WP-Pulse dashboard, tagged to the site.

Data model (alembic 027)

CREATE TABLE site_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id uuid REFERENCES sites(id) ON DELETE CASCADE,
  period_start date NOT NULL,
  period_end date NOT NULL,
  format text NOT NULL,         -- "pdf|html"
  artifact_path text NOT NULL,  -- on disk
  generated_at timestamptz DEFAULT now(),
  emailed_to text
);

CREATE TABLE site_feedback (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id uuid REFERENCES sites(id) ON DELETE CASCADE,
  submitted_at timestamptz DEFAULT now(),
  url text,
  user_agent text,
  message text NOT NULL,
  screenshot_b64 text,
  resolved boolean DEFAULT false
);

CREATE TABLE site_status_pages (
  site_id uuid PRIMARY KEY REFERENCES sites(id) ON DELETE CASCADE,
  enabled boolean DEFAULT false,
  hostname text UNIQUE,         -- "status.client.com"
  brand_logo_url text,
  brand_primary_color text
);

API

• POST /api/v1/sites/{slug}/reports/generate?month=YYYY-MM → 202 + job.
• GET /api/v1/sites/{slug}/reports → list.
• GET /api/v1/sites/{slug}/reports/{id}/download → PDF file.
• POST /api/v1/sites/{slug}/status-page body {enabled, hostname, brand_logo_url, brand_primary_color} → enables.
• Public GET /api/v1/public/status/{hostname} → status JSON (used by the status page's frontend).
• POST /api/v1/widget/feedback body {slug, url, ua, message, screenshot_b64?} → CORS-allowed from *.{enrolled hostname}.
• GET /api/v1/sites/{slug}/feedback → operator-facing inbox.

UI

• /sites/[slug]/reports → list + generate-now button.
• /sites/[slug]/feedback → inbox.
• /sites/[slug]/status-page → config + preview.
• Public route /public/status/[hostname]/page.tsx → renders the status page (different layout, no app chrome).
• Components: ReportsList.tsx, FeedbackInbox.tsx, StatusPageConfig.tsx, PublicStatusPage.tsx.

PDF generation

Use weasyprint (Python) or puppeteer for HTML→PDF. Existing infra has Playwright (used by F4 visual diff) — reuse it for HTML→PDF render. Template via Jinja2 in templates/jinja/client_report.html.j2.

Cron

reports_monthly_generate — fires 1st of each month 06:00 for every site with reports enabled (sites.report_enabled boolean default false).

Edge cases

• Status page hostname needs DNS + CF tunnel route. Lean on ADR-0018 L3 DNS auto-setup if available; otherwise document manual setup.
• Feedback widget CORS: only accept submissions from a hostname enrolled with that slug.
• PDF size: cap at ~5 MB. If report would exceed, paginate or strip charts.

---

Migration consolidation

Alembic 025 (workflow), 026 (migration columns), 027 (reporting). Independent chains; can be applied in any order.

Rollout — 3 sub-agents in parallel

• Sub-agent J: workflow features (staging page, scheduled, approvals, branches).
• Sub-agent L: migration (import-ZIP, fresh-install, DNS setup).
• Sub-agent M: reporting (PDF, status page, feedback inbox).

No shared file ownership across the three. Each one owns its own alembic revision + routers + UI pages.

Open questions

1. Branches vs worktree complexity: J4 is the riskiest because switching branches mid-stack is non-trivial (docker mount + DB state). v1 could limit to file-only branches; DB branches (snapshot + restore) deferred.
2. PDF report ML/AI augmentation: e.g. AI summary of the month. Out of scope (ADR-N section deferred).
3. Status page CDN caching: serve from CF cache with 1-min TTL.
4. Migration ZIP parsing fragility: Duplicator ZIPs vary. v1: support AIO Migration format (most common) + UpdraftPlus. Duplicator deferred.