# Funnelz — Software Specification **Document purpose:** This is a build-ready specification intended as a handoff document for an AI coding model (Claude Sonnet 4) to implement. It defines scope, architecture, data model, security posture, and feature behaviour in enough detail that implementation decisions are unambiguous. **Product name:** Funnelz **One-line pitch:** A multi-user, AI-assisted funnel and landing-page builder that lets anyone go from an idea to a live, hosted, editable sales funnel in minutes — with a drag-and-drop editor for full manual control. --- ## 1. Vision & Product Summary Funnelz lets a user describe a funnel in plain language ("a 3-page webinar signup funnel for a nutrition coach"), and an AI pipeline: 1. Plans the funnel structure (which pages, in what order, what each page's job is). 2. Searches the web for existing, freely-usable page designs that match the funnel's purpose and vibe, and extracts reusable style patterns (colour palette, layout rhythm, typography, component shapes) from them — **never copying copyrighted content or code verbatim**, only derived style signals. 3. Generates a complete, editable funnel: pages, copy, layout, and a backing MySQL schema for capturing leads/orders relevant to that funnel type. 4. Publishes it instantly to a hosted, shareable URL. From there, the user can hand-edit every page in a drag-and-drop, block-based editor (Quill.js for rich text, a custom canvas/grid system for layout), re-run AI assist on individual sections ("rewrite this headline", "make this page more urgent"), manage the funnel's data (leads captured, submissions), and share the funnel — as an editable project or a live link — with specific teammates or publicly within the organisation. Security is a first-class constraint throughout, not an add-on: every design decision below is made with the assumption that Funnelz will hold other people's personal data (names, emails, phone numbers, payment intents) and must defend it accordingly. --- ## 2. Technology Stack | Layer | Choice | Notes | |---|---|---| | Server language | PHP 8.2+ | No heavyweight framework required; a small in-house routing/service layer (see §9) keeps it portable to shared hosting, but structured like a framework internally (PSR-4 autoloading, dependency container, service classes) | | Database | MySQL 8.0+ / MariaDB 10.6+ | InnoDB only, utf8mb4, strict SQL mode | | Front end | HTML5, CSS3, vanilla JavaScript (ES2020+) | No build step required to run; a light bundler (esbuild) is used in development for the editor bundle only | | Rich text editing | Quill.js 2.x | Used inside the drag-and-drop editor for all text blocks | | AI provider | OpenAI (GPT-4o / GPT-4o-mini for cost-sensitive calls) | Server-side only — API key never touches the browser | | Page rendering | Server-side PHP templating from stored JSON page specs | No client framework needed for the *published* funnel pages — they must load fast and work with JS disabled where possible | | Hosting model | Single application, shared URL + slug/payload | See §8 | | Sessions/auth | PHP native sessions + `password_hash` (bcrypt/argon2id), CSRF tokens, optional 2FA (TOTP) | | | Asset storage | Local filesystem under a non-web-root `storage/` directory, served via a signed PHP endpoint OR object storage (S3-compatible) if available | Never store user uploads directly inside the web root | | Email | PHPMailer via SMTP (transactional: verification, sharing invites, lead notifications) | | | Queue/background jobs | A simple DB-backed job table + a cron-triggered worker script (`cron/run_jobs.php`), processing jobs in short, resumable steps | Required for shared hosting: no Redis/RabbitMQ, no daemon — see §2.1 for the concrete constraints this is designed around. The job table is structured so a real queue could be swapped in later if the app ever moves to a VPS/cloud host | **Design constraint:** the stack must run on modest/shared hosting (no root shell access assumed, no long-running daemons guaranteed) while still being structured cleanly enough to migrate to a VPS/cloud environment later without a rewrite. Where a "nice to have" (Redis cache, S3, real queue) isn't available, the spec always defines a filesystem/MySQL fallback. ### 2.1 Shared Hosting Compatibility — Hard Requirement Funnelz **will be deployed on typical shared/cPanel-style hosting**, not a VPS or cloud platform. This is not a fallback scenario — it is the actual target environment, and every part of this spec is written to work within its constraints. Concretely, that means: - **No persistent daemons or long-running processes.** No Node server, no websocket server, no Redis/RabbitMQ, no headless-browser service. Every background operation (AI generation, style fetching, cache warming, email sending) is implemented as a short PHP script invoked either by an HTTP request or by cron, and each invocation must complete well within the host's `max_execution_time` (commonly 30–60s on shared plans). - **Cron granularity is coarse.** Shared hosts typically offer cron no finer than every 1–5 minutes (cPanel's minimum is usually 1 minute, but many budget hosts restrict to 5+). The job queue (§9, `ai_jobs` table) is therefore designed around **small, resumable steps** rather than one long job: a "generate funnel" request is broken into per-page jobs, each processed in a single cron tick, so no single OpenAI call chain risks exceeding the execution-time limit. The cron worker (`cron/run_jobs.php`) processes a small batch (e.g. up to N jobs or T seconds, whichever comes first) and exits cleanly, picking up where it left off on the next tick. - **No headless browser / no screenshot rendering.** The original description of colour extraction "from a screenshot" is revised (see §3.3 below): style extraction works entirely from fetched HTML/CSS text via `curl`/`file_get_contents`-style requests and CSS parsing — never by rendering a page with Chromium/Puppeteer, which is not available on shared hosting. - **No shell-out dependencies.** The app never calls `exec()`, `shell_exec()`, or `proc_open()` for core functionality (many shared hosts disable these entirely for security). Image handling uses the PHP `gd` extension (near-universally available on shared hosting) rather than Imagick or CLI tools; PDF/zip handling (if ever needed) uses pure-PHP libraries, not system binaries. - **Baseline PHP extensions only.** The stack is built assuming only what's near-universally enabled on shared cPanel hosting: `pdo_mysql`, `curl`, `mbstring`, `openssl`, `json`, `gd`, `fileinfo`. Nothing in the spec requires a custom PHP build, a PECL extension, or root-level `php.ini` changes — only `.htaccess`/per-directory `php.ini` overrides where the host allows them (e.g. raising `upload_max_filesize`). - **Sessions and locking via filesystem/MySQL, not Redis.** PHP's native file-based session handler is the default; if the host's session directory has issues (common on some shared clusters), a DB-backed session handler (a `sessions` table) is the documented fallback. Any operation needing a lock (e.g. preventing two cron ticks double-processing the same job) uses `SELECT ... FOR UPDATE` / `GET_LOCK()` in MySQL rather than a Redis/semaphore-based lock. - **Output caching is file-based**, written under `storage/cache/`, keyed and invalidated as described in §8.2 — no dependency on an external cache service. - **Respect hosting quotas.** Disk usage (uploads, page-version history, cache, logs) and MySQL row/size limits common on shared plans are treated as real constraints: version history and AI job logs older than a configurable retention window are prunable (see §11 addition below), asset uploads are size-capped at intake, and the cache directory is periodically swept by the same cron worker. - **Single database, single web-root deployability.** The entire application deploys as a normal PHP codebase uploaded via FTP/git-to-cPanel/File Manager, with one MySQL database — no multi-service orchestration, no Docker requirement (though a `Dockerfile`/`docker-compose.yml` for local development only is fine to include, as long as it isn't required for production). - **Outbound HTTP calls (OpenAI, style-source fetches) use `curl` with explicit timeouts** (e.g. 10–15s connect/read) so a slow external host can never itself blow the PHP execution-time budget — see the revised Style Discovery flow below, which is chunked into cron-driven steps for the same reason. Every other section of this document should be read with these constraints as binding, not aspirational. --- ## 3. Core User-Facing Features ### 3.1 AI Easy-Setup (funnel generation wizard) - Chat-style or form-style intake: purpose, target audience, offer, number of steps (or "let AI decide"), tone, brand colours/logo (optional upload), and any reference URL the user likes. - AI Planning step (OpenAI call #1): produces a **Funnel Plan** — an ordered list of pages, each with a role (`landing`, `opt_in`, `sales`, `checkout`, `upsell`, `thank_you`, `webinar_registration`, etc.), goal, and the data fields it needs to capture. - Style Discovery step (see §3.3): finds reference designs and produces a **Style Brief** (palette, font pairing, layout density, imagery style). - Content + Layout Generation step (OpenAI call #2, one call per page or batched): produces each page as a structured **Page Spec JSON** (see §6.3) — sections, blocks, copy, image prompts/placeholders — conforming to the Style Brief. - Database Generation step: derives a lead/submission schema per funnel automatically (see §6.4) from the fields each page collects, and provisions it without the user writing SQL. - Review screen: shows the generated funnel as a preview slideshow before publishing; user can regenerate any single page, or accept and land straight in the editor. - All AI steps are resumable/retryable and run as background jobs with progress polling, so a slow OpenAI response never times out a shared-hosting PHP request. ### 3.2 Drag-and-Drop Page Editor - Canvas-based editor operating on the Page Spec JSON, not raw HTML — this is what makes AI-editing and manual-editing interoperable (both read/write the same structured spec). - Block types: Heading, Paragraph (Quill-powered rich text), Image, Button/CTA, Form (field-builder with validation rules), Video embed, Countdown timer, Testimonial, Icon list, Divider/Spacer, Columns/Grid container, Custom HTML (sandboxed, escaped, admin-gated). - Drag to reorder sections and blocks; resize columns; per-block style panel (colour, spacing, font size/weight, border radius, shadow, animation-on-scroll toggle). - Global "Theme" panel: brand colours, font pairing, button style — changes propagate to all blocks using theme tokens instead of hard-coded values (a block can still override). - Inline "Ask AI" affordance on every block/section: user highlights a block and requests changes in natural language ("make this punchier", "translate to Spanish", "shorten to one sentence") which calls OpenAI with just that block's content and constraints, returns updated content into the same block schema. - Autosave (debounced) + explicit "Save version" checkpoints; full version history with restore (see §3.6). - Responsive preview toggle (desktop/tablet/mobile) — the block system stores per-breakpoint overrides. - Undo/redo stack client-side, backed by periodic server snapshots so history survives a reload. ### 3.3 Style Discovery ("look at existing pages and copy their style") This is explicitly scoped as **style inspiration, not content copying**, for both legal and product-quality reasons. - Given the funnel purpose/industry, the system queries a curated set of sources known to offer freely-usable design references: - Open template galleries (e.g., open-source landing page kits, Google Fonts pairings, Coolors palettes, Unsplash/Pexels for imagery under permissive licences). - The user's own supplied reference URL, if given. - A **Style Extraction** service fetches candidate pages via `curl` (HTML + linked CSS, no rendering) and derives a structured **Style Signature** purely from parsed text: colour palette (by tallying hex/`rgb()`/named-colour values found in inline styles and linked stylesheets and picking the dominant/most-frequent set — no screenshot or headless-browser rendering, per the shared-hosting constraint in §2.1), font families declared in `font-family` rules (matched against a bundled Google Fonts name list), spacing/density heuristics (derived from common `margin`/`padding`/`gap` values in the parsed CSS), button shape/radius (from `border-radius`/`box-shadow` rules on button-like selectors), and a general layout archetype guessed from structural HTML patterns (single column, hero+grid, split-screen, etc.). Because a full style-discovery pass may fetch and parse several candidate pages, it runs as its own `ai_jobs` entry, processed by the cron worker one source per tick rather than in a single request, consistent with §2.1. - The extractor **never stores or reproduces the source page's HTML/CSS/text/images verbatim** — only the derived numeric/categorical signature (hex codes, font names, spacing scale, layout archetype label) is persisted and passed to the content-generation AI as style constraints. This keeps the feature squarely on the "inspired by" side of the copyright line and avoids importing arbitrary third-party code into a user's live page. - Where a source's licence is unclear (most commercial sites), only the style signature is used, never any asset. Where an asset source explicitly grants reuse (Unsplash/Pexels licence, open template's stated licence), the asset can be imported directly, with attribution stored alongside it. - User can also point Style Discovery at a URL and say "match this style" — same pipeline, single source. - This whole subsystem is implemented as a swappable `StyleSourceProvider` interface so new libraries can be added without touching the editor or AI layers. ### 3.4 Hosted, On-the-Fly Funnel Rendering See §8 for full detail. In short: funnels are not exported to static files by default. A single route (e.g. `https://funnelz.app/f/{payload}`) resolves `{payload}` to a funnel+page record, loads the Page Spec JSON, and server-side renders it to HTML on each request (with an output cache layer). This means editing a live page takes effect immediately with no redeploy step, and every published page is one row update away from changing. ### 3.5 Multi-User, Sharing & Permissions - Every funnel belongs to an **Owner** (a user) and optionally an **Organisation** (a team of users), so the product works for solo users and small teams from day one. - Sharing modes per funnel: - **Private** (owner only). - **Shared with specific users** — invite by email or existing username, with a role: `viewer`, `editor`, `admin` (admin can re-share/change permissions, editor can edit content, viewer can view the editor read-only and see submitted lead data if granted). - **Organisation-wide** — visible/editable (per role) to every member of the owner's organisation. - **Public link (view-only of the editor/preview)** — separate from the funnel being live; a *published* funnel's actual landing pages are always publicly reachable at their hosted URL by design (that's the point of a funnel), but the *editor* and *lead data* stay access-controlled regardless. - Every permission check happens server-side against a single authorization service (`AccessControl::can($user, $action, $funnel)`), never inferred from the UI, so there is one place to audit. - Activity/change log per funnel: who edited what block, when (see §7 audit logging). ### 3.6 Version History - Every explicit save (and every autosave older than N minutes) creates an immutable snapshot row of the full Page Spec JSON. - Editor has a "History" panel: named restore points, diff-lite view (which sections changed), one-click restore (which itself creates a new version rather than destructively overwriting, so restore is never data-losing). ### 3.7 Lead/Submission Management - Every form block writes submissions into the funnel's auto-generated data table (see §6.4), never into a shared generic table with unstructured JSON, so the owner gets a real, queryable dataset per funnel. - A "Submissions" tab per funnel: table view, CSV export, basic filters, and optional webhook/email-on-submit notification. - PII fields are flagged at schema-generation time (email, phone, address, payment-related) and are encrypted at rest (§7.4) and redacted in exports unless the exporting user has explicit `export_pii` permission. ### 3.8 Analytics (recommended "next level" addition) - Per-page view count, per-funnel conversion funnel (visits → step completions → final conversion), basic UTM capture, and simple A/B variant support (a page can have >1 published variant with traffic-split and win-tracking) — see §10 for the schema. --- ## 4. Non-Functional Requirements - **Performance:** Rendered funnel pages must return in <300ms server time on cached content, <1.2s uncached, on typical shared hosting. Output caching (§8.2) is mandatory, not optional. - **Availability:** No single AI outage should break already-published funnels — OpenAI is only in the write/edit path, never in the read/render path of a live page. - **Portability:** Must run on a standard LAMP-style shared host (no shell daemons required); background jobs run via a cron hitting a PHP entrypoint. - **Accessibility:** Generated pages must produce valid semantic HTML (proper heading order, alt text fields required on image blocks, colour-contrast check run against the Style Signature before it's applied). - **Internationalisation:** UTF-8 throughout; AI content generation accepts a target language parameter. - **Auditability:** Every privileged action (permission change, data export, funnel deletion, user role change) is logged with actor, timestamp, and before/after state. --- ## 5. High-Level Architecture ``` ┌──────────────────────┐ ┌──────────────────────────┐ │ Browser (Editor) │◄──────►│ App Layer (PHP, PSR-4) │ │ Quill + Canvas JS │ JSON │ Controllers → Services │ └──────────────────────┘ APIs │ → Repositories (PDO) │ └────────────┬──────────────┘ ┌──────────────────────┐ │ │ Public Visitor │ render request │ │ (funnel page) │────────────────────►│ └──────────────────────┘ │ ┌────────────▼──────────────┐ │ MySQL 8 │ │ users / orgs / funnels / │ │ pages / versions / leads │ │ per-funnel data tables │ └────────────┬──────────────┘ │ ┌────────────▼──────────────┐ │ Job Queue (DB table) │ │ cron worker ─► OpenAI API │ │ ─► Style │ │ fetchers │ └─────────────────────────────┘ ``` Key architectural rule: **the browser never talks to OpenAI directly.** All AI calls are proxied through a server-side `AiGatewayService` that (a) hides the API key, (b) enforces per-user rate limits and cost budgets, (c) validates/sanitises the returned JSON against a strict schema before it ever reaches the database or the editor. --- ## 6. Data Model ### 6.1 Design principles - Every table has a surrogate `BIGINT UNSIGNED AUTO_INCREMENT` primary key plus a `UUID CHAR(36)` public identifier — internal IDs are never exposed in URLs or the API, only UUIDs, to prevent enumeration attacks. - All foreign keys are enforced (`InnoDB`, `ON DELETE` behaviour explicit per relationship — usually `RESTRICT` for financial/audit data, `CASCADE` for purely dependent child rows like blocks-within-a-version). - All timestamps `DATETIME` in UTC, `created_at`/`updated_at` on every table, `deleted_at` for soft deletes on user-facing entities (funnels, pages) so accidental deletion is recoverable. - No table stores plaintext passwords, API keys, or unencrypted PII (see §7.4). ### 6.2 Core schema (abridged DDL — implementer should expand with full indexes/constraints) ```sql CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(120) NOT NULL, role ENUM('member','org_admin','superadmin') NOT NULL DEFAULT 'member', totp_secret VARBINARY(255) NULL, -- encrypted at rest email_verified_at DATETIME NULL, status ENUM('active','suspended','pending') NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE organisations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, owner_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE organisation_members ( organisation_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, org_role ENUM('member','admin') NOT NULL DEFAULT 'member', joined_at DATETIME NOT NULL, PRIMARY KEY (organisation_id, user_id), FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE funnels ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, payload_slug VARCHAR(64) NOT NULL UNIQUE, -- the public URL token, see §8 owner_user_id BIGINT UNSIGNED NOT NULL, organisation_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, purpose_brief TEXT NULL, -- original AI prompt/brief style_signature JSON NULL, -- see §3.3 visibility ENUM('private','shared','organisation','public_preview') NOT NULL DEFAULT 'private', status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE funnel_shares ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, funnel_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, permission ENUM('viewer','editor','admin') NOT NULL, invited_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY (funnel_id, user_id), FOREIGN KEY (funnel_id) REFERENCES funnels(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE pages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, funnel_id BIGINT UNSIGNED NOT NULL, step_order SMALLINT UNSIGNED NOT NULL, role VARCHAR(40) NOT NULL, -- landing, opt_in, checkout, thank_you... slug VARCHAR(80) NOT NULL, -- path within the funnel current_version_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, UNIQUE KEY (funnel_id, slug), FOREIGN KEY (funnel_id) REFERENCES funnels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE page_versions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, page_id BIGINT UNSIGNED NOT NULL, spec_json JSON NOT NULL, -- the full Page Spec, see §6.3 created_by BIGINT UNSIGNED NOT NULL, is_published TINYINT(1) NOT NULL DEFAULT 0, label VARCHAR(120) NULL, -- optional named checkpoint created_at DATETIME NOT NULL, FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT ) ENGINE=InnoDB; ALTER TABLE pages ADD CONSTRAINT fk_current_version FOREIGN KEY (current_version_id) REFERENCES page_versions(id) ON DELETE SET NULL; CREATE TABLE ai_jobs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, funnel_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NOT NULL, job_type ENUM('plan_funnel','style_discovery','generate_page','edit_block','regenerate_page') NOT NULL, status ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued', input_json JSON NOT NULL, output_json JSON NULL, error_text TEXT NULL, tokens_used INT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY (funnel_id) REFERENCES funnels(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE audit_log ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, subject_type VARCHAR(60) NOT NULL, subject_id BIGINT UNSIGNED NOT NULL, ip_address VARBINARY(16) NULL, before_json JSON NULL, after_json JSON NULL, created_at DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` ### 6.3 Page Spec JSON (contract between editor, renderer, and AI) ```json { "version": 1, "theme": { "palette": {"primary": "#1B4B43", "secondary": "#E8DCC8", "accent": "#C87941", "text": "#1A1A1A", "bg": "#FFFFFF"}, "fonts": {"heading": "Fraunces", "body": "Inter"}, "buttonStyle": {"radius": 8, "shadow": "soft"} }, "sections": [ { "id": "sec_hero", "layout": "split-image-right", "blocks": [ {"id": "blk_h1", "type": "heading", "level": 1, "text": "Book your free discovery call", "breakpointOverrides": {}}, {"id": "blk_cta", "type": "button", "text": "Get Started", "action": {"type": "scroll_to", "target": "sec_form"}} ] }, { "id": "sec_form", "layout": "centered", "blocks": [ {"id": "blk_form", "type": "form", "fields": [ {"name": "full_name", "label": "Full name", "type": "text", "required": true, "pii": true}, {"name": "email", "label": "Email", "type": "email", "required": true, "pii": true} ], "submitAction": {"type": "store_and_redirect", "nextPageSlug": "thank-you"}} ] } ] } ``` This JSON is the single source of truth. The editor reads/writes it. The renderer (§8) turns it into HTML server-side. The AI layer only ever emits/edits fragments of it, validated against a JSON Schema before being persisted (see §7.2). ### 6.4 Auto-generated per-funnel data tables When a form block is added with `pii`-flagged or custom fields, a `SchemaGeneratorService`: 1. Derives a safe table name: `funnel_data_{funnel_id}` (never built from user input directly — always the numeric internal ID). 2. Creates/alters the table with one column per declared field, using an allow-listed type mapping (`text`→`VARCHAR(255)`, `email`→`VARCHAR(255)`, `textarea`→`TEXT`, `number`→`DECIMAL(18,2)`, `checkbox`→`TINYINT(1)`), plus fixed system columns: `id`, `uuid`, `page_id`, `submitted_at`, `ip_address` (hashed, see §7.4), `utm_json`. 3. All DDL is generated through a whitelist template — **no raw user-supplied string is ever concatenated into DDL.** Field *names* from the AI/user are slugified to `[a-z0-9_]` and checked against a reserved-word blocklist before being used as a column identifier. 4. Every schema change is itself logged (`audit_log`, subject_type = `funnel_schema`). --- ## 7. Security Architecture (priority requirement) Security is treated as the top non-functional requirement, per the brief. Concrete rules: ### 7.1 Data access - All DB access goes through **PDO with prepared statements only** — string-built SQL is disallowed anywhere in the codebase; enforced via a lint rule/code-review checklist, and the DB service layer has no method that accepts raw SQL fragments from calling code. - The DB user Funnelz connects as has **no DDL privileges** except through one narrowly-scoped account used only by `SchemaGeneratorService` (separate credential, principle of least privilege) — the main app connection is `SELECT/INSERT/UPDATE/DELETE` only. - Row-level authorization is enforced in the service layer, not just hidden in the UI: every query touching a funnel/page/submission is scoped by the requesting user's accessible funnel IDs, computed via `AccessControl`. ### 7.2 Input handling - All incoming JSON (from the editor, and from AI responses) is validated against strict JSON Schemas before being persisted or rendered — unknown block types, unknown fields, or malformed structures are rejected, not silently accepted. - All AI-generated HTML/content is treated as untrusted: rich text from Quill/AI is sanitised through an allow-list HTML sanitiser (strip `