# Database Schema & Migrations

App patch v1.7.1 adds no schema migration; current schema revision remains Migrator v1.7.0. Callback/update idempotency tables and session/secondary guards remain unchanged.

This document details the database structure, table definitions, unique indexes, and the migration strategy for the **TFB Engine**.

---

## 1. Schema Base & Table Inventory

The base DDL schema resides in `src/Install/Schema.php` (for fresh installs) and is upgraded additively via `src/Install/Migrator.php`. v1.6.0 keeps fresh install and upgrade paths aligned through this single source of truth:

### Core Funnel Metadata
* **`tests`**: The marketing funnels. Contains unique current `slug`, result/counter fields, v1.6.0 gate fields (`gate_title`, `gate_description`, `gate_show_question_count`, `gate_question_count_template`, `gate_start_button_text`), and `secondary_enabled`.
* **`test_secondary_categories`**: Ordered categories per test; unique `(test_id, sort_order)`.
* **`test_secondary_items`**: Category-bound text/photo/video/voice/audio/document metadata with caption, stored file and channel references; indexed by test/category/order. Old flat rows migrate to category 1.
* **`processed_updates`**: Global Telegram update_id idempotency with 14-day retention.
* Generated guards enforce one default test, one secondary test, and one in-progress session per user/test.
* **`test_profiles`**: Personalities or scoring dimensions. Bound per-test (`UNIQUE(test_id, code)`).
* **`questions`**: Test questions. Ordered by `sort_order` per test.
* **`question_options`**: Choice options for a question. Contains `next_action` (`next`, `jump`, `end`) and `jump_to_question_id`.
* **`option_profile_scores`**: The score mapping of options to profiles. Stores `score` (minimum 0 enforced at input boundary).

### Results & Media Metadata
* **`result_definitions`**: Result content packages per profile. Contains `description` (analysis text), `cta_button_text`, and `cta_url`.
* **`result_media`**: Mapping of result definitions to stored files.
* **`stored_files`**: Telegram `file_id` and unique file metadata for uploaded assets (voice/pdf/image). **No binary media is stored on-host.**

### Student Attempt Bookkeeping
* **`test_sessions`**: Individual student test sessions. Tracks `current_question_id`, `current_question_message_id`, `started_at`, and `completed_at`.
* **`session_answers`**: Stores option answered per question per session. `UNIQUE(session_id, question_id)` ensures only one answer per question.
* **`session_scores`**: Scores aggregated per profile for completed sessions.

### Marketing, Campaigns & Attribution
* **`campaigns`**: Ad/marketing source metadata. Unique `code`, `title`, `source`, `medium`, `is_active`.
* **`campaign_links`**: Shareable, rotate-able deep links bound to campaigns and/or tests. Tracks status (`active`, `replaced`, `disabled`) and clicks.

### Unique Analytics & User State
* **`users`**: All registered Telegram users. Tracks `is_banned`, `is_blocked`, `role`, first-touch `campaign_id`, and `admin_workspace_message_id` (the single workspace surface ID).
* **`test_user_stats`**: Stores first/last start, completion, and attempt counts for each unique user per test. `UNIQUE(user_id, test_id)` is the single source of truth for **uniqueness-at-source**.
* **`user_states`**: One active state and JSON context per user for multi-step wizards.
* **`processed_callbacks`**: Idempotency guard for webhook queries. `UNIQUE(callback_id)` guarantees a callback is processed at most once.
* **`analytics_events`**: Append-only event log.

### System, Security & Operations
* **`settings`**: Key-value system config with typed values (`string`, `int`, `bool`, `json`).
* **`admin_audit_logs`**: Append-only trail of admin mutations.
* **`membership_logs`**: Log of Telegram join status transitions.
* **`broadcast_jobs`**: Broadcast jobs queue metadata.
* **`broadcast_job_items`**: Targets and delivery statuses (`pending`, `sent`, `failed`, `skipped`) for broadcast jobs.
* **`secure_setup_tokens`**: Tokens for secure web setup (`secure-setup.php`).
* **`release_backups`**: Records of local/remote rollbacks.

---

## 2. Critical Unique Indexes & Constraints

These indexes are load-bearing invariants and must never be removed or altered:

| Unique Index / Constraint | Table | Enforcement |
|---|---|---|
| `UNIQUE(slug)` | `tests` | Prevents duplicate test slugs. |
| `UNIQUE(test_id, code)` | `test_profiles` | Restricts code collisions to the test's scope. |
| `UNIQUE(session_id, question_id)` | `session_answers` | Prevents duplicate answers to the same question. |
| `UNIQUE(callback_id)` | `processed_callbacks` | Guards against duplicate webhook/callback delivery. |
| `UNIQUE(user_id, test_id)` | `test_user_stats` | Guarantees starts/completions are unique per user per test. |
| `UNIQUE(token)` | `campaign_links` | Prevents unguessable link collisions. |

---

## 3. Idempotent Migration Strategy (`Tfb\Install\Migrator`)

To support updates and shared-hosting deployments, database modifications never use separate, file-by-file migrations. Instead:

* **Single Source of Truth:** `Migrator::upgrade()` is the sole class that manages additive schema upgrades.
* **Additive Only:** It checks `information_schema` columns, keys, and settings existence before running any `ALTER TABLE` or `INSERT`, making it completely idempotent and safe to run repeatedly.
* **In-Bot Updates:** The in-bot update system (`UpdateService::runMigrations()`) calls this exact same method automatically after extracting a ZIP package.

### "Do Not Invent Columns/Tables Casually"
When adding new features:
1. Try to utilize existing `user_states.context_json` or custom settings in `settings` first.
2. If schema changes are unavoidable, add them as **idempotent, additive-only statements** inside `Migrator::upgrade()`.
3. Never use destructive operations (`DROP COLUMN`, `RENAME TABLE`).