chartboard/TASKS.md

📋 Chart-Board — Phased Work & Task Breakdown

Project: Chart-Board (CodeIgniter 4) Stack: PHP 8.1 · CI4 · MySQL 8 · Bootstrap 5 · ApexCharts.js · Alpine.js Total Estimated Duration: ~14 Weeks (Solo) / ~7 Weeks (2-Dev Team)

---

📌 Legend

Symbol	Meaning
🔴	Blocker — must be done before next task
🟡	Important — high priority
🟢	Normal priority
⚙️	Backend task
🎨	Frontend task
🧪	Testing task
📄	Documentation task

---

──────────────────────────────────────────

PHASE 1 — Project Foundation & Setup

Estimated Time: 3–4 Days

──────────────────────────────────────────

1.1 Environment & Scaffolding

• 🔴 ⚙️ Install CodeIgniter 4 via Composer (composer create-project codeigniter4/appstarter chart-board)
• 🔴 ⚙️ Configure .env file — baseURL, database.*, CI_ENVIRONMENT
• 🔴 ⚙️ Set up MySQL database chartboard with utf8mb4 charset
• 🔴 ⚙️ Run the full chartboard.sql schema to create all 15+ tables
• 🟡 ⚙️ Configure app/Config/Database.php for MySQL connection
• 🟡 ⚙️ Set up app/Config/App.php — timezone, base URL, session settings
• 🟢 ⚙️ Configure app/Config/Cache.php — file-based cache for dev, Redis for prod
• 🟢 ⚙️ Configure app/Config/Email.php — SMTP settings for alerts/verification
• 🟢 ⚙️ Set writable directory permissions (chmod -R 777 writable/)
• 🟢 📄 Create .gitignore — exclude .env, writable/, vendor/

1.2 Front-End Base Setup

• 🔴 🎨 Integrate Bootstrap 5 via CDN or npm build pipeline
• 🔴 🎨 Integrate ApexCharts.js via CDN
• 🟡 🎨 Integrate Alpine.js for lightweight reactivity
• 🟡 🎨 Create base layout file app/Views/layouts/main.php — sidebar + topbar + content slot
• 🟡 🎨 Create app/Views/layouts/auth.php — centered card layout for login/register
• 🟢 🎨 Set up global CSS variables file public/assets/css/variables.css
• 🟢 🎨 Set up global JS file public/assets/js/app.js — sidebar toggle, toast, helpers
• 🟢 🎨 Add favicon, logo mark assets to public/assets/images/

1.3 CI4 Structure Setup

• 🟡 ⚙️ Create base BaseController.php — set shared data (user session, workspace)
• 🟡 ⚙️ Create app/Config/Routes.php skeleton — group routes by module
• 🟡 ⚙️ Create AuthFilter.php — redirect unauthenticated users to login
• 🟡 ⚙️ Create RoleFilter.php — check workspace role before allowing access
• 🟢 ⚙️ Create ApiAuthFilter.php — validate Authorization: Bearer token for API routes
• 🟢 ⚙️ Set up CI4 Encryption service config (app/Config/Encryption.php)
• 🟢 ⚙️ Generate encryption key with php spark key:generate

---

──────────────────────────────────────────

PHASE 2 — Authentication & User Management

Estimated Time: 4–5 Days

──────────────────────────────────────────

2.1 User Model & Migration

• 🔴 ⚙️ Create UserModel.php — CRUD, soft delete, findByEmail, findByApiToken
• 🟡 ⚙️ Create Migration_CreateUsersTable.php (already in SQL; create CI4 migration file)
• 🟢 ⚙️ Create InitialSeeder.php — seed default super admin + default workspace

2.2 Registration

• 🔴 ⚙️ Auth/RegisterController.php — show form, validate input, hash password, save user
• 🔴 ⚙️ Generate verify_token, send verification email on registration
• 🟡 🎨 app/Views/auth/register.php — registration form with name, email, password, confirm password
• 🟡 ⚙️ Auth/RegisterController::verify() — handle email verification token link
• 🟢 🎨 Show success flash message after registration
• 🟢 🧪 Test: register with valid data, duplicate email, weak password

2.3 Login & Logout

• 🔴 ⚙️ Auth/LoginController.php — validate credentials, check email_verified, start session
• 🔴 ⚙️ Store user data in CI4 session: user_id, name, email, role
• 🔴 🎨 app/Views/auth/login.php — email + password form, remember me checkbox
• 🟡 ⚙️ Auth/LoginController::logout() — destroy session, redirect to login
• 🟡 ⚙️ Insert record into user_sessions on login, delete on logout
• 🟢 🧪 Test: wrong password, unverified email, active session redirect

2.4 Password Reset

• 🟡 ⚙️ Auth/PasswordController::forgot() — generate reset_token, set reset_token_expiry, send email
• 🟡 ⚙️ Auth/PasswordController::reset() — validate token, check expiry, update password
• 🟡 🎨 app/Views/auth/forgot.php — email input form
• 🟡 🎨 app/Views/auth/reset.php — new password + confirm form
• 🟢 🧪 Test: expired token, already-used token, mismatched passwords

2.5 User Profile

• 🟡 ⚙️ ProfileController.php — show profile, update name/avatar, change password
• 🟡 🎨 app/Views/profile/index.php — profile card with avatar upload
• 🟡 ⚙️ Handle avatar image upload to writable/uploads/avatars/
• 🟡 ⚙️ ProfileController::generateApiToken() — create/rotate personal API token, save hashed
• 🟢 🎨 Show/copy API token UI with regenerate button
• 🟢 🧪 Test: avatar upload size limits, password mismatch, token regeneration

2.6 Super Admin — User Management

• 🟡 ⚙️ Admin/UserController.php — list all users, activate/deactivate, change role
• 🟡 🎨 app/Views/admin/users/index.php — paginated table with search and status filter
• 🟢 🎨 app/Views/admin/users/edit.php — edit user role and active status
• 🟢 🧪 Test: deactivate user blocks login, role change reflects immediately

---

──────────────────────────────────────────

PHASE 3 — Workspace Management

Estimated Time: 3–4 Days

──────────────────────────────────────────

3.1 Workspace CRUD

• 🔴 ⚙️ WorkspaceController.php — create, read, update, delete workspaces
• 🔴 ⚙️ WorkspaceModel.php — with soft delete, slug generation, owner filter
• 🔴 🎨 app/Views/workspace/index.php — workspace list/grid with create button
• 🟡 🎨 app/Views/workspace/create.php — name, description, timezone, logo upload form
• 🟡 🎨 app/Views/workspace/settings.php — edit workspace details
• 🟡 ⚙️ Auto-generate unique slug from workspace name on creation
• 🟡 ⚙️ On workspace creation, auto-insert creator as workspace_members with role admin
• 🟢 ⚙️ Soft delete workspace — cascade to members, data sources, charts, dashboards
• 🟢 🧪 Test: duplicate slug, logo upload, owner-only delete restriction

3.2 Members & Invitations

• 🔴 ⚙️ WorkspaceMemberController.php — list members, change role, remove member
• 🔴 ⚙️ WorkspaceInvitationController.php — send invite email, accept invite, cancel invite
• 🟡 🎨 app/Views/workspace/members.php — members table with role dropdown and remove button
• 🟡 🎨 app/Views/workspace/invite.php — email + role form, pending invites list
• 🟡 ⚙️ Generate secure token for invite, store in workspace_invitations, set expiry (48h)
• 🟡 ⚙️ Public route /invite/{token} — if user exists log them in; else redirect to register
• 🟢 ⚙️ Prevent inviting existing members, prevent duplicate pending invites
• 🟢 🧪 Test: expired token, already-accepted token, role change enforcement

3.3 Workspace Context Switching

• 🟡 ⚙️ Store active_workspace_id in session, set on login/switch
• 🟡 🎨 Workspace switcher dropdown in sidebar — list user's workspaces, highlight active
• 🟢 ⚙️ Middleware: validate user is member of active workspace on every request
• 🟢 🧪 Test: user with 0 workspaces, switching while on a dashboard page

---

──────────────────────────────────────────

PHASE 4 — Data Source Connections

Estimated Time: 5–6 Days

──────────────────────────────────────────

4.1 Data Source Model & Encryption

• 🔴 ⚙️ DataSourceModel.php — CRUD, filter by workspace, soft delete
• 🔴 ⚙️ Libraries/Encrypter.php — wrap CI4 Encryption to encrypt/decrypt credentials
• 🔴 ⚙️ Encrypt password, api_auth_value fields before saving; decrypt on retrieval
• 🟢 🧪 Test: encrypted values are not plain text in DB, decryption returns correct value

4.2 Connection UI

• 🔴 🎨 app/Views/datasource/index.php — list all data sources with type icon, status badge
• 🔴 🎨 app/Views/datasource/create.php — dynamic form (type selector shows/hides fields)
• 🟡 🎨 Alpine.js: show MySQL/PostgreSQL fields when DB type selected; show API fields for REST API
• 🟡 🎨 app/Views/datasource/edit.php — edit form with masked password field
• 🟡 🎨 Connection status badge — Untested / Connected (green) / Failed (red)
• 🟢 🎨 Delete confirmation modal

4.3 Connection Drivers

• 🔴 ⚙️ Libraries/Connectors/MySQLConnector.php — connect via PDO, run test query SELECT 1
• 🔴 ⚙️ Libraries/Connectors/PostgreSQLConnector.php — connect via PDO pgsql
• 🟡 ⚙️ Libraries/Connectors/MongoDBConnector.php — connect via MongoDB PHP library URI
• 🟡 ⚙️ Libraries/Connectors/RestApiConnector.php — cURL GET/POST with auth headers
• 🟡 ⚙️ Libraries/Connectors/CsvConnector.php — parse uploaded CSV into in-memory array
• 🟢 ⚙️ Libraries/ConnectionFactory.php — factory to return correct connector by type
• 🟢 🧪 Test: each connector with valid/invalid credentials

4.4 Test Connection Endpoint

• 🔴 ⚙️ POST /datasource/test (AJAX) — instantiate connector, run test, return JSON {success, message}
• 🔴 🎨 "Test Connection" button with spinner; show success/error inline below button
• 🟡 ⚙️ Update status and last_tested_at in DB after test
• 🟢 🧪 Test: timeout handling (set cURL timeout 10s), wrong host, wrong credentials

4.5 Schema Browser (for Query Builder)

• 🟡 ⚙️ GET /datasource/{id}/schema (AJAX) — return tables list and columns per table as JSON
• 🟡 🎨 Schema sidebar in query builder — collapsible tree: Tables → Columns with types
• 🟢 ⚙️ Cache schema response for 5 minutes per data source
• 🟢 🧪 Test: DB with 100+ tables, special characters in column names

---

──────────────────────────────────────────

PHASE 5 — Query Builder

Estimated Time: 5–6 Days

Completion note: Core MVP items are implemented (unified create/edit form, three query modes, multi-filter / multi–order-by visual builder, variables for raw + visual, API headers in api_params, preview table + execution log, query cache). Open: CodeMirror editor, datasource schema tree in UI, visual aggregates + GROUP BY controls, chart/dashboard variable wiring (Phase 6), and listed tests.

---

5.1 Saved Query Model

• 🔴 ⚙️ SavedQueryModel.php — CRUD, filter by workspace and data source, soft delete
• 🟢 🧪 Test: save and retrieve query with complex JSON config

5.2 Visual Query Builder (No-Code)

• 🔴 🎨 Visual mode in app/Views/query/_form.php (create/edit) — table name input + comma-separated columns (no separate visual.php; schema dropdown / per-column checkboxes not implemented)
• 🔴 🎨 Schema-driven table dropdown + column checkboxes with optional aliases
• 🟡 🎨 Filter builder — add/remove filter rows: field, operator (=, !=, >, <, LIKE, IS NULL, IS NOT NULL), value
• 🟡 🎨 Aggregate row — apply COUNT/SUM/AVG/MIN/MAX to numeric columns
• 🟡 🎨 GROUP BY selector — multi-select (backend QueryBuilder::toSQL() supports group_by[]; no UI yet)
• 🟡 🎨 ORDER BY — multiple sort columns, each with ASC/DESC + add/remove rows
• 🟡 🎨 LIMIT input — max rows (default 500); supports {{variable}} in visual fields
• 🟡 ⚙️ QueryBuilder::toSQL() — convert visual config JSON to safe parameterized SQL
• 🟢 ⚙️ Prevent destructive keywords: block DROP, DELETE, UPDATE, INSERT, TRUNCATE in generated SQL
• 🟢 🧪 Test: multi-filter query, aggregate with group by, null filter

5.3 Raw SQL Mode

• 🔴 🎨 CodeMirror 6 editor integration — SQL syntax highlighting, auto-complete (table/column names) (currently plain textarea in _form.php)
• 🔴 🎨 Mode selector — Raw SQL / Visual builder / API query
• 🟡 ⚙️ SQL safety check before execution — regex/parse to block DDL/DML mutations
• 🟡 ⚙️ QueryController::execute() — run sanitized SQL on the selected data source, return JSON results
• 🟢 ⚙️ Enforce query timeout — kill query after 30 seconds
• 🟢 🧪 Test: malicious SQL injection attempt, timeout simulation, empty result set

5.4 Query Variables

• 🔴 ⚙️ Libraries/QueryVariableParser.php — scan query string for {{ var_name }} pattern using regex, return list of variable names
• 🔴 ⚙️ Libraries/QueryVariableResolver.php — resolve system variables ({{today}}, {{now}}, etc.) and substitute user values via PDO bindings; resolveTemplateString() for visual builder identifiers/literals
• 🔴 🎨 Variable panel — Raw SQL + Visual builder: detect / configure / test values; hidden for API mode on create/edit
• 🔴 🎨 Variable config form per detected variable:
  • Label (display name shown to end user)
  • Type: text / number / date / date_range / select / multi_select
  • Default value
  • For select/multi_select: options list (comma-separated or from another query)
  • Required toggle
• 🟡 ⚙️ Persist variable definitions in query_variables table (per saved_query_id) on save/update (dashboard/chart display_config integration pending Phase 6)
• 🟡 🎨 Dashboard view — render a variable input widget per variable above each chart that has variables defined:
  • text → <input type="text">
  • number → <input type="number">
  • date → date picker
  • date_range → dual date range picker
  • select → dropdown
  • multi_select → multi-select dropdown with checkboxes
• 🟡 🎨 On variable value change → re-fetch chart data AJAX with new values, re-render chart without page reload
• 🟡 ⚙️ Global dashboard filter — if multiple charts share a variable with the same name, a single widget controls all of them simultaneously
• 🟡 ⚙️ Resolve built-in system variables server-side before query execution (no user input needed for these)
• 🟢 🎨 Variable widget in Query Builder preview — show input fields for each detected variable so creator can test values before saving
• 🟢 ⚙️ multi_select in raw SQL — QueryVariableResolver::resolve() expands array values to multiple ? placeholders (use inside IN ({{var}}) in SQL)
• 🟢 ⚙️ Public shared dashboards — variable widgets still visible and functional for anonymous viewers
• 🟢 🧪 Test: SQL injection attempt via variable value is blocked by PDO binding; date variable resolves correctly; missing required variable shows validation error; multi-select generates correct IN clause

5.5 API Query Builder

• 🟡 🎨 API query form — endpoint URL, JSON path, grouped sections (headers + field map with add/remove rows)
• 🟡 🎨 HTTP headers — repeatable rows; persisted in saved_queries.api_params as JSON { "headers": [...] }
• 🟡 🎨 JSON path input — e.g. data.results to extract nested array
• 🟡 🎨 Field map — source JSON key → column alias rows
• 🟡 ⚙️ Libraries/ApiConnector::fetch() — variable substitution on URL, headers, extract by JSON path
• 🟢 🧪 Test: nested JSON path, missing field graceful fallback, invalid URL

5.6 Query Preview & Result Table

• 🔴 🎨 "Run Query" button — AJAX call, show spinner while loading
• 🔴 🎨 Result preview table — first 100 rows, dynamic columns, sortable headers, pagination, CSV download, execution log tab
• 🟡 🎨 Row count badge, execution time badge
• 🟡 🎨 Save flow — name/description/meta on same form as builder; POST to store/update saved_queries
• 🟢 🎨 Empty state — centered “No rows returned” message in preview (illustration asset optional)
• 🟢 🧪 Test: 0 rows, 1000+ rows truncated, columns with special characters

5.7 Query Cache

• 🟡 ⚙️ After execution, store result JSON in query_cache with MD5 cache key and TTL
• 🟡 ⚙️ On next execution, check query_cache first; serve from cache if not expired
• 🟢 ⚙️ CI4 Cron task: DeleteExpiredQueryCache — run every 30 minutes to purge expired rows
• 🟢 🧪 Test: cache hit serves faster, cache miss goes to DB, expired cache re-fetches

---

──────────────────────────────────────────

PHASE 6 — Chart Builder

Estimated Time: 6–7 Days

Completion note: Core MVP is in place: ChartModel, SavedQueryRunner, ChartRenderer, ChartController, multi-step builder (saved query first — no separate data-source step), ApexCharts live preview (preview-query / preview-render), POST /chart/{id}/data with CSRF refresh in JSON, chart list with Edit / Duplicate / Delete. Open: public_token generation, saved-query modal vs dropdown (dropdown implemented), unsaved-changes guard, table pagination/sort, legend position + custom hex + X-axis date format in UI, client setInterval refresh + spinner, search/filter, listed tests.

---

6.1 Chart Model

• 🔴 ⚙️ ChartModel.php — CRUD, filter by workspace, soft delete
• 🟡 ⚙️ Public token generator (is_public / public_token) — pending (sharing phase)
• 🟢 🧪 Test: create chart with JSON display_config, retrieve and parse correctly

6.2 Chart Builder UI — Step Flow

• 🔴 🎨 Multi-step chart builder UI (Step 1: Saved query → Type → Fields → Style → Preview & save)
• 🔴 🎨 Step 1 — Choose saved query + run preview (data source implied by query; no separate source step)
• 🔴 🎨 Step 2 — Chart type selector grid (12 types with icons)
• 🟡 🎨 Step 3 — Field mapping: X-axis, Y-axis, Group By, Value (+ combo second metric); columns from preview
• 🟡 🎨 Step 4 — Display settings: title, subtitle, palette presets, legend toggle, data label toggle, Y-axis min/max, number format, grid / smooth / stacked / horizontal bar
• 🟡 🎨 Step navigation — Back / Next / Save; validation before advancing
• 🟡 🎨 Live preview — ApexCharts with real query data (chart/preview-render)
• 🟢 🎨 "Use Saved Query" button — modal to pick query (dropdown on Step 1 covers pick-from-list)
• 🟢 🎨 Unsaved changes warning on browser back/close

6.3 Chart Rendering Engine

• 🔴 ⚙️ Libraries/ChartRenderer.php — chart config + rows → Apex-compatible options (buildPayload)
• 🔴 ⚙️ Renderer: bar, line, area, pie, donut
• 🟡 ⚙️ Renderer: scatter, kpi_card, funnel, gauge, heatmap, combo
• 🟡 ⚙️ table — paginated HTML table with sort (basic truncated table only)
• 🟢 ⚙️ Number formatter — currency (₹/$), percentage, decimals, K/M/B (server + client)
• 🟢 🧪 Test: each chart type with realistic data, empty data, single-row data

6.4 Chart Display Settings

• 🟡 🎨 Color palette presets (Ocean, Forest, Sunset, Mono) (custom hex input — open)
• 🟡 🎨 Legend position selector (top/bottom/left/right/none) in builder UI
• 🟡 🎨 Refresh interval dropdown (Manual / 1 min / 5 min / 15 min / 1 hr) (1 day option — open)
• 🟢 🎨 Date format selector for time-series X-axis (dd/MM, MMM dd, MMM yyyy)
• 🟢 🎨 Stacked bar/area toggle

6.5 Chart Auto-Refresh

• 🟡 🎨 JavaScript: refresh_interval > 0 → setInterval re-fetch
• 🟡 ⚙️ POST /chart/{id}/data — re-run query, JSON render payload (POST + variables_json for CSRF/vars; spec listed GET)
• 🟢 🎨 Refresh spinner overlay while loading
• 🟢 🧪 Test: 1-minute refresh updates data without full page reload

6.6 Chart List & Management

• 🟡 🎨 app/Views/chart/index.php — card grid, type, last updated, data source + query name
• 🟡 🎨 Chart card actions — Edit, Duplicate, Delete (Add to Dashboard / Share — Phase 7+)
• 🟡 ⚙️ ChartController::duplicate() — clone with name suffix (copy)
• 🟢 🎨 Search and filter charts by type or data source
• 🟢 🧪 Test: duplicate preserves all config, delete removes from all dashboards

---

──────────────────────────────────────────

PHASE 7 — Dashboard Builder

Estimated Time: 6–7 Days

──────────────────────────────────────────

7.1 Dashboard Model

• 🔴 ⚙️ DashboardModel.php — CRUD, filter by workspace, soft delete, public token generator
• 🔴 ⚙️ DashboardWidgetModel.php — CRUD widgets per dashboard, store grid position
• 🟢 🧪 Test: save layout_config JSON and retrieve widget positions correctly

7.2 Dashboard List

• 🔴 🎨 app/Views/dashboard/index.php — card grid of dashboards with pinned section at top
• 🟡 🎨 Dashboard card — name, description, chart count, last updated, share status badge
• 🟡 🎨 Create Dashboard button — modal with name + description input
• 🟢 🎨 Pin/Unpin dashboard toggle
• 🟢 🧪 Test: 0 dashboards empty state, pinned order preserved on reload

7.3 Dashboard View Mode

• 🔴 🎨 app/Views/dashboard/view.php — render all widgets in their grid positions
• 🔴 🎨 Render chart widgets: fetch data via AJAX, render ApexCharts
• 🟡 🎨 Render text widgets — parse Markdown to HTML using marked.js
• 🟡 🎨 Render image widgets — <img> with configurable object-fit
• 🟡 🎨 Global filter widgets — date range picker and dropdown filter
• 🟡 ⚙️ When global date filter changes, re-fetch all chart data with new date params injected into queries
• 🟢 🎨 Fullscreen button — expand dashboard to fill viewport, hide sidebar/topbar
• 🟢 🧪 Test: mixed widget types, dashboard with 20+ charts, date filter propagation

7.4 Dashboard Edit Mode (Drag & Drop)

• 🔴 🎨 Integrate gridstack.js or Muuri for drag-and-drop grid layout
• 🔴 🎨 "Edit Layout" toggle activates draggable/resizable mode on all widgets
• 🟡 🎨 Resize handles on widget cards — drag corner to resize (min 1×1, max 4×3 units)
• 🟡 🎨 "Add Widget" button in edit mode — opens modal to pick chart, text, image, or filter widget
• 🟡 🎨 Remove widget button (✕) visible only in edit mode
• 🟡 ⚙️ "Save Layout" — AJAX POST grid positions (x, y, w, h) per widget to dashboard_widgets
• 🟡 🎨 "Discard Changes" — reload original layout from DB without saving
• 🟢 🎨 Widget title override input (optional per-widget title different from chart name)
• 🟢 🧪 Test: save layout, reload — positions preserved exactly; concurrent edit race condition

7.5 Dashboard Settings

• 🟡 🎨 Dashboard settings panel — name, description, theme (light/dark/system), refresh interval
• 🟡 ⚙️ DashboardController::updateSettings() — update name, theme, refresh
• 🟢 🎨 Danger zone — delete dashboard with confirmation typing
• 🟢 🧪 Test: theme toggle persists on reload, refresh interval auto-starts

---

──────────────────────────────────────────

PHASE 8 — Alerts & Notifications

Estimated Time: 4–5 Days

──────────────────────────────────────────

8.1 Alert Model & CRUD

• 🔴 ⚙️ AlertModel.php — CRUD, filter by workspace, soft delete
• 🔴 ⚙️ AlertHistoryModel.php — insert triggered log, fetch recent history per alert
• 🟡 🎨 app/Views/alert/index.php — list alerts with status (OK/Triggered/Muted), last triggered time
• 🟡 🎨 app/Views/alert/create.php — form: chart selector, metric field, condition, threshold, channels
• 🟡 🎨 app/Views/alert/edit.php — edit + mute/unmute toggle
• 🟢 🧪 Test: create alert with all fields, edit threshold, delete alert

8.2 Alert Engine (Background Check)

• 🔴 ⚙️ Libraries/AlertEngine.php — load active alerts, run chart query, compare value to threshold
• 🔴 ⚙️ CI4 Cron (php spark alert:check) — runs every minute via system cron job
• 🟡 ⚙️ AlertEngine::evaluate() — conditions: gt, lt, eq, gte, lte
• 🟡 ⚙️ Skip alert if is_muted_until is in the future
• 🟡 ⚙️ On trigger: send Email via CI4 Email library, send Slack message via Webhook HTTP POST
• 🟡 ⚙️ Log result to alert_history — value, channels notified, status (sent/failed/muted)
• 🟢 ⚙️ Prevent duplicate notifications — if same alert triggered within last 5 minutes, skip
• 🟢 🧪 Test: threshold breach triggers notification, muted alert is skipped, failed webhook logs error

8.3 Alert Notifications UI

• 🟡 🎨 Alerts sidebar panel (as in POC) — show recent triggered alerts with current value
• 🟡 🎨 Alert history sub-page — paginated log of all past triggers per alert
• 🟡 🎨 "Mute for" button — snooze alert for 1h / 4h / 24h
• 🟢 🎨 Red badge count on sidebar Alerts nav item for active triggered alerts
• 🟢 🧪 Test: snooze clears badge, alert re-triggers after snooze expires

---

──────────────────────────────────────────

PHASE 9 — Sharing & Embedding

Estimated Time: 3–4 Days

──────────────────────────────────────────

9.1 Public Share Links

• 🔴 ⚙️ SharedLinkModel.php — create token, find by token, increment view count
• 🔴 ⚙️ SharingController::generate() — create record in shared_links, return public URL
• 🔴 ⚙️ Public route GET /share/{token} — no auth required, load dashboard/chart view-only
• 🟡 🎨 Share modal in dashboard/chart view — show public URL with copy button, QR code
• 🟡 🎨 Optional password field — bcrypt hash stored, prompt on public page if set
• 🟡 🎨 Optional expiry date picker — after expiry show "This link has expired" page
• 🟡 ⚙️ SharingController::revoke() — set is_active = 0, invalidate link
• 🟢 🎨 View count display in share modal ("Viewed 42 times")
• 🟢 🧪 Test: password protection, expired link, revoked link, view count increment

9.2 iFrame Embed

• 🟡 🎨 Embed tab in share modal — show iframe HTML snippet with correct URL
• 🟡 ⚙️ Public share route: set X-Frame-Options: ALLOWALL header for embed URLs
• 🟡 🎨 Embed view — stripped layout (no sidebar/topbar), chart/dashboard only
• 🟢 🎨 Embed size presets (640×480, 800×600, 1200×800, custom)
• 🟢 🧪 Test: embed renders in external <iframe>, responsive sizing

9.3 Export

• 🟡 🎨 Export chart as PNG — use ApexCharts chart.dataURI() + FileSaver.js client-side
• 🟡 🎨 Export chart data as CSV — AJAX to GET /chart/{id}/export?format=csv
• 🟡 ⚙️ ChartController::export() — re-run query, format as CSV using PHP fputcsv, stream download
• 🟡 ⚙️ Export chart data as Excel — use PhpSpreadsheet library to generate .xlsx
• 🟢 ⚙️ Log each export to chart_exports table
• 🟢 🧪 Test: CSV column headers match query columns, Excel opens without errors

---

──────────────────────────────────────────

PHASE 10 — REST API

Estimated Time: 3–4 Days

──────────────────────────────────────────

10.1 API Authentication

• 🔴 ⚙️ ApiAuthFilter.php — extract Bearer token from Authorization header, find user in users.api_token
• 🔴 ⚙️ Return 401 Unauthorized JSON if token missing or invalid
• 🟢 ⚙️ Rate limiter — 60 requests per minute per token using CI4 Throttler
• 🟢 🧪 Test: valid token, invalid token, missing token, rate limit exceeded returns 429

10.2 API Endpoints

• 🔴 ⚙️ Api/WorkspaceController — GET /api/v1/workspaces, POST /api/v1/workspaces
• 🔴 ⚙️ Api/DataSourceController — GET /api/v1/workspaces/{id}/datasources, POST, DELETE
• 🔴 ⚙️ Api/ChartController — GET /api/v1/workspaces/{id}/charts, POST, GET /api/v1/charts/{id}/data
• 🟡 ⚙️ Api/DashboardController — GET, POST, PUT /api/v1/dashboards/{id}
• 🟡 ⚙️ Api/QueryController — POST /api/v1/datasources/{id}/query — run ad-hoc query
• 🟡 ⚙️ Api/AlertController — GET /api/v1/alerts, POST, DELETE
• 🟢 ⚙️ Consistent JSON response format: { success, data, message, errors }
• 🟢 📄 Generate Postman collection JSON for all API endpoints
• 🟢 🧪 Test: each endpoint with valid/invalid workspace membership, missing fields

---

──────────────────────────────────────────

PHASE 11 — Audit Logs

Estimated Time: 2–3 Days

──────────────────────────────────────────

• 🔴 ⚙️ Libraries/AuditLogger.php — static log(action, resource_type, resource_id, old, new) method
• 🔴 ⚙️ Hook AuditLogger::log() into key controller actions: create/update/delete chart, dashboard, data source; login/logout; role change; share link created/revoked
• 🟡 🎨 app/Views/audit/index.php — paginated table of audit logs
• 🟡 🎨 Filters — by user, by action type, by date range
• 🟡 🎨 Log detail modal — show old_value and new_value JSON diff view
• 🟢 ⚙️ Auto-capture ip_address and user_agent from CI4 IncomingRequest
• 🟢 ⚙️ CI4 Cron: PurgeOldAuditLogs — delete logs older than 90 days (configurable)
• 🟢 🧪 Test: login creates log entry, chart delete logs old_value, IP captured correctly

---

──────────────────────────────────────────

PHASE 12 — Settings & Configuration

Estimated Time: 2 Days

──────────────────────────────────────────

• 🟡 ⚙️ SettingsModel.php — get/set by key and workspace_id (null = global)
• 🟡 🎨 app/Views/settings/workspace.php — name, logo, timezone, default refresh, default theme
• 🟡 🎨 app/Views/settings/notifications.php — SMTP test button, Slack webhook test button
• 🟡 🎨 app/Views/admin/settings.php (super admin) — allow_registration toggle, max workspaces
• 🟢 🎨 Theme toggle (light/dark) saved to users table or localStorage with CI4 session sync
• 🟢 🧪 Test: workspace settings persist after logout, global settings affect new registrations

---

──────────────────────────────────────────

PHASE 13 — Polish, Testing & Security

Estimated Time: 4–5 Days

──────────────────────────────────────────

13.1 UI/UX Polish

• 🟡 🎨 Loading states — skeleton shimmer on all chart cards while data loads
• 🟡 🎨 Empty states — friendly illustrations for no charts, no data sources, no dashboards
• 🟡 🎨 Toast notification system — success, error, warning toasts globally
• 🟡 🎨 Breadcrumb navigation on all pages
• 🟡 🎨 Responsive layout — sidebar collapses to hamburger on mobile
• 🟢 🎨 Keyboard shortcuts — N = new chart, D = go to dashboards, ESC = close modal
• 🟢 🎨 Dark mode — full dark theme toggle applied to all views
• 🟢 🎨 404 and 500 custom error pages

13.2 Security Hardening

• 🔴 ⚙️ Enable CI4 CSRF protection on all POST forms (Config/Security.php)
• 🔴 ⚙️ Enable CI4 XSS clean on all user inputs via IncomingRequest::getVar()
• 🔴 ⚙️ Validate all user-supplied SQL through allowlist check — block DROP, ALTER, GRANT, etc.
• 🟡 ⚙️ Set HttpOnly, Secure, SameSite=Strict on session cookie (Config/Cookie.php)
• 🟡 ⚙️ Add Content Security Policy headers via ResponseTrait or middleware
• 🟡 ⚙️ Add rate limiting on login route — block after 5 failed attempts for 15 minutes
• 🟢 ⚙️ Sanitize all file uploads — allow only jpg, png, csv; validate MIME type server-side
• 🟢 🧪 Test: CSRF token rejection, XSS payload in chart name, SQL injection in query field

13.3 Testing

• 🟡 🧪 PHPUnit: write feature tests for Auth module (register, login, logout, reset)
• 🟡 🧪 PHPUnit: write feature tests for Chart CRUD
• 🟡 🧪 PHPUnit: write feature tests for Dashboard CRUD and widget save
• 🟡 🧪 PHPUnit: write unit tests for ChartRenderer, QueryBuilder::toSQL(), AlertEngine::evaluate()
• 🟢 🧪 Browser test (manual): end-to-end user flow — register → create workspace → add data source → build chart → add to dashboard → share
• 🟢 🧪 Load test: dashboard with 10 charts, each auto-refreshing every 5 minutes — check DB load

---

──────────────────────────────────────────

PHASE 14 — Deployment & Documentation

Estimated Time: 2–3 Days

──────────────────────────────────────────

14.1 Deployment

• 🔴 ⚙️ Set CI_ENVIRONMENT = production in .env
• 🔴 ⚙️ Configure web server (Apache .htaccess or Nginx server {}) to point root to public/
• 🟡 ⚙️ Set up system cron jobs:
  • * * * * * php /var/www/chart-board/spark alert:check
  • */30 * * * * php /var/www/chart-board/spark cache:purge
  • 0 2 * * * php /var/www/chart-board/spark logs:purge
• 🟡 ⚙️ Configure Redis for production cache (update Config/Cache.php)
• 🟢 ⚙️ Set up log rotation for writable/logs/
• 🟢 ⚙️ Production MySQL — create read-only user for data source connections
• 🟢 ⚙️ Set up SSL certificate (Let's Encrypt)
• 🟢 📄 Create DEPLOYMENT.md — server setup steps, Nginx config, cron setup

14.2 Documentation

• 🟡 📄 Complete README.md — finalize installation steps, config reference
• 🟡 📄 CONTRIBUTING.md — code style (PSR-12), branch naming, PR checklist
• 🟢 📄 Postman Collection JSON for full REST API
• 🟢 📄 In-app help tooltips on query builder and chart builder fields
• 🟢 📄 Changelog CHANGELOG.md — v1.0.0 feature list

---

📊 Summary

Phase	Name	Est. Days	Priority
1	Foundation & Setup	3–4	🔴
2	Auth & User Management	4–5	🔴
3	Workspace Management	3–4	🔴
4	Data Source Connections	5–6	🔴
5	Query Builder	5–6	🔴
6	Chart Builder	6–7	🔴
7	Dashboard Builder	6–7	🔴
8	Alerts & Notifications	4–5	🟡
9	Sharing & Embedding	3–4	🟡
10	REST API	3–4	🟡
11	Audit Logs	2–3	🟢
12	Settings	2	🟢
13	Polish, Testing & Security	4–5	🟡
14	Deployment & Docs	2–3	🟡
Total		~56–68 days

Note: Phases 1–7 are the critical path (core MVP). Phases 8–14 are enhancement and hardening layers. A team of 2 developers can parallelize frontend and backend tasks within each phase to cut delivery time roughly in half.

---

Chart-Board · Task Breakdown v1.0 · Generated for CI4 Build