MariaDB 10.11 (MySQL-compatible), utf8mb4 throughout. The canonical schema lives in setup/workflow_database.sql — it's imported wholesale by the installer (setup/index.php) on first run. This document is a human-readable map of it, grouped by domain, plus notes on which tables are actually used.
28 tables total. Two naming prefixes: base_* (identity/access/config — platform-level) and proj_* (project-management domain). kb_entries, chat_*, and user_notes stand alone.
The team roster. role_id → base_roles. password is a bcrypt hash. password_reset_required and status (active/disabled) exist and are read at login (status = 1 required to authenticate) but there's no UI flow that currently forces a reset on password_reset_required = 1.
Seven seeded roles: ADMIN, TECH_LEAD, PRODUCT_OWNER, PROJECT_MANAGER, DEVELOPER, QA_ENGINEER, UI_UX_DESIGNER. New roles can be added via Settings (POST /settings/api_add_role).
The list of nav items (Dashboard, Projects, Knowledge Hub, ADRs, Release Notes, Chat, Updates, Note Keeper, Settings). route maps to a controller name. sort_order exists but the actual nav render order comes from a hardcoded array in top.phtml — see ARCHITECTURE.md.
Many-to-many join: which roles see which modules in the sidebar (and, for Settings/Controlpanel, page access too). Rewritten per-role (DELETE + bulk insert for that one role_id, not a global TRUNCATE) by Settings::api_save_role_access().
CRUD permission catalog for the four shared-content modules: {PROJECT,KNOWLEDGE_HUB,ADRS,RELEASES}_{CREATE,EDIT,DELETE} (12 rows, permission_code PK, each tied to a module_id). Enforced — see below.
Many-to-many join: which roles have which of the 12 CRUD permissions above. Actually enforced — every api_create_*/api_update_*/api_delete_* method on the four CRUD controllers calls Auth::require_permission($code) (application/helper/auth.php), which checks this table's grants (loaded into $_SESSION['user']['permissions'] at login). Rewritten per-role by Settings::api_save_role_access(), same as base_role_modules. See ARCHITECTURE.md §3 and MODULES.md for the default grants and the RBAC UI.
One row per login (base_user_sessions.session_id mirrors $_SESSION['session_id']). Written on login/logout (ip, user_agent, started_on, ended_on). Bookkeeping only — nothing reads it back today (no "active sessions" UI, no forced-logout capability).
Columns suggest signed-webhook/integration replay protection (nonce, direction IN/OUT, peer). Unused — no code references this table.
Generic key/value store (config_key PK). Seeded with workspace branding (workspace_name, workspace_subtitle), email settings (daily_email_recipients, weekly_email_recipients, from_email, from_name, smtp_host, smtp_port, smtp_user, smtp_pass), Control Panel bottleneck thresholds (bottleneck_blocked_days, bottleneck_stale_backlog_days, bottleneck_overload_task_count), and workspace_template (empty until the Admin picks one on first Dashboard load — see MODULES.md; once set, it's a one-way flag, nothing currently reads the specific value back except to check whether it's empty). Read/written by Settings, read by Updates, Controlpanel, and Dashboard. The SMTP fields are stored but nothing actually sends mail yet — see the Updates module note in MODULES.md.
Security/activity audit trail — category, action, entity_type/entity_id, status, JSON metadata, ip. Written via Auth::audit(), currently only called for login success/failure and logout. Controller::logSystemActivity() is a legacy shim that also forwards here.
Looks like a more structured, role-aware version of proj_notifications (has role_code, severity, status enum with dismissed, a JSON metadata column with a CHECK (json_valid(...)) constraint). Unused — the live notification system is proj_notifications instead. Don't build against this table without checking first; it may be intended to replace proj_notifications but that migration hasn't happened.
Top-level project record: name, description, color (for sidebar dots), status (PRE_FLIGHT → AWAITING_REVIEW → ACTIVE → ... → CLOSED/ARCHIVED), tags. owner_id defaults to created_by at creation but is independently editable — it's the Control Panel's escalation target (see MODULES.md).
Many-to-many: which users can see/access a project. Project_model::getProjects()/getProject() both join through this — membership is the access-control boundary for the Projects module, not role or permission.
Named feature groupings within a project (project_id, name). Flat, no status/description columns.
project_id, name, start_date/end_date, is_active flag (drives the Dashboard's "active sprints" count).
The core work-item table. project_id (required), feature_id/sprint_id/parent_id (all optional — parent_id self-references proj_tasks.id for subtasks), status (free-text), completed_at (nullable timestamp, set/cleared by Projects::api_update_task() whenever status transitions to/from done, and set on CSV-import rows created with status='done'), priority (e.g. med/high/urgent), assignee_id → base_users, estimated_hours, due_date, tags. status/priority are plain varchar, not enums — validate against expected values in application code if you add new statuses, since nothing at the DB layer will stop a typo.
status has two different spellings of "in progress" in the wild: the task-edit UI (projects/task.phtml) writes progress, but the CSV importer (Projects::api_import_csv()) validates against in_progress. Nothing reconciles these — a task's actual status string depends on which path created it. Any query that means "is this in progress" needs to check both (status IN ('progress', 'in_progress')), which is what Updates::index() now does. If you're adding a new "in progress" check elsewhere, do the same, or better, pick one spelling and migrate the other.
status_reason (varchar, nullable), status_changed_at (timestamp, nullable), and snoozed_until (date, nullable) support the expanded 8-value status vocabulary (todo/progress/review/on_hold/blocked/snoozed/cancelled/done) and the Control Panel's bottleneck detection — see MODULES.md → Projects & Tasks and → Control Panel for the validation rules and how they're queried.
sequence_order (int, nullable) powers My Queue — each assignee's personal, cross-project ranked work order. It's only ever compared between tasks sharing the same assignee_id; there's no per-user table because the column itself is the per-user ranking (two different users' sequence_order = 0 don't collide, since queries always filter by assignee_id first). NULL means "not manually ranked yet" and sorts after ranked tasks, falling back to priority then due date. See MODULES.md.
Threaded comments on a task — task_id, user_id, message. Flat (no reply-to), ordered by created_at ASC.
Link/file attachments on a task — task_id, type, url, name.
Many-to-many self-join on proj_tasks: task_id depends on depends_on_task_id (must finish first). Unique on the pair. Deliberately does not touch proj_tasks.status — a task's dependency-blocked state is computed at read time (Project_model::isBlockedByDependency(), Controlpanel's "Blocked by Dependency" query), not written into the status column, so a manually-set status like review is never silently overwritten by dependency state. Project_model::wouldCreateDependencyCycle() walks the graph (BFS) before every insert to reject cycles. See MODULES.md and MODULES.md.
Reusable, project-scoped task blueprints (proj_task_templates: project_id, name, description) each holding an ordered list of task rows (proj_task_template_items: title, priority, sort_order). assignee_type (member/role/unassigned) picks which of the two nullable assignee columns is live: member uses assignee_user_id directly; role stores assignee_role_id and is deliberately left unresolved here — resolution against the target sprint's actual project members happens at apply-time in Project_model::applyTaskTemplate(), not when the template is saved. See MODULES.md.
The actually-used notification table (see log_notifications above for the unused alternative). user_id, type (mention/chat/...), title, message, link_url, is_read. Populated by Project_model::addNotification(), currently only from Team Chat mentions.
See MODULES.md. visibility ENUM (global/project/feature/self, default global) plus project_id/feature_id (both nullable) gate who can see each ADR — enforced in Adrs::index(), not just hidden client-side.
Optionally scoped to a project_id (nullable = org-wide). Has no visibility column and no access filtering — every logged-in user sees every release note regardless of project membership, same gap ADRs and Knowledge Hub had before their visibility model was added. Follow the proj_adrs/kb_entries pattern if this needs the same treatment.
Knowledge Hub entries. parent_id self-references for tree nesting (assembled in PHP by Knowledgehub::buildTree(), not a recursive CTE). type (article/video/knowledgedoc/file), url, tags. Has a folder_path column that's only referenced by the dead Knowledge_model class — the live tree-building code ignores it in favor of parent_id. visibility ENUM (global/project/feature/self, default global) plus project_id/feature_id (both nullable) gate who can see each entry — same model and enforcement as proj_adrs, see MODULES.md.
If a parent folder isn't visible to the current user (filtered out before buildTree() runs), its children simply never surface anywhere in the tree either — there's no orphan-leakage path, but also no explicit handling; it falls out naturally from how the tree is built from root down.
type ENUM(public, dm) — only public is ever created or queried by current code; DM channels are schema-ready but not implemented.
channel_id, user_id, message, attachment_url, attachment_name, attachment_type (image/file, nullable — set together when a message carries an upload). Polled by the frontend every 3 seconds; older history is paged in on scroll-up (see MODULES.md — Team Chat).
message is ciphertext (AES-256-GCM, application/helper/crypto.php), not plaintext — attachment_url/attachment_name/attachment_type are not encrypted. See ARCHITECTURE.md §6 for the encryption model and why this table is excluded from Spotlight search.
Added for the Note Keeper module. Private to its owner — every query filters on user_id.
| Column | Notes |
|---|---|
user_id |
Owner. Not a foreign key at the DB level (matches the rest of the schema's convention — no FK constraints anywhere), but every controller query enforces it. |
scope_type |
ENUM global / project / feature / sprint / task |
project_id |
Set for project/feature/sprint/task scope; NULL for global |
scope_id |
The specific feature/sprint/task ID; NULL for global/project scope |
title, body |
Ciphertext (AES-256-GCM, application/helper/crypto.php) — title was widened from varchar(255) to text to fit the encrypted+base64 output. See ARCHITECTURE.md §6. |
tags |
Plain comma-separated text (not encrypted), same convention as proj_tasks/proj_adrs/kb_entries — but unlike those tables, user_notes is excluded from Spotlight search entirely (title/body being ciphertext made the encrypted columns unsearchable, so the whole module was dropped from search rather than leaving a tags-only partial result) |
created_at, updated_at |
updated_at auto-touches on UPDATE (ON UPDATE current_timestamp()) |
- No foreign key constraints anywhere in this schema — every relationship (
project_id,user_id,assignee_id, ...) is an unenforcedint. Referential integrity is entirely the application's responsibility. In practice the app mostly sidesteps the question:Projects::api_delete_project()is a soft-delete (UPDATE proj_projects SET status = 'Archived', filtered out of list queries), not a realDELETE— so there's currently no code path that actually deletes a project row and would need to worry about orphaning its tasks/features/sprints. If you ever add a hard-delete, you'd need to cascade manually. tagscolumns are plain comma-separatedvarchar(255), not a join table — consistent acrossproj_tasks,proj_adrs,proj_projects,kb_entries, anduser_notes. If you need real tag taxonomy (rename-once-updates-everywhere, autocomplete, etc.) this would need a realtags/taggablesjoin table; today it'sLIKE '%tag%'matching inSearch::api_global_search()— exceptuser_notes.tags, which isn't searched at all since the rest of that table is encrypted (see ARCHITECTURE.md §6).- Every table uses
InnoDB/utf8mb4_general_ci— match this if you add one. - Status/type columns are
varchar, notENUM, except where explicitly noted above (chat_channels.type,user_notes.scope_type,log_notifications.severity/status). Prefer matching the existing convention for a given table rather than introducing an ENUM into a table that currently uses free-text, since application code may already do loose string comparisons against it.