# AroGlow — Admin Panel & Core Backend ## Product Requirement Document (PRD) — Module 1 **Version:** 1.0  |  **Status:** Draft for Engineering Sign-off  |  **Owner:** Product & Engineering  |  **Scope:** `/AroGlow/admin_panel/` --- # 1. Executive Summary AroGlow is an AI-powered Tele-Dermatology and Skincare Companion platform. Users capture or upload images of their skin, converse with an AI dermatology assistant, and receive structured condition analysis — condition name, category, confidence score, severity score, do's/don'ts, and safe-ingredient guidance — powered by Google Gemini's multimodal vision models. Module 1 covers the **Admin Panel and Core PHP Backend**, the operational and technical backbone of the entire ecosystem. This module is a **stateless REST API server** built on Core PHP 8.3 (no framework, native PDO) backed by MySQL 8.0, paired with a **web-based Admin Panel** used by internal operations, support, and engineering staff. The backend serves three constituencies simultaneously: 1. **The Flutter mobile client** (`/AroGlow/user_panel/`) — consumes versioned REST APIs (`/api/v1/...`) for auth, chat, scans, and profile management. 2. **Internal Admin Operators** — manage users, monitor AI cost/usage, configure Gemini API keys and models, and audit system health via the Admin Panel UI. 3. **The AI Orchestration Layer** — a multi-key, multi-model Gemini failover engine that guarantees uptime and cost control across the vision/chat AI pipeline. This document is the single source of truth for database schema, API contracts, admin UI/UX, security hardening, deployment topology, and the Gemini failover engine logic. It is written to be directly actionable by backend engineers, frontend/admin UI developers, DB administrators, and QA — with zero ambiguity left to interpretation. **Primary success criteria for Module 1:** - 99.9% API uptime with automatic Gemini key/model failover (zero user-facing AI outages under single-key exhaustion). - Sub-300ms P95 response time for non-AI endpoints; sub-6s P95 for AI-mediated chat/scan endpoints. - Zero critical security findings (OWASP Top 10) at launch audit. - Admin operators can fully manage users, keys, models, and prompts without a single code deployment. --- # 2. Product Vision **Vision Statement:** *AroGlow's backend should be the invisible, unbreakable nervous system connecting a user's skin concern to a trustworthy AI-driven answer — while giving AroGlow's internal team complete operational command over cost, quality, and safety of that AI pipeline, without ever touching code.* **Strategic Pillars:** | Pillar | Description | |---|---| | **Resilience** | The AI layer must never present a hard failure to the end user. Multi-key, multi-model failover is a first-class architectural citizen, not an afterthought. | | **Operability** | Every configuration that affects cost or AI behavior (keys, models, temperature, system prompts, quotas) is admin-configurable at runtime. | | **Auditability** | Every AI call, failover event, admin action, and error is logged with enough context to reconstruct exactly what happened, when, and why. | | **Security-by-default** | PDO prepared statements everywhere, strict upload validation, JWT-based stateless auth, and least-privilege admin roles. | | **Lean Technical Footprint** | Core PHP + MySQL only — no heavy framework overhead — optimized for a lean VPS deployment with predictable performance. | **Out of Scope for Module 1:** Flutter UI/UX (Module 2), payment gateway integration (Phase 2 monetization — architecturally planned in §21 but not implemented), push notification delivery infrastructure (stubbed only), multi-tenant/white-label support. --- # 3. User Personas ### 3.1 Persona: "Priya" — Super Admin - **Role:** Founder / Head of Product. - **Goals:** Full visibility into user growth, AI token spend, and system health at a glance. Needs to make go/no-go calls on Gemini key top-ups. - **Pain Points:** Cannot tolerate a support ticket saying "AI stopped responding" — needs failover to be invisible and needs the dashboard to show it happened. - **Permissions:** Full CRUD across all modules, including Gemini API Studio and Admin User Management. ### 3.2 Persona: "Rohan" — Operations/Support Admin - **Role:** Customer support lead. - **Goals:** Look up a specific user, view their chat/scan history to resolve a support ticket ("why did the AI say X"), block abusive accounts. - **Pain Points:** Needs a fast, searchable user table and a complete 360° profile view without needing DB access. - **Permissions:** Read/write on Users module, read-only on Gemini API Studio and System Logs. ### 3.3 Persona: "Ananya" — Backend/AI Engineer - **Role:** Maintains the Gemini integration and monitors model performance. - **Goals:** Add new Gemini models the moment Google releases them, tune system prompts, inspect failover logs to identify a dying key before it fully exhausts. - **Pain Points:** Doesn't want to redeploy code to change a model name, prompt, or temperature. - **Permissions:** Full access to Gemini API Studio, System Logs, and Analytics. Read-only on Users. ### 3.4 Persona: "The System" — Automated Actor - **Role:** Cron jobs, failover engine, rate limiters, audit logger. - **Goals:** Enforce quotas, rotate keys, expire JWTs, purge stale sessions, and write immutable logs — all without human intervention. --- # 4. User Stories ### 4.1 Admin-Level Stories - As a **Super Admin**, I want to see today's scan count, active users, and token burn on one dashboard so I can assess system health in under 10 seconds. - As a **Support Admin**, I want to search a user by email/name/ID and instantly view their full chat + scan timeline so I can resolve tickets without engineering help. - As a **Support Admin**, I want to block a user with one click (and have that block immediately reflected in the API's auth middleware) so abusive accounts can't call the API. - As an **AI Engineer**, I want to add a new Gemini API key with a priority rank so the failover engine starts using it in the configured order without a deploy. - As an **AI Engineer**, I want to mark a key as "Dead" manually if I know it's revoked, so the failover engine skips it instantly instead of waiting for a live 401. - As an **AI Engineer**, I want to add `gemini-2.5-flash` as a new model entry via a modal form (name, display label, max tokens, cost tier) and set it as the default active model. - As an **AI Engineer**, I want to edit the system prompt used for skin analysis and temperature per model, with a live preview/test call, before making it live. - As a **Super Admin**, I want an audit log of every failover event (Key A failed → Key B used, timestamp, error code) so I can predict when to top up quota. - As any **Admin**, I want role-based access so support staff cannot see/change Gemini keys, and AI engineers cannot delete user accounts. ### 4.2 System-Level Stories - As the **System**, when a Gemini call returns HTTP 429/401/403/500, I must automatically retry the next active key in priority order before failing the request to the client. - As the **System**, when all keys are exhausted, I must log a `CRITICAL` system event, mark the affected model unavailable, and return a graceful, user-safe error to the Flutter client. - As the **System**, I must expire JWTs after a configurable TTL and reject any API call with an expired/invalid/blacklisted token with `401`. - As the **System**, I must rate-limit `/api/v1/chat/send` per user (configurable, default 20 req/min) to prevent abuse and cost overruns. - As the **System**, I must reject any uploaded file whose real MIME type (via `finfo`) does not match an allow-listed image type, regardless of extension. --- # 5. Functional Requirements | ID | Requirement | Priority | |---|---|---| | FR-01 | Admin authentication via email/password with bcrypt hashing and session-bound JWT (separate from user-facing JWT) | P0 | | FR-02 | Role-Based Access Control (RBAC): `super_admin`, `support_admin`, `ai_engineer` roles with distinct module permissions | P0 | | FR-03 | User Management CRUD with search, filter (status, date range), pagination, and export to CSV | P0 | | FR-04 | User 360° Profile view: profile data, chat sessions list, per-session message thread, scan history with thumbnails | P0 | | FR-05 | Block/Unblock toggle on user accounts that immediately affects API auth middleware (checked on every request) | P0 | | FR-06 | Gemini API Key CRUD: add, edit priority, activate/deactivate, soft-delete, view real-time status (Active/Exhausted/Dead) | P0 | | FR-07 | Gemini Model CRUD: add/edit/remove model identifiers, set default active model, configure per-model temperature & max tokens | P0 | | FR-08 | System Prompt Editor with versioning (each save creates a new version row; rollback supported) | P1 | | FR-09 | Real-time (polling-based, 15s interval) Dashboard widgets: scan count, active users (24h), token burn estimate, key health strip | P0 | | FR-10 | System Logs viewer: filterable by log type (`api_error`, `failover`, `admin_action`, `auth`), date range, and severity | P0 | | FR-11 | Failover Event Timeline: chronological view of every key-switch event with request context | P1 | | FR-12 | REST API layer for Flutter client: auth, profile, chat, scan history — fully documented in §14 | P0 | | FR-13 | Multipart image upload handling for chat messages and skin scans, stored outside webroot execution path | P0 | | FR-14 | Google Sign-In token verification and JWT issuance for mobile users | P0 | | FR-15 | Admin-configurable daily/monthly quota per user for AI calls (monetization groundwork, §21) | P2 | | FR-16 | Email alerting (via SMTP/Mailer) to Super Admin when all Gemini keys for a model are exhausted | P1 | | FR-17 | CSV/PDF export of analytics reports (user growth, scan volume, cost trends) | P2 | | FR-18 | Admin action audit trail (who changed what, when — immutable, append-only) | P0 | --- # 6. Non-Functional Requirements | Category | Requirement | |---|---| | **Performance** | P95 < 300ms for CRUD/list endpoints; P95 < 6s for Gemini-mediated endpoints (includes AI round-trip); Admin dashboard TTI < 2s | | **Scalability** | Stateless API layer horizontally scalable behind NGINX load balancer; MySQL read-replica-ready schema (no session state in DB rows that assumes single writer) | | **Availability** | 99.9% uptime target for API layer; Gemini failover must guarantee AI feature degrades gracefully, never hard-fails, unless 100% of configured keys across all models are exhausted | | **Security** | OWASP Top 10 compliant; PDO prepared statements exclusively (no raw string interpolation into SQL, ever); all admin routes behind auth + RBAC middleware | | **Maintainability** | PSR-12 coding standard for PHP; every module in its own namespaced directory; no business logic in view files | | **Portability** | Must run on any standard LAMP-adjacent VPS (Ubuntu 22.04/24.04, PHP-FPM 8.3, MySQL 8.0, NGINX) without proprietary cloud lock-in | | **Observability** | All AI calls, failovers, and 5xx errors logged to `system_logs` with structured JSON context column | | **Data Retention** | Chat/scan data retained indefinitely unless user requests deletion (GDPR-style right-to-erasure supported via cascading deletes) | | **Localization** | Admin Panel UI in English (v1); backend API responses use ISO 8601 timestamps and UTF-8 throughout | | **Accessibility** | Admin Panel meets WCAG 2.1 AA: keyboard navigable tables/modals, 4.5:1 contrast minimum, visible focus rings | --- # 7. Information Architecture & URL Routing ### 7.1 Admin Panel Route Map (session-authenticated, server-rendered PHP views) ``` /admin/login → Login screen /admin/logout → Session destroy + redirect /admin/dashboard → Main dashboard (default post-login) /admin/users → User list/table /admin/users/view/{id} → User 360° profile /admin/users/edit/{id} → Edit user /admin/users/toggle-status/{id} → POST: block/unblock (AJAX) /admin/gemini/keys → API Key management /admin/gemini/keys/add → Add key modal (AJAX POST) /admin/gemini/keys/edit/{id} → Edit key /admin/gemini/keys/reorder → POST: priority reorder (drag-drop, AJAX) /admin/gemini/models → Model management /admin/gemini/models/add → Add model modal (AJAX POST) /admin/gemini/models/set-default/{id} → POST: set default active model /admin/gemini/prompts → System Prompt Live Editor /admin/gemini/prompts/save → POST: save new prompt version /admin/gemini/prompts/rollback/{ver} → POST: rollback to version /admin/logs → System Logs & Audit Center /admin/logs/failover-timeline → Failover event timeline view /admin/analytics → Analytics & Telemetry dashboard /admin/analytics/export → CSV/PDF export /admin/settings/roles → Admin user/role management (super_admin only) ``` ### 7.2 Public REST API Route Map (stateless, JWT-authenticated, consumed by Flutter) ``` POST /api/v1/auth/google POST /api/v1/auth/refresh GET /api/v1/user/profile PUT /api/v1/user/profile POST /api/v1/chat/send GET /api/v1/chat/history GET /api/v1/chat/session/{uuid} GET /api/v1/scan/history GET /api/v1/scan/{id} ``` All API routes are namespaced under `/api/v1/` to allow additive, non-breaking `/api/v2/` evolution later. Routing is handled by a lightweight front-controller (`index.php`) dispatching to controllers via a static route table — no external router dependency, per the "pure Core PHP" constraint. --- # 8. Screen by Screen Breakdown (Admin Panel) ### 8.1 Login Screen Centered card, AroGlow logo, email + password fields, "Remember me," inline validation errors, subtle shake animation on failed auth. No public "forgot password" self-service in v1 — reset is handled by `super_admin` via Settings. ### 8.2 Dashboard - **Top stat cards (4-up grid):** Total Users, Scans Today, Active Chat Sessions (24h), Estimated Token Spend (24h). - **API Key Health Strip:** horizontal row of pill badges, one per active Gemini key, color-coded (green=Active, amber=Near Quota, red=Exhausted/Dead). - **User Growth Chart:** line chart, last 30 days, daily new-user count. - **Recent Failover Events:** last 5 events, mini-table with "View all" linking to §8.7. - **Recent System Errors:** last 5 `error`/`critical` log rows. ### 8.3 User Management (List) Data table: `ID | Avatar | Name | Email | Registered Date | Status Badge | Last Active | Actions`. Actions column: View (eye icon), Edit (pencil), Block/Unblock (toggle switch), Delete (trash, confirmation modal required). Top bar: search input (debounced 300ms), status filter dropdown, date range picker, "Export CSV" button. Server-side pagination, 25 rows/page default. ### 8.4 User 360° Profile Three-column layout: - **Left:** Avatar, name, email, Google ID, registration date, status badge, quick actions (Block, Edit, Delete). - **Center:** Tabbed panel — **Chat Sessions** (list of session cards, date + first message preview, click to expand full thread with inline image thumbnails) / **Scan History** (grid of scan cards: image thumbnail, condition name, severity badge, confidence %, date). - **Right:** Activity summary sidebar — total scans, total chat sessions, account age, last login. ### 8.5 Gemini API Studio — Keys Tab Table: `Priority (drag handle) | Key Label | Masked Key (sk-...xxxx) | Status Badge | Requests Today | Last Used | Actions`. "Add New Key" opens a modal: Label, Full Key (masked input), Priority (auto-appended to end, reorderable via drag-drop after save). Status badges computed live from `gemini_api_keys.status` + recent `system_logs` failure counts. ### 8.6 Gemini API Studio — Models Tab Table: `Model Name | Display Label | Status (Active/Inactive) | Default Badge | Max Tokens | Temperature | Actions`. "Add New Model" modal: Model Identifier (e.g. `gemini-2.5-flash`), Display Label, Max Tokens, Default Temperature (slider 0.0–1.0), Cost Tier (dropdown, informational). "Set as Default" action swaps the `is_default` flag atomically (only one model can be default). ### 8.7 System Prompt Live Editor Split-pane: left = large textarea (monospace font) bound to selected model's active prompt; right = "Test Panel" — sample user message + optional test image upload, "Run Test" button fires a live Gemini call using the draft prompt (not yet saved) and shows raw response + latency. "Save New Version" commits to `gemini_system_prompts` as a new version row; version history dropdown allows rollback. ### 8.8 System Logs & Audit Center Filterable table: `Timestamp | Log Type Badge | Severity Badge | Message | Context (expandable JSON) | Actor`. Filters: Log Type (`api_error`, `failover`, `admin_action`, `auth_event`), Severity (`info`, `warning`, `error`, `critical`), Date Range. Row expansion reveals full JSON context (stack trace, request payload snapshot, IP). ### 8.9 Failover Event Timeline Vertical timeline UI, most recent first. Each entry: timestamp, "Key A (Priority 1) → Key B (Priority 2)" transition label, triggering error code, affected model, affected session/request ID (linkable). ### 8.10 Analytics & Telemetry Chart grid: User Growth (line), Scan Volume by Condition Category (bar), Token Spend Trend (area), Model Usage Split (donut: which Gemini models are handling traffic). Date range selector applies to all charts. "Export" generates CSV/PDF snapshot. ### 8.11 Role & Admin User Management (Super Admin only) Table of admin users with Role badge and Active status. Add/Edit modal: Name, Email, Role (dropdown: `super_admin`/`support_admin`/`ai_engineer`), temporary password (force reset on first login). --- # 9. UI / UX Specification (Admin Design Tokens) ### 9.1 Color Palette | Token | Hex | Usage | |---|---|---| | `--color-primary-600` | `#0F766E` | Primary actions, active nav, links (teal — evokes clinical trust + skincare freshness) | | `--color-primary-50` | `#F0FDFA` | Primary hover backgrounds | | `--color-accent-500` | `#7C3AED` | AI/Gemini-related highlights (distinguishes "AI system" surfaces from core CRUD) | | `--color-success-500` | `#16A34A` | Active/Healthy status badges | | `--color-warning-500` | `#D97706` | Near-quota / degraded status | | `--color-danger-500` | `#DC2626` | Exhausted/Dead/Blocked status, destructive actions | | `--color-neutral-900` | `#111827` | Primary text | | `--color-neutral-500` | `#6B7280` | Secondary text | | `--color-neutral-200` | `#E5E7EB` | Borders, dividers | | `--color-neutral-50` | `#F9FAFB` | App background | | `--color-surface` | `#FFFFFF` | Card/table surfaces | Dark theme: `--color-neutral-900` becomes background (`#0B0F14`), surfaces at `#151A21`, borders at `#242B33`; primary/accent/status hues shifted +10% lightness for AA contrast on dark. ### 9.2 Typography | Token | Font | Size / Line Height | Usage | |---|---|---|---| | `--font-family-base` | Inter | — | All UI text | | `--font-family-mono` | JetBrains Mono | — | API keys (masked), JSON log context, prompt editor | | `--text-display` | 32px / 40px, 600 | Page titles ("Dashboard", "User Management") | | `--text-heading` | 20px / 28px, 600 | Card/section headers | | `--text-body` | 14px / 20px, 400 | Table cells, body copy | | `--text-caption` | 12px / 16px, 500 | Badges, timestamps, helper text | ### 9.3 Spacing Scale 4px base unit: `4, 8, 12, 16, 24, 32, 48, 64` (tokens `--space-1` through `--space-8`). Card padding: `24px`. Table cell padding: `12px 16px`. Section gaps: `32px`. ### 9.4 Radius & Elevation | Token | Value | Usage | |---|---|---| | `--radius-sm` | 6px | Badges, inputs | | `--radius-md` | 10px | Cards, modals | | `--radius-full` | 9999px | Pills, avatars, toggle switches | | `--shadow-sm` | `0 1px 2px rgba(0,0,0,0.05)` | Table rows on hover | | `--shadow-md` | `0 4px 12px rgba(0,0,0,0.08)` | Cards | | `--shadow-lg` | `0 12px 32px rgba(0,0,0,0.12)` | Modals, dropdowns | ### 9.5 Grid 12-column responsive grid, `24px` gutter, max content width `1440px`, side nav fixed at `260px` (collapsible to `72px` icon rail below `1280px` viewport). --- # 10. Design System (Components) **Buttons:** Primary (filled, `--color-primary-600`), Secondary (outline), Ghost (text-only), Destructive (filled `--color-danger-500`). Sizes: `sm (32px)`, `md (40px)`, `lg (48px)` height. All buttons: `--radius-sm`, 150ms ease-out hover transition, disabled state at 40% opacity with `cursor: not-allowed`. **Inputs:** 40px height, `--radius-sm`, 1px `--color-neutral-200` border, focus state = 2px `--color-primary-600` ring + border color shift. Error state = `--color-danger-500` border + helper text below in danger color. **Tables:** Sticky header row, zebra-free (single surface color + `1px` row dividers), row hover = `--color-neutral-50` background, sortable column headers with chevron indicator, empty-state illustration + copy when zero rows. **Badges (Status):** - `Active` → green pill, `--color-success-500` text on `--color-success-500 @ 10% opacity` background. - `Exhausted` / `Near Quota` → amber pill. - `Dead` / `Blocked` → red pill. - `Default` (model) → accent-purple pill. **Modals:** Centered, max-width `560px` (forms) or `800px` (test panels), `--radius-md`, `--shadow-lg`, backdrop `rgba(0,0,0,0.4)` blur(2px), close on `Esc` and backdrop click, focus-trapped for accessibility. **Toggle Switch:** 44×24px track, `--radius-full`, green when "on" (Active), gray when "off" (Blocked) — used for user block/unblock and key active/inactive. **Loading Skeletons:** Shimmer gradient placeholders matching the shape of tables/cards — never a blocking spinner for list views. **Empty States:** Centered icon + one-line message + primary CTA (e.g., "No API keys yet" → "Add your first key"). **Toasts:** Bottom-right stack, auto-dismiss 4s, success (green left border), error (red left border), max 3 stacked. --- # 11. System & Admin Flow ### 11.1 Admin Login Flow 1. Admin submits email/password → `POST /admin/login`. 2. Backend fetches `admin_users` row by email, verifies `password_argon2id_hash` (Argon2id, not bcrypt — chosen for PHP 8.3 native support and stronger memory-hardness). 3. On success: generate server-side session (PHP native session, `httponly`, `secure`, `samesite=strict` cookie) + short-lived admin JWT stored in session for AJAX calls. 4. On 5 consecutive failures within 15 minutes for the same email: temporary lockout (15 min), logged as `auth_event` severity `warning`. 5. Redirect to `/admin/dashboard`. ### 11.2 Gemini Failover Flow (see full detail in §19) Admin-configured key list (priority-ordered) is loaded, cached in-memory per request via a `KeyManager` class reading from `gemini_api_keys` where `status != 'dead'`, ordered by `priority ASC`. First eligible key attempted; on transient failure, cascades down the list; all outcomes logged. ### 11.3 User Block Flow 1. Admin clicks "Block" toggle on User Management or 360° Profile. 2. `POST /admin/users/toggle-status/{id}` → sets `users.status = 'blocked'`, `blocked_at = NOW()`, writes `admin_actions` audit row. 3. **Effect is immediate:** every subsequent API request from that user's JWT is checked against `users.status` in the auth middleware (`AuthMiddleware::verify()`), returning `403 { "error": "account_blocked" }` even though the JWT itself is still cryptographically valid until expiry — status check is a live DB read, not baked into the token. ### 11.4 System Prompt Update Flow 1. Engineer edits prompt in Live Editor, optionally runs a test call against the draft (not persisted). 2. On "Save New Version": new row inserted into `gemini_system_prompts` with incremented `version`, `is_active = 1` on the new row, `is_active = 0` on the prior row for that `model_id` (transaction-wrapped). 3. Next chat/scan request for that model picks up the new active prompt on next call — no cache invalidation delay beyond the request-scoped `KeyManager`/`PromptManager` instantiation. --- # 12. Admin Panel Core Modules | Module | Responsibility | |---|---| | **Auth Module** | Admin login/logout, session management, RBAC enforcement middleware | | **User Management Module** | CRUD, search/filter, block/unblock, 360° profile aggregation queries | | **Gemini API Studio Module** | Key CRUD + priority ordering, model CRUD, system prompt versioning, live test harness | | **Dashboard Module** | Aggregate stat queries (cached 15s via lightweight file/APCu cache to avoid hammering DB on polling) | | **System Logs Module** | Read-only query interface over `system_logs`, with filter/pagination | | **Analytics Module** | Aggregate reporting queries, chart data serialization, CSV/PDF export | | **Settings/Roles Module** | Admin user CRUD, role assignment (super_admin only) | | **Audit Module** | Write-only interceptor triggered by every mutating admin action; never user-editable | --- # 13. Database Design & SQL DDL ### 13.1 Entity Relationship Overview ``` admin_users ──< admin_actions (audit) users ──< chat_sessions ──< chat_messages users ──< skin_scans chat_messages }o──o{ skin_scans (a scan can originate from a chat message, nullable FK) gemini_api_keys ──< gemini_failover_logs ai_models ──< gemini_system_prompts ai_models ──< gemini_api_keys (a key can be scoped globally or per-model; modeled as nullable FK) users ──< system_logs (nullable — system logs may be user-scoped or system-wide) ``` ### 13.2 Full DDL ```sql -- ============================================================ -- AroGlow Core Schema — MySQL 8.0 -- Charset: utf8mb4 / Collation: utf8mb4_unicode_ci throughout -- ============================================================ CREATE DATABASE IF NOT EXISTS `aroglow` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `aroglow`; -- ------------------------------------------------------------ -- 1. admin_users -- ------------------------------------------------------------ CREATE TABLE `admin_users` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(120) NOT NULL, `email` VARCHAR(190) NOT NULL, `password_hash` VARCHAR(255) NOT NULL COMMENT 'Argon2id hash', `role` ENUM('super_admin','support_admin','ai_engineer') NOT NULL DEFAULT 'support_admin', `status` ENUM('active','disabled') NOT NULL DEFAULT 'active', `must_reset_password` TINYINT(1) NOT NULL DEFAULT 0, `last_login_at` DATETIME NULL, `failed_login_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0, `locked_until` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_admin_email` (`email`), KEY `idx_admin_role_status` (`role`, `status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 2. users (mobile app end-users, synced via Google Sign-In) -- ------------------------------------------------------------ CREATE TABLE `users` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `google_uid` VARCHAR(64) NOT NULL COMMENT 'Google Sign-In sub claim', `name` VARCHAR(150) NOT NULL, `email` VARCHAR(190) NOT NULL, `avatar_url` VARCHAR(500) NULL, `date_of_birth` DATE NULL, `gender` ENUM('male','female','other','undisclosed') NOT NULL DEFAULT 'undisclosed', `skin_type` ENUM('oily','dry','combination','normal','sensitive','unknown') NOT NULL DEFAULT 'unknown', `status` ENUM('active','blocked','deleted') NOT NULL DEFAULT 'active', `blocked_at` DATETIME NULL, `blocked_reason` VARCHAR(255) NULL, `daily_ai_quota` SMALLINT UNSIGNED NOT NULL DEFAULT 20 COMMENT 'Monetization: max AI calls/day', `last_login_at` DATETIME NULL, `last_active_at` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_users_google_uid` (`google_uid`), UNIQUE KEY `uq_users_email` (`email`), KEY `idx_users_status_created` (`status`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 3. ai_models (dynamic Gemini model registry) -- ------------------------------------------------------------ CREATE TABLE `ai_models` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `model_identifier` VARCHAR(80) NOT NULL COMMENT 'e.g. gemini-2.0-flash', `display_label` VARCHAR(120) NOT NULL, `max_output_tokens` INT UNSIGNED NOT NULL DEFAULT 2048, `default_temperature` DECIMAL(3,2) NOT NULL DEFAULT 0.40 COMMENT '0.00 - 1.00', `cost_tier` ENUM('low','medium','high') NOT NULL DEFAULT 'medium', `is_active` TINYINT(1) NOT NULL DEFAULT 1, `is_default` TINYINT(1) NOT NULL DEFAULT 0, `supports_vision` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_model_identifier` (`model_identifier`), KEY `idx_model_active_default` (`is_active`, `is_default`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 4. gemini_api_keys (multi-key pool with priority + status) -- ------------------------------------------------------------ CREATE TABLE `gemini_api_keys` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `label` VARCHAR(100) NOT NULL, `api_key_encrypted` VARBINARY(1024) NOT NULL COMMENT 'AES-256-GCM encrypted at rest', `key_last4` CHAR(4) NOT NULL COMMENT 'For masked display: sk-...xxxx', `model_id` INT UNSIGNED NULL COMMENT 'NULL = usable across all models', `priority` SMALLINT UNSIGNED NOT NULL DEFAULT 100 COMMENT 'Lower = tried first', `status` ENUM('active','exhausted','dead','disabled') NOT NULL DEFAULT 'active', `requests_today` INT UNSIGNED NOT NULL DEFAULT 0, `requests_total` BIGINT UNSIGNED NOT NULL DEFAULT 0, `last_used_at` DATETIME NULL, `last_error_code` VARCHAR(10) NULL, `last_error_at` DATETIME NULL, `quota_reset_at` DATETIME NULL COMMENT 'When provider-side quota is expected to reset', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_keys_priority_status` (`status`, `priority`), CONSTRAINT `fk_keys_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 5. gemini_system_prompts (versioned prompt history) -- ------------------------------------------------------------ CREATE TABLE `gemini_system_prompts` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `model_id` INT UNSIGNED NOT NULL, `version` INT UNSIGNED NOT NULL, `prompt_text` MEDIUMTEXT NOT NULL, `temperature_override` DECIMAL(3,2) NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 0, `created_by_admin_id` BIGINT UNSIGNED NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_model_version` (`model_id`, `version`), KEY `idx_prompt_active` (`model_id`, `is_active`), CONSTRAINT `fk_prompt_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_prompt_admin` FOREIGN KEY (`created_by_admin_id`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 6. gemini_failover_logs (dedicated failover audit trail) -- ------------------------------------------------------------ CREATE TABLE `gemini_failover_logs` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `request_id` CHAR(36) NOT NULL COMMENT 'UUID correlating to the originating API request', `model_id` INT UNSIGNED NOT NULL, `failed_key_id` INT UNSIGNED NULL, `fallback_key_id` INT UNSIGNED NULL, `error_code` VARCHAR(10) NOT NULL COMMENT 'e.g. 429, 401, 403, 500', `error_message` TEXT NULL, `resolved` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 if a fallback key succeeded', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_failover_model_created` (`model_id`, `created_at`), CONSTRAINT `fk_failover_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_failover_failed_key` FOREIGN KEY (`failed_key_id`) REFERENCES `gemini_api_keys`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_failover_fallback_key` FOREIGN KEY (`fallback_key_id`) REFERENCES `gemini_api_keys`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 7. chat_sessions -- ------------------------------------------------------------ CREATE TABLE `chat_sessions` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `session_uuid` CHAR(36) NOT NULL, `user_id` BIGINT UNSIGNED NOT NULL, `title` VARCHAR(160) NULL COMMENT 'Auto-generated from first message', `model_id` INT UNSIGNED NULL COMMENT 'Model used for this session', `status` ENUM('active','archived') NOT NULL DEFAULT 'active', `last_message_at` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_session_uuid` (`session_uuid`), KEY `idx_sessions_user_updated` (`user_id`, `updated_at`), CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_session_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 8. chat_messages -- ------------------------------------------------------------ CREATE TABLE `chat_messages` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `session_id` BIGINT UNSIGNED NOT NULL, `sender` ENUM('user','ai','system') NOT NULL, `message_text` MEDIUMTEXT NULL, `image_path` VARCHAR(500) NULL COMMENT 'Relative path under non-webroot storage', `image_thumb_path` VARCHAR(500) NULL, `structured_metadata` JSON NULL COMMENT 'AI response metadata: condition, confidence, severity, tokens_used', `gemini_key_id` INT UNSIGNED NULL COMMENT 'Which key served this AI response', `model_id` INT UNSIGNED NULL, `latency_ms` INT UNSIGNED NULL, `tokens_used` INT UNSIGNED NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_messages_session_created` (`session_id`, `created_at`), CONSTRAINT `fk_message_session` FOREIGN KEY (`session_id`) REFERENCES `chat_sessions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_message_key` FOREIGN KEY (`gemini_key_id`) REFERENCES `gemini_api_keys`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_message_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 9. skin_scans -- ------------------------------------------------------------ CREATE TABLE `skin_scans` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` BIGINT UNSIGNED NOT NULL, `source_message_id` BIGINT UNSIGNED NULL COMMENT 'Originating chat_messages row, if scan came from chat', `image_path` VARCHAR(500) NOT NULL, `image_thumb_path` VARCHAR(500) NULL, `condition_name` VARCHAR(150) NOT NULL, `condition_category` ENUM('acne','pigmentation','eczema','psoriasis','rosacea','aging','allergy','infection','other') NOT NULL DEFAULT 'other', `confidence_score` DECIMAL(5,2) NOT NULL COMMENT '0.00 - 100.00', `severity_score` TINYINT UNSIGNED NOT NULL COMMENT '1 (mild) - 10 (severe)', `dos` JSON NULL COMMENT 'Array of recommended actions', `donts` JSON NULL COMMENT 'Array of actions to avoid', `safe_ingredients` JSON NULL COMMENT 'Array of ingredient names safe for this condition', `raw_ai_response` MEDIUMTEXT NULL COMMENT 'Full raw Gemini response for audit/debug', `model_id` INT UNSIGNED NULL, `gemini_key_id` INT UNSIGNED NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_scans_user_created` (`user_id`, `created_at`), KEY `idx_scans_category_severity` (`condition_category`, `severity_score`), CONSTRAINT `fk_scan_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_scan_message` FOREIGN KEY (`source_message_id`) REFERENCES `chat_messages`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_scan_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_scan_key` FOREIGN KEY (`gemini_key_id`) REFERENCES `gemini_api_keys`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 10. system_logs -- ------------------------------------------------------------ CREATE TABLE `system_logs` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `log_type` ENUM('api_error','failover','admin_action','auth_event','ai_call','rate_limit') NOT NULL, `severity` ENUM('info','warning','error','critical') NOT NULL DEFAULT 'info', `message` VARCHAR(500) NOT NULL, `context` JSON NULL COMMENT 'Structured payload: request_id, stack_trace, IP, admin_id, etc.', `user_id` BIGINT UNSIGNED NULL, `admin_id` BIGINT UNSIGNED NULL, `ip_address` VARCHAR(45) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_logs_type_severity_created` (`log_type`, `severity`, `created_at`), CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 11. admin_actions (immutable audit trail) -- ------------------------------------------------------------ CREATE TABLE `admin_actions` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `admin_id` BIGINT UNSIGNED NOT NULL, `action` VARCHAR(100) NOT NULL COMMENT 'e.g. user.block, key.add, model.set_default', `target_type` VARCHAR(50) NULL COMMENT 'e.g. users, gemini_api_keys', `target_id` BIGINT UNSIGNED NULL, `before_state` JSON NULL, `after_state` JSON NULL, `ip_address` VARCHAR(45) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_actions_admin_created` (`admin_id`, `created_at`), KEY `idx_actions_target` (`target_type`, `target_id`), CONSTRAINT `fk_actions_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 12. jwt_blacklist (logout / forced-invalidation support) -- ------------------------------------------------------------ CREATE TABLE `jwt_blacklist` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `jti` CHAR(36) NOT NULL COMMENT 'JWT ID claim', `expires_at` DATETIME NOT NULL COMMENT 'Mirrors token exp for cleanup jobs', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_jti` (`jti`), KEY `idx_blacklist_expires` (`expires_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` ### 13.3 Indexing Rationale - Composite indexes always lead with the highest-selectivity/most-filtered column used in admin queries (e.g. `status` before `priority` on `gemini_api_keys`, since dashboard queries always filter `status='active'` first). - `system_logs` and `admin_actions` are append-only/high-write tables — indexes kept minimal (type/severity/date, actor+date) to avoid write amplification. - All `JSON` columns (`structured_metadata`, `dos`, `donts`, `safe_ingredients`, `context`) are intentionally not indexed directly; if query patterns later demand it, MySQL 8.0 generated columns + functional indexes will be added in a migration, not v1. - All foreign keys use `ON UPDATE CASCADE`; deletion behavior is deliberately mixed — `CASCADE` for strict parent-child ownership (user → sessions → messages, user → scans), `SET NULL` for soft-references that should survive parent deletion (key/model references on historical logs). --- # 14. API Specification **Base URL:** `https://api.aroglow.app/api/v1` **Auth:** Bearer JWT in `Authorization: Bearer ` header (except `/auth/google`). **Content-Type:** `application/json` unless multipart noted. **Error envelope (all endpoints):** ```json { "success": false, "error": { "code": "string_error_code", "message": "Human readable" } } ``` ### 14.1 `POST /api/v1/auth/google` Exchanges a Google Sign-In ID token for an AroGlow backend JWT pair. **Headers:** `Content-Type: application/json` **Request Body:** ```json { "id_token": "eyJhbGciOi..." } ``` **Validation:** `id_token` required; verified server-side against Google's public certs (`google/apiclient` or manual JWKS verification) — audience must match AroGlow's OAuth Client ID. **Success 200:** ```json { "success": true, "data": { "access_token": "eyJhbGciOi...", "refresh_token": "eyJhbGciOi...", "expires_in": 3600, "user": { "id": 4521, "name": "Aditi Sharma", "email": "aditi@example.com", "avatar_url": "https://...", "skin_type": "combination" } } } ``` **Errors:** `400 invalid_token_format` · `401 google_verification_failed` · `403 account_blocked` · `500 internal_error` --- ### 14.2 `POST /api/v1/auth/refresh` **Request Body:** `{ "refresh_token": "..." }` **Success 200:** `{ "success": true, "data": { "access_token": "...", "expires_in": 3600 } }` **Errors:** `401 refresh_token_expired` · `401 refresh_token_revoked` --- ### 14.3 `GET /api/v1/user/profile` **Headers:** `Authorization: Bearer ` **Success 200:** ```json { "success": true, "data": { "id": 4521, "name": "Aditi Sharma", "email": "aditi@example.com", "avatar_url": "https://...", "date_of_birth": "1996-04-12", "gender": "female", "skin_type": "combination", "created_at": "2026-02-11T09:22:00Z" } } ``` **Errors:** `401 unauthorized` · `403 account_blocked` --- ### 14.4 `PUT /api/v1/user/profile` **Request Body (partial update supported):** ```json { "name": "Aditi S.", "gender": "female", "skin_type": "oily", "date_of_birth": "1996-04-12" } ``` **Validation:** `skin_type` must be one of the enum values; `date_of_birth` must be a valid past date (age ≥ 13 enforced). **Success 200:** returns updated profile object (same shape as 14.3). **Errors:** `400 validation_failed` (field-level detail in `error.fields`) · `401 unauthorized` --- ### 14.5 `POST /api/v1/chat/send` **Content-Type:** `multipart/form-data` **Form Fields:** | Field | Type | Required | Notes | |---|---|---|---| | `session_uuid` | string (UUID) | No | Omit to start a new session | | `message` | string | Conditional | Required if no image attached | | `image` | file | Conditional | Required if no message text; JPEG/PNG/WEBP, max 8MB | **Success 201:** ```json { "success": true, "data": { "session_uuid": "b3f1c2a0-...", "user_message": { "id": 88123, "sender": "user", "message_text": "Is this a rash?", "image_url": "https://cdn.../abc.jpg", "created_at": "2026-08-14T10:02:00Z" }, "ai_message": { "id": 88124, "sender": "ai", "message_text": "Based on the image, this appears consistent with contact dermatitis...", "structured_metadata": { "condition_name": "Contact Dermatitis", "category": "eczema", "confidence_score": 82.5, "severity_score": 4, "dos": ["Apply fragrance-free moisturizer", "Avoid the suspected irritant"], "donts": ["Do not scratch the area", "Avoid hot water on the site"], "safe_ingredients": ["Ceramides", "Colloidal oatmeal"] }, "model_used": "gemini-2.0-flash", "latency_ms": 2340, "created_at": "2026-08-14T10:02:03Z" } } } ``` **Errors:** `400 empty_message_and_image` · `401 unauthorized` · `403 account_blocked` · `413 image_too_large` · `415 unsupported_media_type` · `429 rate_limited` (includes `retry_after_seconds`) · `429 daily_quota_exceeded` · `502 ai_service_unavailable` (all keys/models exhausted — see §19.4) · `500 internal_error` --- ### 14.6 `GET /api/v1/chat/history` **Query Params:** `page` (default 1), `per_page` (default 20, max 50) **Success 200:** ```json { "success": true, "data": { "sessions": [ { "session_uuid": "b3f1c2a0-...", "title": "Rash on forearm", "last_message_preview": "Apply fragrance-free moisturizer...", "last_message_at": "2026-08-14T10:02:03Z", "message_count": 6 } ], "pagination": { "page": 1, "per_page": 20, "total": 34, "total_pages": 2 } } } ``` **Errors:** `401 unauthorized` --- ### 14.7 `GET /api/v1/chat/session/{uuid}` Returns the full ordered message thread for a session (paginated, `page`/`per_page` params same as above). **Errors:** `401 unauthorized` · `403 not_your_session` · `404 session_not_found` --- ### 14.8 `GET /api/v1/scan/history` **Query Params:** `page`, `per_page`, `category` (optional filter) **Success 200:** ```json { "success": true, "data": { "scans": [ { "id": 5521, "image_thumb_url": "https://cdn.../thumb_5521.jpg", "condition_name": "Contact Dermatitis", "condition_category": "eczema", "confidence_score": 82.5, "severity_score": 4, "created_at": "2026-08-14T10:02:03Z" } ], "pagination": { "page": 1, "per_page": 20, "total": 12, "total_pages": 1 } } } ``` **Errors:** `401 unauthorized` --- ### 14.9 `GET /api/v1/scan/{id}` Returns full scan detail including `dos`, `donts`, `safe_ingredients`, full-size `image_url`. **Errors:** `401 unauthorized` · `403 not_your_scan` · `404 scan_not_found` --- ### 14.10 Global Token Validation Rules - JWT signed `HS256` (or `RS256` if key-rotation infra is added later), claims: `sub` (user id), `iat`, `exp`, `jti`. - Every request: (1) verify signature, (2) check `exp`, (3) check `jti` not in `jwt_blacklist`, (4) live DB check `users.status = 'active'`. - `access_token` TTL: 60 minutes. `refresh_token` TTL: 30 days, single-use rotation (old refresh token blacklisted on use). --- # 15. Backend Architecture & Folder Structure (`/AroGlow/admin_panel/`) ``` /AroGlow/ ├── admin_panel/ │ ├── public/ # Web root (only this dir is web-served) │ │ ├── index.php # Front controller — admin panel entry │ │ ├── api/ │ │ │ └── index.php # Front controller — REST API entry (/api/v1/*) │ │ ├── assets/ │ │ │ ├── css/ │ │ │ ├── js/ │ │ │ └── img/ │ │ └── .htaccess / nginx handled # No PHP execution outside index.php entrypoints │ │ │ ├── app/ │ │ ├── Config/ │ │ │ ├── database.php │ │ │ ├── app.php # env, JWT secret refs, upload limits │ │ │ └── routes.php # Static route table (admin + API) │ │ │ │ │ ├── Core/ │ │ │ ├── Router.php │ │ │ ├── Controller.php # Base controller │ │ │ ├── Database.php # PDO singleton wrapper │ │ │ ├── Request.php │ │ │ ├── Response.php │ │ │ └── Session.php │ │ │ │ │ ├── Middleware/ │ │ │ ├── AuthMiddleware.php # JWT verify + status check (API) │ │ │ ├── AdminAuthMiddleware.php # Session-based admin auth │ │ │ ├── RbacMiddleware.php # Role permission gate │ │ │ └── RateLimitMiddleware.php │ │ │ │ │ ├── Controllers/ │ │ │ ├── Admin/ │ │ │ │ ├── AuthController.php │ │ │ │ ├── DashboardController.php │ │ │ │ ├── UserController.php │ │ │ │ ├── GeminiKeyController.php │ │ │ │ ├── GeminiModelController.php │ │ │ │ ├── GeminiPromptController.php │ │ │ │ ├── SystemLogController.php │ │ │ │ ├── AnalyticsController.php │ │ │ │ └── RoleController.php │ │ │ └── Api/ │ │ │ ├── AuthController.php │ │ │ ├── ProfileController.php │ │ │ ├── ChatController.php │ │ │ └── ScanController.php │ │ │ │ │ ├── Services/ │ │ │ ├── Gemini/ │ │ │ │ ├── KeyManager.php # Loads + orders eligible keys │ │ │ │ ├── ModelManager.php # Resolves active model + config │ │ │ │ ├── PromptManager.php # Resolves active system prompt │ │ │ │ ├── GeminiClient.php # Raw cURL wrapper for Gemini API │ │ │ │ └── FailoverEngine.php # Orchestrates try/catch/cascade │ │ │ ├── Auth/ │ │ │ │ ├── JwtService.php │ │ │ │ └── GoogleTokenVerifier.php │ │ │ ├── Upload/ │ │ │ │ ├── ImageUploadService.php # MIME validation, storage, thumbnailing │ │ │ ├── Logging/ │ │ │ │ └── SystemLogger.php │ │ │ └── Audit/ │ │ │ └── AuditLogger.php │ │ │ │ │ ├── Models/ # Thin data-access objects (not ORM) │ │ │ ├── UserModel.php │ │ │ ├── AdminUserModel.php │ │ │ ├── ChatSessionModel.php │ │ │ ├── ChatMessageModel.php │ │ │ ├── SkinScanModel.php │ │ │ ├── GeminiApiKeyModel.php │ │ │ ├── AiModelModel.php │ │ │ └── SystemLogModel.php │ │ │ │ │ ├── Views/ # PHP templates for Admin Panel (server-rendered) │ │ │ ├── layouts/ │ │ │ │ └── main.php │ │ │ ├── auth/login.php │ │ │ ├── dashboard/index.php │ │ │ ├── users/{list,view,edit}.php │ │ │ ├── gemini/{keys,models,prompts}.php │ │ │ ├── logs/{index,timeline}.php │ │ │ └── analytics/index.php │ │ │ │ │ └── Helpers/ │ │ ├── Validator.php │ │ ├── Sanitizer.php │ │ └── UuidGenerator.php │ │ │ ├── storage/ │ │ ├── uploads/ │ │ │ ├── chat_images/ # Outside webroot, non-executable │ │ │ └── scan_images/ │ │ ├── logs/ │ │ │ └── php_error.log │ │ └── cache/ # Dashboard stat cache (file/APCu) │ │ │ ├── database/ │ │ ├── migrations/ # Numbered .sql migration files │ │ └── seeders/ │ │ │ ├── vendor/ # Composer (minimal: firebase/php-jwt, phpmailer only) │ ├── composer.json │ ├── .env.example │ └── .env # gitignored │ └── user_panel/ # Flutter client (Module 2 — out of scope here) ``` **Key architectural decisions:** - **Single front controller per surface** (`public/index.php` for admin, `public/api/index.php` for REST API) — both route through the same `Core/Router.php` but resolve against separate route tables and middleware stacks. - **No ORM.** Data access via thin Model classes wrapping PDO prepared statements directly — matches the "pure Core PHP" constraint and keeps SQL fully auditable. - **Storage outside webroot execution path**: `storage/uploads/` is not under `public/`; images are served via a signed, controller-mediated streaming endpoint (or via NGINX `X-Accel-Redirect`) so uploaded files can never be directly executed even if disguised as PHP. - **Composer kept minimal** by design — only `firebase/php-jwt` (JWT) and `phpmailer/phpmailer` (SMTP) as third-party dependencies; everything else is hand-rolled per the "pure Core PHP" mandate. --- # 16. Frontend Architecture (Admin Web Interface) - **Stack:** Server-rendered PHP views + Tailwind CSS (utility-first, compiled via CLI, no Node runtime dependency in production) + vanilla JS (ES6 modules) for interactivity. Alpine.js (lightweight, CDN-delivered) used for declarative UI state (toggles, tabs, dropdowns) without a full SPA framework — consistent with the "pure/lean backend" philosophy. - **AJAX Layer:** All mutating actions (block/unblock, key CRUD, model CRUD, prompt save) execute via `fetch()` against dedicated admin AJAX endpoints returning JSON, with optimistic UI updates rolled back on error + toast notification. - **Charting:** Chart.js (CDN) for Dashboard and Analytics — line/bar/donut charts fed by JSON endpoints (`/admin/analytics/data/*`). - **Tables:** Server-side pagination/filter/sort (no client-side heavy datatable library) to keep payloads small and support large user bases without pagination-library bloat. - **State Management:** No global client state store needed — each page is independently server-rendered; Alpine.js scope is component-local. - **Build Pipeline:** Tailwind CLI compiles `assets/css/input.css` → `assets/css/app.min.css` at build/deploy time; no bundler required for JS (native ES modules, `