Repository navigation
Projects Internals 2 Schema
Every table the Projects module owns, column by column: the CREATE TABLE as it stands in database/freeitsm.sql, what the non-obvious columns mean and which code writes and reads them, the foreign keys (and which of them Database Verification adds to an upgraded install), and the ...Ready() guard that keeps the code working before Database Verification has created a table or column. It also explains how a schema change is made in this repo and ends with SQL queries that are useful when you are working on the module. Part 1 explains the vocabulary (services, loadForActor(), "worked out, never stored") this page uses.
Pages in this series
- 1 Architecture
- 2 Schema
- 3 Services and API
- 4 Plan, Timeline and dependencies
- 5 Health and charts
- 6 Governance
- 7 Alerts, reports and AI
- 8 Connections, templates, people
- 9 Demo data, testing and traps
- 10 Contractors
- The shape of it
- Rules that apply to every table
- Core: projects, stages, history, tasks
- Connections: the seven link tables
- People, scope and RACI
- RAID and tolerances
- Plan: milestones, dependencies, flow, asset targets
- Money: budget lines and labour rates
- Governance: baselines, change requests, gates, benefits
- Reports and templates
- Tables other modules own that point at projects
- Foreign keys
- The Ready guards
- How a schema change is made
- Useful queries
tenants ─┐
▼
analysts ◄── owner/created/approval ── projects ──────────────────────────┐
│ ▲ │
┌───────────────┬───────────────┼──┼──────────────┬──────────────┐ │
▼ ▼ ▼ │ ▼ ▼ │
project_stages project_audit tasks.project_id project_members project_items
│ (gate_*) tasks.project_stage_id │ ▲ │ ▲ parent_id
│ tasks.estimate_hours │ │ │ │
│ └──┴─ project_raci ┘
├── project_milestones (stage_id SET NULL)
├── project_gate_items (stage_id CASCADE)
├── project_tolerances (stage_id, unused yet)
└── project_baselines.stage_id
project_raid ── project_raid_tasks ── tasks
project_assets / _changes / _tickets / _contracts / _cmdb_objects / _knowledge_articles / _problems
project_asset_targets ── project_asset_target_snapshots
project_budget_lines project_labour_rates (scope default | project | analyst, no FK)
project_baselines ◄── project_change_requests.baseline_id
project_benefits ── project_benefit_measures
project_task_flow (composite PK) task_dependencies (tasks ↔ tasks)
project_reports project_templates (no project_id) project_roles (install-wide)
Thirty-three tables plus three columns on tasks (the Ask AI assistant's two, project_ai_threads and project_ai_messages, arrived last - §10). Everything except project_roles, project_templates and project_labour_rates hangs off a project, and everything except task_dependencies and project_raid_tasks names its project directly in a project_id column (those two hang off tasks and RAID entries).
-
Every module table carries
is_demo,TINYINT(1) NOT NULL DEFAULT 0, used by System -> Demo data: the importer sets it to 1 on what it inserts, so removing the demo removes exactly those rows and nothing anybody typed. The column is in bothdatabase/freeitsm.sqlandincludes/db_verify_schema.php, so an upgraded install gains it on Verification - all 33 tables on this page carry it (#2240, #2246), including the install-wide ones (project_roles,project_templates,project_labour_rates) and the ones without aproject_id(task_dependencies,project_raid_tasks,project_benefit_measures,project_asset_target_snapshots). The demo rows themselves are generated:php scripts/gen_projects_demo.phpwritesdatabase/demo-data/tasks.json. See part 9 and the Demo data developer guide. -
Dates are
DATE, moments areDATETIMEin UTC. Every write usesUTC_TIMESTAMP()/UTC_DATE()orgmdate(). - Nothing derived is stored. No progress, health (except the person's override and what the alert scan last saw), exception, RAID score, milestone state, benefit state, critical path or "done" for a document or change gate item. Each is worked out on read - see the column notes below for where.
-
created_by_*and other "who" columns areON DELETE SET NULL. Deleting an analyst never deletes project data. -
Children cascade with the project in the schema, and are deleted by hand as well.
ProjectsService::deleteProject()deletes every child table itself, each in its owntry, because an upgraded install whose foreign keys failed to add (or a table Verification created without one) has no cascade to rely on, and a table not created yet must never stop a delete:
// includes/services/projects.php - deleteProject()
$st = $conn->prepare("UPDATE tasks SET project_id = NULL, project_stage_id = NULL WHERE project_id = ?");
$st->execute([$id]);
$detached = $st->rowCount();
// Benefit measurements (3.3.0), then their benefits below.
try { $conn->prepare("DELETE m FROM project_benefit_measures m JOIN project_benefits b ON b.id = m.benefit_id WHERE b.project_id = ?")->execute([$id]); } catch (Throwable $e) { /* not created yet */ }
// RAID actions first (3.3.0): joined to the entries about to go; the tasks stay.
try { $conn->prepare("DELETE rt FROM project_raid_tasks rt JOIN project_raid r ON r.id = rt.raid_id WHERE r.project_id = ?")->execute([$id]); } catch (Throwable $e) { /* not created yet */ }
foreach (['project_raci', 'project_members', 'project_items', 'project_raid', 'project_tolerances', 'project_budget_lines', 'project_reports', 'project_milestones', 'project_change_requests', 'project_baselines', 'project_benefits', 'project_gate_items', 'project_task_flow'] as $t) {
try { $conn->prepare("DELETE FROM `$t` WHERE project_id = ?")->execute([$id]); } catch (Throwable $e) { /* not created yet */ }
}
// A project's own hourly rate (3.3.0 budget): project_labour_rates has no
// foreign key - scope + ref_id point at different tables - so by hand.
try { $conn->prepare("DELETE FROM project_labour_rates WHERE scope = 'project' AND ref_id = ?")->execute([$id]); } catch (Throwable $e) { /* not created yet */ }
foreach (['project_stages', 'project_audit'] as $t) {
$conn->prepare("DELETE FROM `$t` WHERE project_id = ?")->execute([$id]);
}
require_once __DIR__ . '/../projects/links.php';
projectLinksDeleteAll($conn, $id);
try { $conn->prepare("UPDATE status_planned SET project_id = NULL WHERE project_id = ?")->execute([$id]); } catch (Throwable $e) { /* not created yet */ }
$conn->prepare("DELETE FROM projects WHERE id = ?")->execute([$id]);Asset targets (and their snapshots) are not in that list; they go by the fk_patg_project / fk_patgs_target cascades. Project-scope rows in project_labour_rates are deleted by scope = 'project' AND ref_id (#2242), because that table has no project_id and no FK. Rows in task_dependencies stay, because they belong to tasks and the tasks survive.
A new child table must be added to that list.
CREATE TABLE IF NOT EXISTS `projects` (
`id` INT NOT NULL AUTO_INCREMENT,
`tenant_id` INT NULL,
`name` VARCHAR(200) NOT NULL,
`summary` TEXT NULL, -- a paragraph: what and why
`goal` VARCHAR(500) NULL, -- one sentence: what "done" means
-- A key into the code-defined presets (includes/projects/methodologies.php).
-- A LENS over one data model: switching it converts and deletes nothing.
`methodology` VARCHAR(20) NOT NULL DEFAULT 'simple', -- simple | staged | agile
`status` VARCHAR(20) NOT NULL DEFAULT 'proposed', -- proposed | active | on_hold | closed | cancelled
-- 'auto' = worked out from the plan; anything else is a person's call, and
-- health_note says why.
`health` VARCHAR(10) NOT NULL DEFAULT 'auto', -- auto | green | amber | red
`health_note` VARCHAR(500) NULL,
`priority` VARCHAR(10) NOT NULL DEFAULT 'medium', -- 3.3.0: low | medium | high | critical (the words are a setting)
`visibility` VARCHAR(10) NOT NULL DEFAULT 'everyone', -- 3.3.0: everyone | members (includes/projects/visibility.php)
`report_schedule` VARCHAR(12) NOT NULL DEFAULT 'off', -- 3.3.0: off | weekly | fortnightly | monthly - a draft report each period
`report_schedule_kind` VARCHAR(20) NOT NULL DEFAULT 'highlight', -- highlight | checkpoint | exception
`owner_analyst_id` INT NULL, -- the project manager
`start_date` DATE NULL,
`target_end_date` DATE NULL,
`actual_end_date` DATE NULL,
-- The project's identity on its card: a colour from a fixed palette and an
-- icon key from a fixed set (validated by the service, never free CSS/SVG).
`colour` VARCHAR(20) NOT NULL DEFAULT 'coral',
`icon` VARCHAR(30) NOT NULL DEFAULT 'rocket',
-- Phase 2. The business case (Staged projects - reviewed at every gate) and
-- the tailoring: which tools this project uses, as JSON, against its method's
-- defaults. NULL = the method's defaults.
`business_case` TEXT NULL,
`tailoring` VARCHAR(500) NULL,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`closed_datetime` DATETIME NULL,
-- What the alert scan last SAW (includes/projects/alerts.php): the health
-- shown and the tolerance breaches, so an alert fires on a change only and
-- re-arms once it clears. NULL health = never scanned: the first scan only
-- records, so an upgrade does not ring every bell at once.
`alert_health` VARCHAR(10) NULL,
`alert_exceptions` VARCHAR(100) NULL,
-- The project's budget currency (ISO 4217), stamped when it is created and
-- never derived on read: changing the install's default later must not
-- relabel a budget that already exists (includes/projects/budget.php).
`currency` CHAR(3) NULL,
-- Intake and approval (3.3.0) - includes/projects/intake.php. The case for
-- the project is business_case; these are the proposal's own figures, who
-- proposed it when that was not an analyst (a form), and the approval.
-- approval_status: NULL = needs none (and every project before 3.3.0),
-- pending, approved, rejected.
`estimated_cost` DECIMAL(18,2) NULL,
`estimated_benefit` TEXT NULL,
`approval_status` VARCHAR(10) NULL,
`approval_by_id` INT NULL,
`approval_datetime` DATETIME NULL,
`approval_notes` TEXT NULL,
`form_submission_id` INT NULL,
`proposed_by_name` VARCHAR(200) NULL,
`proposed_by_email` VARCHAR(255) NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- set by the demo data importer (#1297)
PRIMARY KEY (`id`),
KEY `idx_projects_tenant` (`tenant_id`),
KEY `idx_projects_approval` (`approval_status`),
KEY `idx_projects_status` (`status`),
KEY `idx_projects_owner` (`owner_analyst_id`),
CONSTRAINT `fk_projects_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_projects_owner` FOREIGN KEY (`owner_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_projects_created_by` FOREIGN KEY (`created_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_projects_approval_by` FOREIGN KEY (`approval_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_projects_submission` FOREIGN KEY (`form_submission_id`) REFERENCES `form_submissions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Who writes which column. Every person-settable column is in ProjectsService::fieldMap() and is written only through createProject() / updateProject() (UI, REST API, templates, the create_project workflow action). The rest:
| Column(s) | Written by | Read by / meaning |
|---|---|---|
tenant_id |
createProject() via storeTenant() (Default stored as NULL). Not in fieldMap() - a project never moves company |
assertScope(), activeTenantReadFilter()
|
methodology |
fieldMap() enum of projectMethodologies() keys; default from project_default_method
|
projectEnabledTools(), saveStage(), applyMethodology()
|
status |
fieldMap(); going to closed / cancelled stamps closed_datetime (and actual_end_date for closed if unset), reopening clears it. Pending proposals may only be proposed / cancelled (projectProposalBlocksStatus()). decideProposal() sets it directly |
everything |
health, health_note
|
fieldMap(). auto = worked out; anything else is a person's override and wins in shown_health
|
projectDecorate() |
priority (3.3.0)
|
fieldMap() enum projectPriorities()
|
portfolio sort, chips. Before Verification projectPriorityColumn() selects 'medium' AS priority
|
visibility (3.3.0)
|
fieldMap(); default project_default_visibility; a change needs team or Manage Projects |
includes/projects/visibility.php (part 1 §8) |
report_schedule, report_schedule_kind (3.3.0)
|
ProjectReportsService::setSchedule() (reports.php action schedule) |
ProjectReportsService::runSchedules(). Guard: ProjectReportsService::scheduleReady()
|
owner_analyst_id |
fieldMap() type analyst (active only); defaults to the creator |
the project manager: permissions, the bell, Watchtower scope |
colour, icon
|
fieldMap() enums of projectColours() / projectIcons() keys |
never markup |
business_case |
fieldMap() text, max 50,000 (the Gates tab saves it through save.php). auditDisplay() keeps the first 120 characters in history; the History tab shows "changed the business case" without the text |
gates, AI facts |
tailoring |
fieldMap() type tailoring - JSON {tool: bool}, only known tools, NULL when empty |
projectEnabledTools() |
created_by_id |
createProject() (NULL for actor 0); projectCreateFromAction() may fill it for an analyst who submitted the form |
projectIsTeam(), projectCanDelete()
|
alert_health, alert_exceptions
|
only projectAlertsScan(), compare-and-set (WHERE alert_health <=> ? AND alert_exceptions <=> ?) |
NULL = never scanned (the first scan only records); '' = not live |
currency |
projectStampCurrency() in createProject() and before any budget write (only when empty); ProjectToolsService::setCurrency() relabels |
projectCurrencyOf(). Guard: projectBudgetReady()
|
estimated_cost (3.3.0)
|
fieldMap() type money (two-decimal string, so an unchanged value is not a change) |
projectProposalDetail(), AI facts |
estimated_benefit (3.3.0)
|
fieldMap() text, max 5,000 |
as above |
approval_status, approval_by_id, approval_datetime, approval_notes (3.3.0)
|
createProject() sets pending when projectProposalNeedsApproval(); ProjectsService::decideProposal() sets the rest, compare-and-set WHERE ... AND approval_status = 'pending'
|
NULL = needed no approval (every pre-3.3.0 project). Guard: projectIntakeReady(); the portfolio uses projectProposalApprovalColumn()
|
form_submission_id, proposed_by_name, proposed_by_email (3.3.0)
|
projectCreateFromAction() (one UPDATE after create) |
who asked, when it came from a form |
fieldMap(), for reference:
'name' => ['type' => 'string', 'max' => 200, 'required' => true],
'summary' => ['type' => 'text', 'max' => 20000],
'goal' => ['type' => 'string', 'max' => 500],
'methodology' => ['type' => 'enum', 'values' => array_keys(projectMethodologies())],
'status' => ['type' => 'enum', 'values' => projectStatuses()],
'health' => ['type' => 'enum', 'values' => projectHealthValues()],
'health_note' => ['type' => 'string', 'max' => 500],
'priority' => ['type' => 'enum', 'values' => projectPriorities()],
'owner_analyst_id' => ['type' => 'analyst'],
'start_date' => ['type' => 'date'],
'target_end_date' => ['type' => 'date'],
'actual_end_date' => ['type' => 'date'],
'colour' => ['type' => 'enum', 'values' => array_keys(projectColours())],
'icon' => ['type' => 'enum', 'values' => projectIcons()],
'business_case' => ['type' => 'text', 'max' => 50000],
'tailoring' => ['type' => 'tailoring'],
'estimated_cost' => ['type' => 'money'],
'estimated_benefit'=> ['type' => 'text', 'max' => 5000],
'visibility' => ['type' => 'enum', 'values' => ['everyone', 'members']],Two details that keep this safe before Verification: createProject() skips estimated_* without projectIntakeReady() and visibility without projectVisibilityReady(); updateProject() skips any fieldMap() field whose column the loaded row does not have (if (!array_key_exists($field, $cur)) continue;).
Not stored, on purpose: progress, automatic health, exceptions (all projectDecorate()), and the reference PRJ-0042 (projectCode()).
CREATE TABLE IF NOT EXISTS `project_stages` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`kind` VARCHAR(10) NOT NULL DEFAULT 'phase', -- phase | stage | sprint
`name` VARCHAR(150) NOT NULL,
`goal` VARCHAR(500) NULL,
`start_date` DATE NULL,
`end_date` DATE NULL,
`position` INT NOT NULL DEFAULT 0,
`status` VARCHAR(10) NOT NULL DEFAULT 'planned', -- planned | active | closed
-- The gate at the end of a stage (Staged projects): go | go_with_conditions | stop.
`gate_decision` VARCHAR(20) NULL,
`gate_notes` TEXT NULL,
`gate_decided_by` INT NULL,
`gate_decided_datetime` DATETIME NULL,
`gate_kind` VARCHAR(10) NOT NULL DEFAULT 'standard', -- 3.3.0: standard | golive (a go-live gate starts with its own checklist)
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_project_stages_project` (`project_id`, `position`),
CONSTRAINT `fk_project_stages_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;One table for phases, stages and sprints, because they are the same thing seen through different methods. That is what makes switching method safe: nothing has to be converted - applyMethodology() just rewrites kind on the open ones.
| Column | Notes |
|---|---|
kind |
Set from the preset's timebox on create (saveStage()); rewritten for non-closed rows on a method switch. Never sent by the client |
position |
COALESCE(MAX(position), 0) + 1 on create; reorderStages() rewrites it (no UI calls it yet). Order everywhere is position, id
|
status |
planned / active / closed; single_active presets allow one active (saveStage() refuses a second). Closing fires project.stage_closed
|
gate_decision, gate_notes, gate_decided_by, gate_decided_datetime
|
Written only by ProjectToolsService::decideGate(), straight to the row (not through saveStage()). gate_decided_by is an analyst id with no FK
|
gate_kind (3.3.0)
|
ProjectToolsService::setGateKind(); golive adds the starter checklist. Guard: projectGateKindReady() (in includes/projects/templates.php, used when projectTemplateCapture() reads a project's stages); createFromTemplate() also sets golive on a built stage |
Read by projectDetail() (with task_total / task_done subqueries per stage), projectExceptionColumns() (active_stage_end), projectListRows() (active_stage_name, stage_count), the calendar sync, the Timeline and the alert scan. Deleting a stage (deleteStage()) detaches its tasks, moves its milestones to the whole project, deletes its gate items, then the row - all by hand, in one transaction.
CREATE TABLE IF NOT EXISTS `project_audit` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`analyst_id` INT NULL,
`field_name` VARCHAR(100) NOT NULL,
`old_value` VARCHAR(1000) NULL,
`new_value` VARCHAR(1000) NULL,
`source` VARCHAR(20) NOT NULL DEFAULT 'app', -- app | api | demo
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `idx_project_audit_project` (`project_id`, `created_datetime`),
CONSTRAINT `fk_project_audit_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;The History tab. Written only by ProjectsService::audit() (which never throws and cuts values to 1,000 characters); analyst_id is NULL for actor 0; source is app or api. field_name is either a projects column (status, methodology, owner_analyst_id...) or an event key such as project_created, stage_added, stage_status, member_added, raid_closed, link_added, template_used, proposal_approved, dependency_added, milestone_removed. Values are stored as words, not ids (auditDisplay() turns an owner id into a name), so the history reads as English later; the page translates the field names (history.* in lang/en/projects.php) but not the stored values. projectDetail() reads the latest 50 with the analyst's name.
-- Projects (3.2.0). A project's work items ARE tasks - there is no second task
-- system - so a task simply names the project, and the stage or phase of it,
-- that it belongs to. Both NULL on every task that is not project work.
`project_id` INT NULL,
`project_stage_id` INT NULL,
`estimate_hours` DECIMAL(7,2) NULL, -- 3.3.0: how long the work is expected to take (capacity, estimate vs actual)
...
KEY `ix_tasks_project` (`project_id`),
KEY `ix_tasks_project_stage` (`project_stage_id`),
...
-- SET NULL, never CASCADE: deleting a project must not delete the work people
-- did. ProjectsService::deleteProject() detaches its tasks by hand as well.
CONSTRAINT `fk_tasks_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_tasks_project_stage` FOREIGN KEY (`project_stage_id`) REFERENCES `project_stages` (`id`) ON DELETE SET NULL,🔑 SET NULL, never CASCADE. Deleting a project must not delete the work people did. deleteProject() and deleteStage() also detach by hand.
| Column | Written by | Notes |
|---|---|---|
project_id, project_stage_id
|
ProjectsService::createTaskInProject() (an UPDATE after TasksService::saveTask()), assignTask(), deleteProject() / deleteStage() (to NULL) |
Only top-level tasks carry them; subtasks belong to their task. assignTask() refuses a subtask ("A subtask goes with its parent task - put the parent in the project.", 3.3.0 - the UI never offered it, but the API did not refuse). Progress counts parent_task_id IS NULL only |
estimate_hours (3.3.0)
|
Tasks column, written only through TasksService (parseEstimate(): empty = NULL, else > 0 and <= 9999, two decimals; on create it is a separate UPDATE so an unverified install still creates tasks). Reached from the task window, the REST API and the Plan's estimate box (tools.php task_estimate -> ProjectToolsService::setTaskEstimate() -> TasksService::saveTask()) |
Guard: projectEstimatesReady()
|
Guard for anything outside Projects that names these columns: projectsSchemaReady() - an information_schema check for tasks.project_id and the projects table, cached per request. api/tasks/list.php, api/tasks/get.php and includes/people.php call it and leave projects out when it is false; that is also why api/tasks/list.php reads the project names in a second query rather than a join - the main query never names the new column.
project_assets, project_changes, project_tickets, project_contracts, project_cmdb_objects, project_knowledge_articles (3.2.0) and project_problems (3.3.0) all have the same shape. One of them:
CREATE TABLE IF NOT EXISTS `project_assets` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`asset_id` INT NOT NULL,
`created_by_analyst_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
UNIQUE KEY `uq_pas_pair` (`project_id`, `asset_id`),
KEY `ix_pas_target` (`asset_id`),
CONSTRAINT `fk_pas_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pas_target` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pas_analyst` FOREIGN KEY (`created_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;| Table | Target column -> table | Unique key | FK prefix |
|---|---|---|---|
project_assets |
asset_id -> assets
|
uq_pas_pair |
fk_pas_* |
project_changes |
change_id -> changes
|
uq_pch_pair |
fk_pch_* |
project_tickets |
ticket_id -> tickets
|
uq_ptk_pair |
fk_ptk_* |
project_contracts |
contract_id -> contracts
|
uq_pco_pair |
fk_pco_* |
project_cmdb_objects |
cmdb_object_id -> cmdb_objects
|
uq_pcm_pair |
fk_pcm_* |
project_knowledge_articles |
article_id -> knowledge_articles
|
uq_pka_pair |
fk_pka_* |
project_problems (3.3.0)
|
problem_id -> problems
|
uq_ppr_pair |
fk_ppr_* |
One join table per kind - the Domains pattern. Every link is a real FK that cascades with either side, and permission checks stay per module. There is no polymorphic link table. Each has three FKs: _project and _target CASCADE, _analyst SET NULL; each has an index on the target column for the other side's lookup.
Written only by projectLinkAdd() / projectLinkRemove() in includes/projects/links.php (also linkQuietly() when a RAID lesson becomes an article or an issue becomes a ticket); projectLinksDeleteAll() loops over projectLinkKinds() for a deleted project. Read by projectLinks(), projectsLinkedTo(), projectUnapprovedChanges() (project_changes), projectTaskStats() (project_tickets, the 7-day ticket count), projectBudgetContracts() (project_contracts), the gate checklist (project_changes), and asset targets with scope = 'linked' (project_assets). Guard: projectLinksReady($conn, ?string $kind = null) - with a kind it checks that kind's table only (cached per kind), so an upgrade that adds a kind (problem) hides only that kind until Verification, not every link; with no kind it answers "is any kind usable" (the Connections tab's "run Verification" note). projectLinks(), projectLinkAdd(), projectLinkRemove(), projectLinkSearch(), projectsLinkedTo() and projectsPickableFor() all ask per kind (#2242):
function projectLinksReady(PDO $conn, ?string $kind = null): bool
{
static $ready = [];
$kinds = $kind !== null ? [$kind => projectLinkKinds()[$kind] ?? null] : projectLinkKinds();
foreach ($kinds as $name => $k) {
if ($k === null) return false;
if (!isset($ready[$name])) {
try { $conn->query("SELECT 1 FROM {$k['table']} LIMIT 0"); $ready[$name] = true; }
catch (Throwable $e) { $ready[$name] = false; }
}
if ($kind !== null) return $ready[$name];
}
// No kind named: is ANY kind usable (the Connections tab's "run Verification" note)?
return in_array(true, $ready, true);
}The rules are in part 8.
CREATE TABLE IF NOT EXISTS `project_roles` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`description` VARCHAR(255) NULL,
`display_order` INT NOT NULL DEFAULT 0,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_project_roles_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Install-wide, a list of words like task statuses - no project_id, no tenant_id. Seeded with nine roles (Executive, Senior User, Senior Supplier, Project Manager, Team Manager, Project Assurance, Project Support, Team member, Stakeholder) in database/freeitsm.sql and by Database Verification - only into an empty table, so an edited list is never put back. Written by api/projects/settings.php actions role_save / role_delete / role_reorder (Roles capability); read by lookups.php (roles, active only, by display_order, name), members() and the Settings Roles list (with an in_use count). The roles are described in FreeITSM's own words as PRINCE2-style.
CREATE TABLE IF NOT EXISTS `project_members` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`analyst_id` INT NULL,
`team_id` INT NULL,
`user_id` INT NULL,
`role_id` INT NULL,
`supplier_id` INT NULL, -- 3.3.0: a contractor on the team (Contracts -> Suppliers)
`contact_id` INT NULL, -- 3.3.0: a person at that supplier
`notes` VARCHAR(255) NULL,
`power` TINYINT NULL, -- 3.3.0 stakeholder map: 1-5, how much they can affect it
`interest` TINYINT NULL, -- 1-5, how much it affects them
`stance` VARCHAR(10) NULL, -- champion | supporter | neutral | sceptic | blocker
`keep_informed` VARCHAR(255) NULL, -- the communications plan: how, and how often
`position` INT NOT NULL DEFAULT 0,
`created_by_analyst_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_project_members_project` (`project_id`, `position`),
KEY `ix_pmem_analyst` (`analyst_id`),
CONSTRAINT `fk_pmem_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pmem_analyst` FOREIGN KEY (`analyst_id`) REFERENCES `analysts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pmem_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pmem_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pmem_role` FOREIGN KEY (`role_id`) REFERENCES `project_roles` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pmem_created_by` FOREIGN KEY (`created_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;-
Exactly one of
analyst_id/team_id/user_id. A person from People (user_id) lets a sponsor with no analyst account hold a role. The rule is inProjectToolsService::addMember()((($analyst > 0) + ($team > 0) + ($user > 0)) !== 1refuses), not a CHECK constraint.addMember()also refuses an inactive analyst, a person from another company, and a duplicate (conflict). -
(3.3.0) Or a contractor:
supplier_id, optionally withcontact_id(a person at that supplier), added throughaddContractorMember();members()reportskind = 'contractor'. Both FKs (fk_pmem_supplier,fk_pmem_contact) cascade. The same release addedtasks.assigned_supplier_id/assigned_contact_id(FKsSET NULL). See part 10. -
Role FK is
SET NULL- deleting a role never removes a person. - The member, team and person FKs cascade: an analyst, team or person deleted leaves the project.
-
power,interest,stance,keep_informed(3.3.0, the stakeholder map) - written throughupdateMember()(toolsmember_update), audited asstakeholder_saved;stanceis one ofProjectToolsService::STANCES; NULL power / interest = not placed yet. Guard:ProjectToolsService::stakeReady()(lookupsstake_ready);members()selectsNULL AS power...before Verification. - Read by
members()(with akindofanalyst/team/person, the name and email),projectIsTeam(),projectVisibleSql(),peopleProjects()(People pages: a person throughuser_id),ProjectReportsService::recipients()(teams expanded) and the nudges.
CREATE TABLE IF NOT EXISTS `project_items` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`parent_id` INT NULL,
`title` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
`acceptance_criteria` TEXT NULL,
`moscow` VARCHAR(10) NULL, -- must | should | could | wont
`stage_id` INT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'proposed', -- proposed | agreed | in_progress | accepted | dropped
`position` INT NOT NULL DEFAULT 0,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_project_items_project` (`project_id`, `position`),
CONSTRAINT `fk_pitem_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pitem_parent` FOREIGN KEY (`parent_id`) REFERENCES `project_items` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pitem_stage` FOREIGN KEY (`stage_id`) REFERENCES `project_stages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Deliverables and requirements in one table. MoSCoW sorts the rows; RACI says who does what. moscow NULL = not prioritised yet; values are ProjectToolsService::MOSCOW, statuses ITEM_STATUSES. parent_id is in the schema for a tree, but the UI does not use it yet; deleteItem() re-parents children to NULL by hand before deleting. stage_id must belong to the project (stageOf()). Written by saveItem(), deleteItem(), moveItem() (the board's drag: sets moscow and rewrites position for the column); read by items() (with the stage name). Must items that are not dropped are what a baseline counts as must_count (projectPlanSnapshot()).
CREATE TABLE IF NOT EXISTS `project_raci` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`item_id` INT NOT NULL,
`member_id` INT NOT NULL,
`letter` CHAR(1) NOT NULL, -- R | A | C | I
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_praci_cell` (`item_id`, `member_id`),
KEY `ix_praci_project` (`project_id`),
CONSTRAINT `fk_praci_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_praci_item` FOREIGN KEY (`item_id`) REFERENCES `project_items` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_praci_member` FOREIGN KEY (`member_id`) REFERENCES `project_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Unique (item_id, member_id) - one letter per cell. Cascades with the item and with the member, and removeMember() / deleteItem() delete their RACI rows by hand first as well. Written only by setRaci(), which demotes a previous A in the row to R rather than refusing a second A, and deletes the cell for an empty letter. Read by raci() as {item_id: {member_id: letter}} and by peopleProjects() (a person's duties). project_id is carried so the matrix is one indexed read.
CREATE TABLE IF NOT EXISTS `project_raid` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`type` VARCHAR(12) NOT NULL, -- risk | assumption | issue | dependency (3.3.0) | decision | lesson
`title` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
`probability` TINYINT NULL, -- 1-5, risks only
`impact` TINYINT NULL, -- 1-5, risks and issues
`response` VARCHAR(12) NULL, -- avoid | reduce | transfer | accept | share
`response_plan` TEXT NULL,
`owner_analyst_id` INT NULL,
`status` VARCHAR(10) NOT NULL DEFAULT 'open', -- open | closed
`due_date` DATE NULL,
`ticket_id` INT NULL, -- an issue that became, or came from, a ticket
`knowledge_article_id` INT NULL, -- a lesson turned into a Knowledge article
`escalated_datetime` DATETIME NULL, -- 3.3.0: set = escalated (open entries only)
`escalated_by_id` INT NULL,
`escalation_note` VARCHAR(500) NULL,
`decided_by` VARCHAR(150) NULL, -- 3.3.0, decisions: who made it (a name - often not an analyst)
`decided_date` DATE NULL,
`rationale` TEXT NULL, -- why it was decided
`raised_by_id` INT NULL,
`raised_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`closed_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_project_raid_project` (`project_id`, `type`, `status`),
CONSTRAINT `fk_praid_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_praid_owner` FOREIGN KEY (`owner_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_praid_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_praid_raised_by` FOREIGN KEY (`raised_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_praid_article` FOREIGN KEY (`knowledge_article_id`) REFERENCES `knowledge_articles` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_praid_escalated_by` FOREIGN KEY (`escalated_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;| Column | Notes |
|---|---|
type |
ProjectToolsService::RAID_TYPES - dependency arrived in 3.3.0 |
probability, impact, response
|
saveRaid() keeps probability and response for risks only, impact for risks and issues; other types store NULL whatever is sent. 1-5 each. The words are a setting (projectScaleLabels()), the row holds only the step |
| score |
Not stored. probability * impact is computed in SQL by raid() (CASE WHEN r.type = 'risk' ... END AS score) and by projectExceptionColumns() (MAX(r.probability * r.impact) over open risks). The heat map is drawn from the rows, never kept separately |
status, closed_datetime
|
A status change is audited raid_open / raid_closed and stamps or clears closed_datetime
|
due_date |
Open dependencies and decisions past it make raid_overdue in projectTaskStats(), which moves health (project_health_raid_late) |
ticket_id |
An issue turned into a ticket by issueToTicket() (or linked by hand); checked with projectLinkTargetOk(..., 'ticket', ...)
|
knowledge_article_id |
A lesson turned into a draft article by lessonToKnowledge(); FK SET NULL. raid() reads the article's title and published flag in a separate query, not a join, so the log still loads on an install that has not verified the column |
escalated_datetime, escalated_by_id, escalation_note (3.3.0)
|
escalateRaid() (open only, a note required); cleared by deescalateRaid() and by any save that closes the entry |
decided_by, decided_date, rationale (3.3.0)
|
The decision log, type = decision only (switching type away clears them). decided_by is a name, not an id, because the decider is often a sponsor with no analyst account. A decision closed with no date is stamped today; a future date is refused |
raised_by_id, raised_datetime
|
Who logged it |
Guards: projectsPhase2Ready() (the table exists - the portfolio's exception subquery names it), ProjectToolsService::raidLogReady() (the 3.3.0 escalation and decision columns). Read by raid(), projectExceptionColumns(), projectTaskStats(), portfolio_charts.php, the export, the AI facts and the assistants.
CREATE TABLE IF NOT EXISTS `project_raid_tasks` (
`id` INT NOT NULL AUTO_INCREMENT,
`raid_id` INT NOT NULL,
`task_id` INT NOT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_prt_pair` (`raid_id`, `task_id`),
KEY `ix_prt_task` (`task_id`),
CONSTRAINT `fk_prt_raid` FOREIGN KEY (`raid_id`) REFERENCES `project_raid` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_prt_task` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Follow-up actions on a RAID entry. A join, so tasks is untouched. addRaidAction() makes the task through createTaskInProject() (no stage) and links it; removeRaidAction() and deleteRaid() unlink and never delete the task. deleteProject() and the template cleanup() delete the joins by hand first. Guard: ProjectToolsService::raidActionsReady(). Read by raid() as actions.
CREATE TABLE IF NOT EXISTS `project_tolerances` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`stage_id` INT NULL,
`dimension` VARCHAR(12) NOT NULL, -- time | risk | cost (3.3.0)
`value` INT NOT NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ptol_dimension` (`project_id`, `stage_id`, `dimension`),
CONSTRAINT `fk_ptol_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ptol_stage` FOREIGN KEY (`stage_id`) REFERENCES `project_stages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;-
dimensionistime(days late, 0-365),risk(highest open risk score allowed, 1-25) or - since the budget (3.2.0) -cost(overspend allowed, 0-500%). -
Only project-level rows (
stage_idNULL) are used today. The column is there for stage tolerances later. ⚠️ The unique key does not stop duplicate project-level rows: MySQL treats NULLs as distinct in a unique index, so(42, NULL, 'time')twice is allowed.saveTolerances()therefore looks the row up first and UPDATEs or INSERTs; a blank value DELETEs.- Read by
tolerances()({time, risk, cost}) andprojectExceptionColumns()(tol_time,tol_risk,tol_costsubqueries). Guard:projectsPhase2Ready().
CREATE TABLE IF NOT EXISTS `project_milestones` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`stage_id` INT NULL, -- NULL = the whole project's
`name` VARCHAR(150) NOT NULL,
`due_date` DATE NOT NULL,
`done_date` DATE NULL, -- set = reached
`done_by_analyst_id` INT NULL,
`notes` VARCHAR(500) NULL,
`position` INT NOT NULL DEFAULT 0,
`created_by_analyst_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_pms_project` (`project_id`, `due_date`),
KEY `ix_pms_stage` (`stage_id`),
CONSTRAINT `fk_pms_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pms_stage` FOREIGN KEY (`stage_id`) REFERENCES `project_stages` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pms_done_by` FOREIGN KEY (`done_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pms_created_by` FOREIGN KEY (`created_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;A named date, not work. State is never stored - projectMilestoneState():
function projectMilestoneState(array $m, ?string $today = null): string
{
if (!empty($m['done_date'])) return 'done';
return $m['due_date'] < ($today ?? gmdate('Y-m-d')) ? 'missed' : 'due';
}Four FKs: project CASCADE, stage SET NULL (deleting a stage keeps its milestones as the whole project's - deleteStage() also does it by hand), the two analysts SET NULL. Written by saveMilestone() (keeps any field it is not given, so the Timeline can send only {id, due_date}; done_date going from empty to set fires project.milestone_reached) and deleteMilestone(). Read by projectMilestones(), projectMilestoneStats() (missed count and next one, for health and the card), the calendar sync, the alert scan, projectPlanSnapshot(), portfolio_charts.php. Guard: projectMilestonesReady().
CREATE TABLE IF NOT EXISTS `task_dependencies` (
`id` INT NOT NULL AUTO_INCREMENT,
`task_id` INT NOT NULL, -- the one that waits
`depends_on_id` INT NOT NULL, -- the one it waits for
`lag_days` INT NOT NULL DEFAULT 0,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
UNIQUE KEY `uq_tdep_pair` (`task_id`, `depends_on_id`),
KEY `ix_tdep_on` (`depends_on_id`),
CONSTRAINT `fk_tdep_task` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tdep_on` FOREIGN KEY (`depends_on_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Finish-to-start only: task_id cannot start until depends_on_id has finished, plus lag_days (-365 to 365). No project_id - a dependency belongs to two tasks. addTaskDependency() checks both tasks are in the project and that it would not make a loop (projectDependencyMakesCycle()), and upserts on the unique pair (ON DUPLICATE KEY UPDATE lag_days). projectDependencies() reads only rows whose both ends are in the project (a task moved out simply drops out of the picture), and deleting a project leaves the rows in place with the tasks. Waiting-on, clashes and the critical path are worked out by projectDependencyAnalysis() (part 4). Guard: projectDependenciesReady(). The DB Verify page files this table under Tasks (its prefix is task_).
CREATE TABLE IF NOT EXISTS `project_task_flow` (
`project_id` INT NOT NULL,
`day` DATE NOT NULL,
`status_id` INT NOT NULL,
`task_count` INT NOT NULL DEFAULT 0,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`project_id`, `day`, `status_id`),
CONSTRAINT `fk_ptf_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;The cumulative flow diagram's history: how many of a project's top-level tasks sat in each status on each day. No id column - the composite primary key is declared in $primaryKeys in api/system/db_verify.php ('project_task_flow' => ['project_id', 'day', 'status_id']), without which Verification would try PRIMARY KEY (id) and fail. status_id 0 means "no status" (COALESCE(status_id, 0)), and there is no FK to task_statuses. Written only by projectFlowSnapshot(), which deletes and rewrites today's rows (the last write of the day stands for the day), called from get.php and projectAlertsScan(). Read by projectFlow(). Nothing records a status change, so this is the only source; it starts on the day 3.3.0 is installed. Guard: projectFlowReady().
CREATE TABLE IF NOT EXISTS `project_asset_targets` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`name` VARCHAR(150) NOT NULL,
`scope` VARCHAR(10) NOT NULL DEFAULT 'filter', -- filter | linked
`scope_type_id` INT NULL, -- asset_types.id, filter only
`scope_field` VARCHAR(30) NULL, -- model | manufacturer | operating_system | hostname
`scope_value` VARCHAR(100) NULL, -- "contains" text for scope_field
`done_field` VARCHAR(30) NOT NULL, -- status | location | operating_system | feature_release | model | manufacturer | bitlocker_status | tpm_version
`done_op` VARCHAR(20) NOT NULL, -- is | is_not | contains | not_contains | at_least
`done_value` VARCHAR(100) NULL,
`target_date` DATE NULL, -- NULL = the project's target end date
`position` INT NOT NULL DEFAULT 0,
`created_by_analyst_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_patg_project` (`project_id`, `position`),
CONSTRAINT `fk_patg_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_patg_type` FOREIGN KEY (`scope_type_id`) REFERENCES `asset_types` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_patg_created_by` FOREIGN KEY (`created_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- One point a day per target for the burn-up line, written when the project is opened.
CREATE TABLE IF NOT EXISTS `project_asset_target_snapshots` (
`id` INT NOT NULL AUTO_INCREMENT,
`target_id` INT NOT NULL,
`snap_date` DATE NOT NULL,
`done` INT NOT NULL DEFAULT 0,
`total` INT NOT NULL DEFAULT 0,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_patgs_day` (`target_id`, `snap_date`),
CONSTRAINT `fk_patgs_target` FOREIGN KEY (`target_id`) REFERENCES `project_asset_targets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Live progress measured from Assets ("312 of 480 laptops replaced"). The target row is a rule: column names and operators are whitelisted and never interpolated from the row - projectTargetSql() runs projectTargetNormalise() again on every row it is given, takes column names from the two field maps and binds every value. Counts are worked out on read (projectTargetCount()); the snapshot table only keeps one point a day for the burn-up line. done_value for a list field (status, location) is an id on this install; a template stores the name instead (projectTargetValueNames()). Written by ProjectToolsService::saveTarget() / deleteTarget() (saving a changed rule deletes the target's snapshots) and, for snapshots, by projectTargetsDetail() with INSERT ... ON DUPLICATE KEY UPDATE on uq_patgs_day (only from get.php; the last 120 days are returned). position exists but targets cannot be re-ordered yet. Guard: projectTargetsReady() probes both tables.
CREATE TABLE IF NOT EXISTS `project_budget_lines` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`title` VARCHAR(200) NOT NULL,
`category` VARCHAR(20) NOT NULL DEFAULT 'other', -- hardware | software | services | labour | travel | other
`planned_amount` DECIMAL(18,2) NULL,
`actual_amount` DECIMAL(18,2) NULL,
`contract_id` INT NULL,
`cost_centre_id` INT NULL,
`notes` VARCHAR(500) NULL,
`planned_date` DATE NULL, -- 3.3.0: when the money is expected to go out
`spent_date` DATE NULL, -- 3.3.0: when it went out
`forecast_amount` DECIMAL(18,2) NULL, -- 3.3.0: expected final cost; NULL = the larger of planned and actual
`position` INT NOT NULL DEFAULT 0,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `ix_pbl_project` (`project_id`),
CONSTRAINT `fk_pbl_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pbl_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pbl_cost_centre` FOREIGN KEY (`cost_centre_id`) REFERENCES `cost_centres` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Planned and actual per line, in the project's currency (projects.currency). A line can name a contract (its value is the actual unless one is typed, and only when the currencies match - otherwise the line is currency_mismatch and adds nothing) and a cost centre of the project's company. Labour is not a line: it is worked out from the time logged on the project's tasks - except that a labour line with no typed actual is the plan for labour (projectLineCoversLabour()). category is one of PROJECT_BUDGET_CATEGORIES.
Written by ProjectToolsService::saveBudgetLine() (a whole-line replace; the contract must be linked on Connections and the actor must have Contracts; the cost centre must be active and in the project's company, or already on the line) and deleteBudgetLine(). The three 3.3.0 columns are written by the private budgetLineWhen() on its own, so a line still saves before Verification adds them; a spent date in the future is refused. Read by projectBudgetTotals(), projectBudgetDetail(), projectBudgetTimeline(), projectPlanSnapshot() (baselines). Guard: projectBudgetReady() (both money tables and projects.currency).
fk_pbl_project, fk_pbl_contract and fk_pbl_cost_centre are in $projectFks as well (#2243), so an install that gained this table through Database Verification gets them on its next run. deleteProject() still deletes the lines by hand, for an install where the ALTER failed.
CREATE TABLE IF NOT EXISTS `project_labour_rates` (
`id` INT NOT NULL AUTO_INCREMENT,
`scope` VARCHAR(10) NOT NULL,
`ref_id` INT NULL,
`hourly_rate` DECIMAL(12,2) NOT NULL,
`effective_from` DATE NOT NULL,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `ix_plr_scope` (`scope`, `ref_id`, `effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Hourly rates, each from a date: time logged on a day is priced at the rate in force that day (projectRateOn() - the newest effective_from on or before the entry's date), so raising a rate never re-prices the past.
scope |
ref_id |
Currency | Written by |
|---|---|---|---|
default |
NULL | the install's (project_currency) |
api/projects/settings.php rate_save / rate_delete (Budget capability) |
analyst |
analyst id | the install's | the same |
project |
project id | the project's |
ProjectToolsService::addProjectRate() / deleteProjectRate() (labour mode rate only) |
Polymorphic ref_id, so no foreign keys at all; deleteProject() deletes the project-scope rows by hand (#2242). Which scopes count is project_labour_mode. Rates are cached per request in $GLOBALS['__prj_labour_rates']; call projectLabourRatesReset() after a write. The settings GET returns rates only to a Budget holder, because analyst rates are close to pay.
CREATE TABLE IF NOT EXISTS `project_baselines` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`number` INT NOT NULL, -- 1, 2, 3 within the project
`label` VARCHAR(150) NULL,
`reason` VARCHAR(12) NOT NULL DEFAULT 'manual', -- manual | start | stage | change
`stage_id` INT NULL, -- the stage whose start took it
`change_request_id` INT NULL, -- the change whose approval took it
`start_date` DATE NULL,
`target_end_date` DATE NULL,
`budget_planned` DECIMAL(14,2) NULL,
`currency` CHAR(3) NULL,
`task_count` INT NOT NULL DEFAULT 0,
`estimate_hours` DECIMAL(9,2) NULL,
`must_count` INT NOT NULL DEFAULT 0,
`snapshot` MEDIUMTEXT NULL, -- JSON: stages, milestones, must, lines
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `idx_pbase_project` (`project_id`, `number`),
KEY `ix_pbase_stage` (`stage_id`),
KEY `ix_pbase_change` (`change_request_id`),
CONSTRAINT `fk_pbase_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pbase_stage` FOREIGN KEY (`stage_id`) REFERENCES `project_stages` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pbase_created_by` FOREIGN KEY (`created_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;The plan as it stood when it was agreed. Never updated - a new one is taken instead (number = MAX(number) + 1 in the project). Written only by projectTakeBaseline() (includes/projects/control.php), from ProjectToolsService::takeBaseline() (reason manual), projectBaselineAuto() (start / stage) and decideChangeRequest() (change). The headline figures are columns; the detail is JSON built by projectPlanSnapshot(), the same function that builds "the plan now" for every comparison:
{
"stages": [{"id": 12, "name": "Design", "start_date": "2026-11-02", "end_date": "2026-11-27"}],
"milestones": [{"id": 4, "name": "Move day", "due_date": "2027-02-13"}],
"must": [{"id": 31, "title": "Every desk has a docking station"}],
"lines": [{"id": 8, "title": "Removals firm", "planned": 12500}]
}change_request_id has an index but no FK. Variance is worked out on read (projectBaselineVariance(), items matched by id). Guard: projectControlReady() (both governance tables). Details in part 6.
CREATE TABLE IF NOT EXISTS `project_change_requests` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`number` INT NOT NULL, -- CR-1, CR-2 within the project
`title` VARCHAR(200) NOT NULL,
`description` TEXT NULL,
`reason` TEXT NULL,
`impact_days` INT NULL, -- + later, - sooner
`impact_cost` DECIMAL(14,2) NULL, -- + more, - a saving
`impact_scope` VARCHAR(1000) NULL,
`status` VARCHAR(12) NOT NULL DEFAULT 'proposed', -- proposed | approved | rejected | withdrawn
`raised_by_id` INT NULL,
`raised_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`decided_by_id` INT NULL,
`decided_datetime` DATETIME NULL,
`decision_notes` TEXT NULL,
`applied` TEXT NULL,
`baseline_id` INT NULL,
`updated_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `idx_pcr_project` (`project_id`, `status`),
KEY `ix_pcr_baseline` (`baseline_id`),
CONSTRAINT `fk_pcr_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pcr_raised_by` FOREIGN KEY (`raised_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pcr_decided_by` FOREIGN KEY (`decided_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pcr_baseline` FOREIGN KEY (`baseline_id`) REFERENCES `project_baselines` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Approved, rejected or withdrawn - never deleted, it is the audit trail. impact_days and impact_cost are signed. applied records what approving did, as JSON: target_from, target_to, budget_line_id, budget_amount. baseline_id is the baseline the approval took. Written by saveChangeRequest(), decideChangeRequest() (compare-and-set WHERE status = 'proposed', so two approvers decide once) and withdrawChangeRequest(). Read by projectControlDetail(), projectChangeStats() (changes_pending), the nudges and the AI facts. Note that project_change_requests and project_baselines point at each other (baseline_id with an FK, change_request_id without).
CREATE TABLE IF NOT EXISTS `project_gate_items` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`stage_id` INT NOT NULL,
`kind` VARCHAR(10) NOT NULL DEFAULT 'check', -- check | document | signoff | change
`title` VARCHAR(200) NOT NULL,
`analyst_id` INT NULL, -- signoff: who signs
`change_id` INT NULL, -- change: which linked change
`document_id` INT NULL, -- document: the one that satisfies it
`done_by_id` INT NULL,
`done_datetime` DATETIME NULL,
`notes` VARCHAR(500) NULL,
`position` INT NOT NULL DEFAULT 0,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `idx_pgi_stage` (`stage_id`, `position`),
KEY `ix_pgi_project` (`project_id`),
KEY `ix_pgi_analyst` (`analyst_id`),
CONSTRAINT `fk_pgi_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pgi_stage` FOREIGN KEY (`stage_id`) REFERENCES `project_stages` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pgi_analyst` FOREIGN KEY (`analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pgi_done_by` FOREIGN KEY (`done_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;What must be true before a go. "Done" is partly worked out in projectGateItems():
$done = match ($kind) {
'document' => $docId !== null && isset($docs[$docId]),
'change' => $chgId !== null && !empty($changes[$chgId]['approved']),
default => $r['done_datetime'] !== null,
};So done_datetime / done_by_id mean something only for check and signoff; a document item is done while its document is still one of the project's (projectGateDocuments()), a change item while the linked change is approved (projectGateChanges()). change_id and document_id have no FKs - a change unlinked or a document removed simply makes the item open again. Both FKs to the project and the stage cascade, and deleteProject() / deleteStage() delete the rows by hand too. Written by saveGateItem(), deleteGateItem(), tickGateItem(), setGateKind() and createFromTemplate() (kind and title only). Guard: projectGateItemsReady().
CREATE TABLE IF NOT EXISTS `project_benefits` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`title` VARCHAR(200) NOT NULL,
`measure` VARCHAR(255) NULL, -- how it is measured
`unit` VARCHAR(30) NULL,
`direction` VARCHAR(4) NOT NULL DEFAULT 'up', -- up = higher is better, down = lower is better
`baseline_value` DECIMAL(18,2) NULL,
`target_value` DECIMAL(18,2) NULL,
`target_date` DATE NULL,
`owner_analyst_id` INT NULL,
`review_date` DATE NULL,
`review_months` INT NULL, -- NULL / 0 = no repeat
`status` VARCHAR(10) NOT NULL DEFAULT 'open',
`notes` TEXT NULL,
`position` INT NOT NULL DEFAULT 0,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_pben_project` (`project_id`, `position`),
KEY `ix_pben_review` (`status`, `review_date`),
KEY `ix_pben_owner` (`owner_analyst_id`),
CONSTRAINT `fk_pben_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pben_owner` FOREIGN KEY (`owner_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `project_benefit_measures` (
`id` INT NOT NULL AUTO_INCREMENT,
`benefit_id` INT NOT NULL,
`value` DECIMAL(18,2) NOT NULL,
`measured_date` DATE NOT NULL,
`note` VARCHAR(500) NULL,
`recorded_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0, -- 3.3.0: System -> Demo data
PRIMARY KEY (`id`),
KEY `idx_pbm_benefit` (`benefit_id`, `measured_date`),
CONSTRAINT `fk_pbm_benefit` FOREIGN KEY (`benefit_id`) REFERENCES `project_benefits` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pbm_recorded_by` FOREIGN KEY (`recorded_by_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;What a project is meant to improve, reviewed on a date - including after the project has closed, which is when most benefits arrive. status is only open (still reviewed) or closed (stop reviewing); achieved / missed / in progress / not measured are worked out by projectBenefitState() from the latest measurement by date. ix_pben_review serves the reminder scan (projectAlertsBenefits()), which looks at every project except cancelled ones. review_date moves on by review_months when a measurement is recorded near it (projectBenefitNextReview()). Written by saveBenefit() (a full replace), deleteBenefit(), addBenefitMeasure() (never in the future), deleteBenefitMeasure(), and createFromTemplate(). deleteProject() deletes measurements first, then benefits. Guard: projectBenefitsReady() (both tables).
CREATE TABLE IF NOT EXISTS `project_reports` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`kind` VARCHAR(20) NOT NULL,
`title` VARCHAR(200) NOT NULL,
`body` MEDIUMTEXT NULL,
`status` VARCHAR(10) NOT NULL DEFAULT 'draft',
`ai_drafted` TINYINT(1) NOT NULL DEFAULT 0,
`ai_edited` TINYINT(1) NOT NULL DEFAULT 0,
`ai_model` VARCHAR(120) NULL,
`created_by_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_by_id` INT NULL,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`approved_by_id` INT NULL,
`approved_datetime` DATETIME NULL,
`sent_datetime` DATETIME NULL, -- 3.3.0: last emailed
`sent_by_id` INT NULL,
`sent_to` TEXT NULL, -- every address it has gone to, comma-separated
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `ix_prep_project` (`project_id`, `kind`),
CONSTRAINT `fk_prep_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;The AI project manager's drafts and people's own reports. kind is briefing (the Overview's "Brief me" - one kept per project, a new one deletes the old) or one of ProjectReportsService::KINDS: highlight, exception, checkpoint, closure (3.3.0). status is draft / approved; approved is final. ai_drafted says the AI wrote it, ai_edited that a person has since changed its title or body (set by save()), ai_model which model. created_by_id is NULL for a scheduled draft. The "who" columns have no FKs. Written only by ProjectReportsService (briefing(), draftWithAi(), save(), approve(), delete(), send(), draftScheduled()). Guards: ProjectReportsService::ready() (the table) and scheduleReady() (the 3.3.0 sent_* columns and projects.report_schedule*).
Like the budget lines, fk_prep_project is in both freeitsm.sql and $projectFks (#2243); deleteProject() deletes reports by hand as well.
The Ask AI project assistant's conversations - part 7 has how they are used.
CREATE TABLE IF NOT EXISTS `project_ai_threads` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`analyst_id` INT NULL, -- NULL = the project's shared conversation
`summary` MEDIUMTEXT NULL, -- what earlier turns established
`summary_upto_id` INT NULL, -- the last message folded into it
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_pait_owner` (`project_id`, `analyst_id`),
KEY `ix_pait_analyst` (`analyst_id`),
CONSTRAINT `fk_pait_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pait_analyst` FOREIGN KEY (`analyst_id`) REFERENCES `analysts` (`id`) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS `project_ai_messages` (
`id` INT NOT NULL AUTO_INCREMENT,
`thread_id` INT NOT NULL,
`role` VARCHAR(10) NOT NULL, -- user | assistant
`kind` VARCHAR(10) NOT NULL DEFAULT 'chat', -- chat | open | resume
`content` MEDIUMTEXT NULL,
`proposals` MEDIUMTEXT NULL, -- JSON: proposed changes, each with its status
`looked_at` TEXT NULL, -- JSON: the read tools it used
`analyst_id` INT NULL,
`model` VARCHAR(120) NULL,
`tokens_in` INT NULL,
`tokens_out` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_paim_thread` (`thread_id`, `id`),
CONSTRAINT `fk_paim_thread` FOREIGN KEY (`thread_id`) REFERENCES `project_ai_threads` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_paim_analyst` FOREIGN KEY (`analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
);| Column | Notes |
|---|---|
project_ai_threads.analyst_id |
The person whose conversation it is, or NULL for the project's shared one (setting project_assistant_memory = project). MySQL lets several NULLs through a UNIQUE key, so projectChatThread() always looks the shared thread up before inserting one - never an upsert |
summary, summary_upto_id
|
The memory: projectChatSummarise() folds messages older than the latest 12 into summary once more than 24 are newer than summary_upto_id, then moves the pointer |
project_ai_messages.kind |
chat (a person wrote, the assistant answered), open (the greeting of an empty conversation), resume (the catch-up after 8 hours away); open and resume store only the assistant's message |
proposals |
`[{type, args, summary, status: pending |
looked_at |
The list_* tools it called that turn, for the "Checked: tasks, the RAID log" line |
analyst_id |
Who wrote a user turn, or who an assistant turn answered - named under messages in a shared conversation |
Deleting a project or an analyst takes their conversations with it (cascades); Restart deletes the thread. Guard: projectChatReady() (both tables).
CREATE TABLE IF NOT EXISTS `project_templates` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`description` VARCHAR(500) NULL,
`content` MEDIUMTEXT NOT NULL, -- see includes/projects/templates.php
`is_active` TINYINT(1) NOT NULL DEFAULT 1, -- 0 = not offered for new projects
`created_by_analyst_id` INT NULL,
`created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_datetime` DATETIME NULL,
`is_demo` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_project_templates_name` (`name`),
CONSTRAINT `fk_ptpl_created_by` FOREIGN KEY (`created_by_analyst_id`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Templates saved from a project, keyed saved:<id>. No project_id and no tenant_id: the plan is JSON with days counted from the start and no people, companies or links, so one template serves every company. The built-in templates live in code (projectBuiltinTemplates(), keyed builtin:<key>) and are never in this table; hiding one writes its key to the project_hidden_templates setting instead. content is validated by projectTemplateNormalise() on every read and write. Written by ProjectTemplatesService::saveFromProject() (insert, or replace a saved template's content keeping its id and is_active), update(), delete(). Guard: projectTemplatesReady(). The format is in part 8.
| Column | Owner | FK | Notes |
|---|---|---|---|
tasks.project_id, tasks.project_stage_id
|
Tasks |
fk_tasks_project, fk_tasks_project_stage SET NULL (in $projectFks) |
§3 |
status_planned.project_id |
Service Status (planned maintenance, includes/service_status_planned.php) |
fk_sp_project SET NULL in freeitsm.sql; not added by Verification |
The project that announced disruption. Not a project_* join table, because before its start there is no incident to link. deleteProject() nulls it by hand |
form_submissions <- projects.form_submission_id
|
Forms |
fk_projects_submission SET NULL |
the other direction: a project remembers the form that proposed it |
documents (parent type project) |
Documents | none (polymorphic parent) |
documentEntityRegistry(); orphans swept by documentsCollectOrphans()
|
calendar_events with source project_end / project_stage / project_milestone
|
Calendar | none - no reference column at all | Cleared and redrawn per source by projectSyncCalendarKind() (part 7) |
workflow_scheduled_emissions keys project_stage:*, project_milestone:*, project_benefit:*, project_overdue:*, project_stall:*, project_report_schedule:*
|
Workflow | none | The fire-once ledger for alerts and schedules |
system_settings keys project_*, projects_ai_*, project_alerts_last_run
|
System | - | §7 of part 1 |
There are two places a foreign key is declared:
-
database/freeitsm.sql- everyCONSTRAINTabove, created on a fresh install. -
$projectFksinapi/system/db_verify.php(3.3.0 addedfk_pait_project,fk_pait_analyst,fk_paim_thread,fk_paim_analystfor the Ask AI tables, and the four budget-line / report keys andfk_sp_projectthat were only infreeitsm.sql) - the list Database Verification adds to an existing install, each only if the table exists and the named constraint does not ($fkExists). Names and rules must matchfreeitsm.sql.$schema(fromincludes/db_verify_schema.php) carries columns and the primary key only, so a table Verification creates has no FK unless it is listed here.
// api/system/db_verify.php
// Projects foreign keys (3.2.0). Names + rules match freeitsm.sql. A task keeps
// its work when its project or stage goes (SET NULL, never CASCADE); a project's
// own stages and history go with it.
$projectFks = [
['projects', 'fk_projects_tenant', "ALTER TABLE projects ADD CONSTRAINT fk_projects_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE SET NULL"],
// ...
];
foreach ($projectFks as [$tbl, $name, $sql]) {
if (!$tableExists($tbl) || $fkExists($tbl, $name)) continue;
if ($tbl === 'tasks' && !$tableExists('projects')) continue;
try { $conn->exec($sql); } catch (Exception $e) {}
}A failed ALTER (orphan rows, for example) is swallowed - which is exactly why every delete in the services removes children by hand.
What $projectFks covers (at the time of writing):
| Table | FKs in $projectFks
|
|---|---|
projects |
fk_projects_tenant, fk_projects_owner, fk_projects_created_by, fk_projects_approval_by, fk_projects_submission
|
project_stages, project_audit
|
fk_project_stages_project, fk_project_audit_project
|
tasks |
fk_tasks_project, fk_tasks_project_stage (skipped while projects does not exist) |
project_members |
fk_pmem_project, _analyst, _team, _user, _role, _created_by
|
project_items |
fk_pitem_project, _parent, _stage
|
project_raci |
fk_praci_project, _item, _member
|
project_raid |
fk_praid_project, _owner, _ticket, _raised_by, _article, _escalated_by
|
project_raid_tasks |
fk_prt_raid, fk_prt_task
|
project_tolerances |
fk_ptol_project, fk_ptol_stage
|
project_asset_targets / _snapshots
|
fk_patg_project, _type, _created_by; fk_patgs_target
|
project_templates |
fk_ptpl_created_by |
project_milestones |
fk_pms_project, _stage, _done_by, _created_by
|
project_baselines |
fk_pbase_project, _stage, _created_by
|
project_change_requests |
fk_pcr_project, _raised_by, _decided_by, _baseline
|
project_task_flow |
fk_ptf_project |
task_dependencies |
fk_tdep_task, fk_tdep_on
|
project_gate_items |
fk_pgi_project, _stage, _analyst, _done_by
|
project_benefits / _measures
|
fk_pben_project, _owner; fk_pbm_benefit, _recorded_by
|
| the seven link tables |
fk_p??_project, _target, _analyst each |
project_budget_lines |
fk_pbl_project, fk_pbl_contract, fk_pbl_cost_centre (#2243) |
project_reports |
fk_prep_project (#2243) |
Not covered - present in freeitsm.sql only, so an upgraded install lacks it: fk_sp_project (status_planned.project_id, a Service Status table). The code does not depend on it: deleteProject() nulls the column by hand. (fk_pbl_* and fk_prep_project were in this position until #2243 added them.)
Composite primary keys. $primaryKeys in the same file maps a table whose key is not literally id; project_task_flow is the module's one entry. Without it the CREATE builder falls back to PRIMARY KEY (id) and the table fails to create.
Seed. The same file seeds project_roles with the nine roles only when the table is empty.
Each probes once per request (a static), almost always with SELECT <columns> FROM <table> LIMIT 0 inside a try, and returns false before Database Verification. The rule: a query in a shared read must not name a new table or column without one of these.
| Guard | File | Probes | Protects |
|---|---|---|---|
projectChatReady() (3.3.0)
|
includes/projects/assistant_chat.php |
project_ai_messages, project_ai_threads
|
the Ask AI panel and its endpoint - before Verification the panel says to run it |
projectsSchemaReady() |
includes/projects/methodologies.php |
information_schema: tasks.project_id and the projects table |
the Tasks board (api/tasks/list.php, api/tasks/get.php) and People (includes/people.php) |
projectsPhase2Ready() |
includes/projects/read.php |
project_tolerances, project_raid
|
projectExceptionColumns() - the portfolio and project queries; returns NULL AS max_risk, NULL AS tol_time, NULL AS tol_risk, NULL AS tol_cost, NULL AS active_stage_end instead |
projectPriorityColumn() |
includes/projects/read.php |
projects.priority |
returns 'medium' AS priority before |
projectEstimatesReady() |
includes/projects/read.php |
tasks.estimate_hours |
projectDetail(), capacity, effort |
projectVisibilityReady() / projectVisibilityColumn()
|
includes/projects/visibility.php |
projects.visibility |
every visibility check (everything visible before) |
projectIntakeReady() / projectProposalApprovalColumn()
|
includes/projects/intake.php |
projects.approval_status, estimated_cost, form_submission_id |
intake; createProject() skips estimated_*
|
projectLinksReady($conn, ?$kind) |
includes/projects/links.php |
one kind's link table (cached per kind); no kind = is any usable | Connections and the other side, per kind |
projectTargetsReady() |
includes/projects/targets.php |
both target tables | asset targets |
projectBudgetReady() |
includes/projects/budget.php |
project_budget_lines, project_labour_rates, projects.currency
|
the budget (get.php returns budget: null) |
projectMilestonesReady() |
includes/projects/milestones.php |
project_milestones |
milestones, health, calendar |
projectDependenciesReady() |
includes/projects/dependencies.php |
task_dependencies |
dependencies (writes refuse with not_ready) |
projectFlowReady() |
includes/projects/flow.php |
project_task_flow |
the flow snapshot and chart |
projectGateItemsReady() |
includes/projects/gatecheck.php |
project_gate_items |
gate checklists |
projectBenefitsReady() |
includes/projects/benefits.php |
both benefit tables | benefits |
projectControlReady() |
includes/projects/control.php |
project_baselines, project_change_requests
|
change control (get.php returns control: null) |
projectGateKindReady() |
includes/projects/templates.php |
project_stages.gate_kind |
capturing a project as a template (projectTemplateCapture()) |
projectTemplatesReady() |
includes/projects/templates.php |
project_templates |
saved templates |
ProjectToolsService::stakeReady() |
includes/services/project_tools.php |
project_members.power, interest, stance, keep_informed |
the stakeholder map |
ProjectToolsService::raidLogReady() |
includes/services/project_tools.php |
project_raid.escalated_datetime, decided_by |
escalation and the decision log |
ProjectToolsService::raidActionsReady() |
includes/services/project_tools.php |
project_raid_tasks |
RAID actions |
ProjectReportsService::ready() |
includes/services/project_reports.php |
project_reports (not cached) |
reports and briefings |
ProjectReportsService::scheduleReady() |
includes/services/project_reports.php |
projects.report_schedule, report_schedule_kind, project_reports.sent_datetime, sent_to
|
scheduling and sending |
projectAiReady() |
includes/projects/ai.php |
not a schema guard: is an AI key set (aiSettingsLoad($conn, 'projects_ai')) |
the AI project manager |
Elsewhere the code uses the cheaper pattern of its own try around one query - projectTaskStats() wraps the ticket count, the budget totals, RAID, milestones, benefits and change counts each in their own try, so a missing table can never take the task counts with it - and updateProject() skips a fieldMap() column the loaded row does not have.
FreeITSM has no migration files. The schema has two hand-maintained sources of truth that must agree, plus two generated or explicit lists:
| Step | File | What you do |
|---|---|---|
| 1 | database/freeitsm.sql |
The CREATE TABLE (or the new column inside the existing one), with its keys and CONSTRAINTs and an is_demo column. This is what a fresh install runs |
| 2 | includes/db_verify_schema.php |
The same columns as 'column' => 'TYPE [NOT] NULL [DEFAULT ...]' under the table. Database Verification creates a missing table from this and ALTERs in a missing column on an existing install. Columns and the primary key only |
| 3 | api/system/db_verify.php |
Every new FK in $projectFks (same name and rule as freeitsm.sql); a non-id primary key in $primaryKeys; a seed if the table needs one, only into an empty table |
| 4 | php scripts/gen_db_verify_indexes.php |
Regenerates includes/db_verify_indexes.php from every named KEY / UNIQUE KEY in freeitsm.sql. Review the diff and commit both files together. CLI only |
| 5 | php scripts/db_verify_cli.php --apply |
Applies it to your own database. Without --apply it only reports what would change; it runs as an existing administrator. It creates, alters and drops - take a backup first. The same thing as System -> Database Verification |
Then the code:
- add a cached
...Ready()guard (above) and use it in every shared read before the new table or column is named; - for a new child table, add it to the by-hand delete in
ProjectsService::deleteProject()(in its owntry) and todeleteStage()if it hangs off a stage; - add it to the demo data if the demo should show it (part 9).
The drift guard. dbVerifyColumnSelfCheck() (includes/db_verify_column_parse.php) compares freeitsm.sql with db_verify_schema.php on every Verification run and raises a red card on a difference: a column in only one of them means either a new install or an upgraded install silently lacks it. See Database Verification developer guide.
Replace 42 with a project id. Queries are taken from the code; where the code has a ?, the value is filled in.
A project's tasks by stage, with status (the Plan's own query, projectDetail(), trimmed):
SELECT s.position, COALESCE(s.name, '(not in a stage)') AS stage, s.kind, s.status AS stage_status,
t.id, t.title, ts.name AS status_name, COALESCE(ts.is_closed, 0) AS is_closed,
t.start_date, t.due_date, an.full_name AS assignee, t.estimate_hours
FROM tasks t
LEFT JOIN project_stages s ON s.id = t.project_stage_id
LEFT JOIN task_statuses ts ON ts.id = t.status_id
LEFT JOIN analysts an ON an.id = t.assigned_analyst_id
WHERE t.project_id = 42 AND t.parent_task_id IS NULL
ORDER BY s.position IS NULL, s.position, s.id, t.board_position, t.id;Progress, done and overdue per project (projectTaskStats() - top-level tasks only):
SELECT t.project_id,
COUNT(*) AS total,
SUM(CASE WHEN ts.is_closed = 1 THEN 1 ELSE 0 END) AS done,
SUM(CASE WHEN COALESCE(ts.is_closed, 0) = 0 AND t.due_date IS NOT NULL AND t.due_date < UTC_DATE() THEN 1 ELSE 0 END) AS overdue
FROM tasks t
LEFT JOIN task_statuses ts ON ts.id = t.status_id
WHERE t.project_id IN (42) AND t.parent_task_id IS NULL
GROUP BY t.project_id;The RAID log, open first, risks by score (ProjectToolsService::raid()):
SELECT r.id, r.type, r.title, r.status, r.probability, r.impact,
CASE WHEN r.type = 'risk' AND r.probability IS NOT NULL AND r.impact IS NOT NULL THEN r.probability * r.impact END AS score,
a.full_name AS owner_name, r.due_date, r.escalated_datetime
FROM project_raid r
LEFT JOIN analysts a ON a.id = r.owner_analyst_id
WHERE r.project_id = 42
ORDER BY r.status = 'closed', score IS NULL, score DESC, r.raised_datetime DESC;Open risks above a score across every project (the heat map's input, shaded < 8 low, 8-14 mid, >= 15 high):
SELECT p.id, p.name, r.title, r.probability, r.impact, r.probability * r.impact AS score
FROM project_raid r JOIN projects p ON p.id = r.project_id
WHERE r.type = 'risk' AND r.status = 'open' AND r.probability IS NOT NULL AND r.impact IS NOT NULL
AND r.probability * r.impact >= 15
ORDER BY score DESC;The exception columns (projectExceptionColumns(), what a tolerance breach is measured against):
SELECT p.id, p.name, p.target_end_date,
(SELECT MAX(r.probability * r.impact) FROM project_raid r WHERE r.project_id = p.id AND r.type = 'risk' AND r.status = 'open') AS max_risk,
(SELECT t.value FROM project_tolerances t WHERE t.project_id = p.id AND t.stage_id IS NULL AND t.dimension = 'time') AS tol_time,
(SELECT t.value FROM project_tolerances t WHERE t.project_id = p.id AND t.stage_id IS NULL AND t.dimension = 'risk') AS tol_risk,
(SELECT t.value FROM project_tolerances t WHERE t.project_id = p.id AND t.stage_id IS NULL AND t.dimension = 'cost') AS tol_cost,
(SELECT s.end_date FROM project_stages s WHERE s.project_id = p.id AND s.status = 'active' ORDER BY s.position, s.id LIMIT 1) AS active_stage_end
FROM projects p
WHERE p.status IN ('proposed', 'active');The burn-up's source. The burn-up is drawn in the browser (burnupPoints() in assets/js/projects-view.js) from the tasks get.php already returns: scope on a day = tasks (or estimate hours) created by that day, done = tasks completed by that day; daily up to 120 days, weekly beyond. The same series in SQL, by day:
SELECT d.day,
(SELECT COUNT(*) FROM tasks t WHERE t.project_id = 42 AND t.parent_task_id IS NULL AND DATE(t.created_datetime) <= d.day) AS scope,
(SELECT COUNT(*) FROM tasks t JOIN task_statuses ts ON ts.id = t.status_id
WHERE t.project_id = 42 AND t.parent_task_id IS NULL AND ts.is_closed = 1
AND t.completed_datetime IS NOT NULL AND DATE(t.completed_datetime) <= d.day) AS done
FROM (SELECT DISTINCT DATE(created_datetime) AS day FROM tasks WHERE project_id = 42
UNION SELECT DISTINCT DATE(completed_datetime) FROM tasks WHERE project_id = 42 AND completed_datetime IS NOT NULL) d
ORDER BY d.day;(Swap COUNT(*) for SUM(t.estimate_hours) for the hours measure. A task moved into the project later counts from its creation - the honest reading of what is stored.)
The flow history and today's snapshot (projectFlowSnapshot() / projectFlow()):
-- what projectFlowSnapshot() writes for today
SELECT project_id, COALESCE(status_id, 0) AS s, COUNT(*) AS n
FROM tasks WHERE project_id IN (42) AND parent_task_id IS NULL
GROUP BY project_id, COALESCE(status_id, 0);
-- what projectFlow() reads
SELECT day, status_id, task_count FROM project_task_flow WHERE project_id = 42 ORDER BY day;Linked tickets raised in the last seven days (the go-live jump, projectTaskStats()):
SELECT pt.project_id, COUNT(*) AS n
FROM project_tickets pt JOIN tickets t ON t.id = pt.ticket_id
WHERE pt.project_id IN (42) AND t.created_datetime >= UTC_TIMESTAMP() - INTERVAL 7 DAY
GROUP BY pt.project_id;Missed milestones and the next one (projectMilestoneStats()):
SELECT project_id, COUNT(*) AS missed FROM project_milestones
WHERE project_id IN (42) AND done_date IS NULL AND due_date < UTC_DATE() GROUP BY project_id;
SELECT m.project_id, m.id, m.name, m.due_date FROM project_milestones m
WHERE m.project_id IN (42) AND m.done_date IS NULL AND m.due_date >= UTC_DATE()
ORDER BY m.project_id, m.due_date, m.position, m.id;Which projects can analyst 7 see (the members-only clause from projectVisibleSql(), for an analyst without Manage Projects):
SELECT p.id, p.name, p.visibility FROM projects p
WHERE (p.visibility <> 'members' OR p.owner_analyst_id = 7 OR p.created_by_id = 7
OR EXISTS (SELECT 1 FROM project_members vm
LEFT JOIN analyst_teams vat ON vat.team_id = vm.team_id AND vat.analyst_id = 7
WHERE vm.project_id = p.id AND (vm.analyst_id = 7 OR vat.analyst_id IS NOT NULL)));A project's history (projectDetail()):
SELECT pa.field_name, pa.old_value, pa.new_value, pa.source, pa.created_datetime, an.full_name AS analyst_name
FROM project_audit pa LEFT JOIN analysts an ON an.id = pa.analyst_id
WHERE pa.project_id = 42 ORDER BY pa.created_datetime DESC, pa.id DESC LIMIT 50;Housekeeping checks - rows a missing FK could have left behind on an upgraded install:
-- tasks pointing at a project that no longer exists (should be none: SET NULL, and detached by hand)
SELECT t.id, t.project_id FROM tasks t LEFT JOIN projects p ON p.id = t.project_id
WHERE t.project_id IS NOT NULL AND p.id IS NULL;
-- project rates whose project has gone (project_labour_rates has no FK; deleteProject()
-- removes them since #2242, so only a project deleted before that can have left some)
SELECT r.* FROM project_labour_rates r LEFT JOIN projects p ON p.id = r.ref_id
WHERE r.scope = 'project' AND p.id IS NULL;
-- which FKs this install actually has on the project tables
SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE() AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND (TABLE_NAME LIKE 'project%' OR TABLE_NAME IN ('task_dependencies', 'tasks', 'status_planned'))
ORDER BY TABLE_NAME, CONSTRAINT_NAME;Never run test writes against a real install's projects; see part 9 for how the tests make and remove a temporary project.
FreeITSM — an open-source IT Service Management platform · github.com/edmozley/freeitsm · MIT licence
- Installation
- ⏰ Scheduled tasks (cron jobs)
- Architecture
- 🧪 Developer tests
- AI Providers
- Internationalisation (i18n)
- Timezones & Time Handling
- 📅 Date & Time Formats
- Theming & Dark Mode
- 🗂️ Recent — getting back to what you were doing
- ⌨️ Command palette (⌘K)
- 🔍 Searching inside tickets
- 📄 Attached documents
-
Mobile‑Friendly
- ↳ 🎫 Mobile: Tickets
- ↳ 💻 Mobile: Assets
- ↳ 📅 Mobile: Calendar
- ↳ 📚 Mobile: Knowledge
- ↳ 🚦 Mobile: Service Status
- ↳ 🗼 Mobile: Watchtower
- ↳ 🧩 Mobile: Problem Management
- ↳ 🔁 Mobile: Change Management
- ↳ 💿 Mobile: Software
- ↳ ✅ Mobile: Tasks
- ↳ 📝 Mobile: Forms
- ↳ 📄 Mobile: Contracts
- ↳ 📄 Mobile: Domains
- ↳ 📄 Mobile: People
- ↳ 🚀 Mobile: Projects
- ↳ 🎓 Mobile: LMS
- ↳ 🗺️ Mobile: CMDB
- ↳ 🗺️ Mobile: Network Mapper
- ↳ 🧭 Mobile: Process Mapper
- ↳ ⚙️ Mobile: Workflow
- ↳ 🖥️ Mobile: System
- ↳ 📊 Mobile: Reporting
- ↳ 📖 Mobile: System Wiki
- ↳ 🙋 Mobile: Self-Service Portal
- ↳ 🧰 Mobile: Techniques & Tricks
-
Security
- Layer 1 — which modules you can enter
- ↳ 🧩 Module Access Control
- ↳ 🛠️ Module Access — Developer Guide
- Layer 2 — what you can administer
- ↳ 🎭 Roles & Permissions
- ↳ 🛠️ Roles — Developer Guide
- ↳ 🔤 Why capabilities are constants
- Layer 3 — the System module
- ↳ 🔑 Admin Access Control
- Hardening
- ↳ 📄 Security review response 2026-08
- ↳ 🛡️ Security hardening 2026-08
- ↳ 🛠️ Security hardening 2026-08 — Developer Guide
- ↳ 🛡️ Round three — plain English
- ↳ 🛠️ Round three — Developer Guide
- ↳ 🛡️ CSRF protection (S4) — Developer Guide
- Single Sign-On (SSO)
- 🗂️ LDAP & Active Directory
- 📇 CardDAV contact sync
- Browser Extension
- API Reference
-
🔌 REST API — how it works
- ↳ 🎫 REST API: Tickets
- ↳ 💻 REST API: Assets
- ↳ 🔴 REST API: Problems
- ↳ 🟠 REST API: Changes
- ↳ 📚 REST API: Knowledge
- ↳ ✅ REST API: Tasks
- ↳ 🗄️ REST API: CMDB
- ↳ 📜 REST API: Contracts
- ↳ 🗓️ REST API: Calendar
- ↳ 💿 REST API: Software
- ↳ 🌐 REST API: Domains
- ↳ 🚦 REST API: Service Status
- ↳ ☀️ REST API: Morning Checks
- ↳ 📝 REST API: Forms
- ↳ ⚙️ REST API: Workflow
- ↳ 🏷️ REST API: Cost centres
- ↳ 🗺️ REST API: Network Mapper
- ↳ 🧭 Using the API docs page
- ↳ 📐 OpenAPI specification
- ↳ ✅ OpenAPI: kept correct
- ↳ 🛠️ Maintaining the catalogue
- Watchtower
-
Tickets
- ↳ 📋 Rota copy and paste — Developer Deep Dive
- ↳ ✅ Checklists & SOPs
- ↳ ☑️ Mandatory fields
- ↳ 🏷️ Ticket categories
- ↳ 👥 Assigning tickets to a team, and escalation
- ↳ 🏢 One board across every company
- ↳ Mailbox Authentication
- ↳ 📤 Email send log
- ↳ Basic IMAP mailboxes
- ↳ Email rendering & images
- ↳ SLA Management
- ↳ WhatsApp channel
-
↳
✈️ Telegram channel - ↳ ⭐ CSAT company scope and filters — Developer Guide
- ↳ 👥 Microsoft Teams channel
- ↳ 🗨️ Mattermost channel
- ↳ 💬 Web chat channel
- ↳ 🟣 Slack channel
- ↳ 🔗 Linking tickets
- ↳ ⓘ Record previews
- ↳ 📝 Ticket notes: internal or shared
- ↳ 🗒️ Canned responses
- ↳ ✉️ Limiting replies to particular senders
- ↳ 📨 Telling the analyst a ticket is theirs
- ↳ ✍️ Email signatures
- ↳ 🌐 The public web address
- ↳ 🔢 Ticket numbering
- ↳ 🙋 Raising a ticket for someone else
- ↳ 🔀 Merging tickets
- ↳ 🔒 Confidential tickets
- ↳ 👥 Portal managers
- ↳ 👁 Who has seen a ticket
- ↳ 📜 Reading long tickets
- ↳ ⑂ Splitting tickets
- ↳ ✅ Selecting several tickets
- ↳ 🗂️ The folder pane
- ↳ 🔽 Just my tickets, or no closed ones
- ↳ 🛠️ Snoozing tickets — Developer Guide
- ↳ 👥 Collision detection
- ↳ ⏱️ Time tracking
- ↳ 📅 Scheduled work in your own calendar
- Problem Management
- Tasks
- 🚀 Projects
-
Assets
- ↳ 🏢 Moving an asset between companies
- ↳ 📍 Shared asset locations
- ↳ 🧑💼 Assigning assets to analysts
- ↳ 📆 Warranty and lease alerts
- ↳ 🔭 Saved table views
- ↳ 🖨️ Recording anything, and importing it
- ↳ 🏷️ QR asset labels
- ↳ 📋 Who holds what, and handover documents
- ↳ 🖥️ The inventory agent (PowerShell)
- ↳ 🗄️ Proxmox VE servers
- ↳ ☁️ VMware Cloud Director servers
- ↳ 🔗 Linking equipment to tickets
- ↳ ☑️ Follow-up tasks on a ticket
- Knowledge
- Change Management
- Calendar
- Morning Checks
- Reporting
- Software
-
Forms
- ↳ 🎨 The form designer — Developer Guide
- ↳ 📐 Layout & the grid — Developer Guide
- ↳ 🗂️ Collections — grouping submissions
- ↳ 📄 Submissions as PDFs
- ↳ ⚡ What happens next — a form's own actions
- ↳ 🛠️ Sections & conditional logic — Developer Guide
- ↳ 🛠️ Lookup fields — Developer Guide
- ↳ 🛡️ Catalogue request approvals
- People
- Domains
- Contracts
- Service Status
- 🔔 Notifications
- 🚨 War Room
- Self-Service Portal
- LMS
- Process Mapper
- CMDB
- Network Mapper
- Workflows
- Issue trackers (Jira, Azure DevOps)
- System
-
Overview
- ↳ 📊 Progress tracker
- ↳ Concepts & vocabulary
- ↳ Email routing & mailboxes
- ↳ Settings: global vs per-company
- ↳ Users & self-service
- ↳ Staff cross-company access
- ↳ 🏢 One board across every company
- ↳ Worked examples
- ↳ Pitfalls & gotchas
- ↳ Scope: what it's for
- ↳ 🛠️ Developer Guide (make a module multi-company)
- ↳ 🗄️ Case study: CMDB (a linked graph)
- ↳ 🧪 Test harness (prove it's isolated)
-
🐞 Bugs resolved
- ↳ 🖼️ Logo and courses broke on Apache with PHP-FPM
- ↳ 🔢 Chat tickets ignored your ticket numbering
- ↳ 📅 Dates shown as a dash, or in server time
- ↳ 🔒 Assets → Users showed people from other companies
- ↳ 🔒 Restricted analysts could read other modules' data
- ↳ 🖼️ Replies with a picture in the thread failed to send
- ↳ 📎 Reply attachments never reached the customer
- ↳ 🛠️ Outbound email attachments — Developer Guide
- ↳ 🔑 A global SSO provider was missing from the portal
- ↳ 🔀 Behind a proxy, the SSO redirect said http
- ↳ ✏️ The portal tagline moved when you saved it
- ↳ 🎨 The portal settings screen forgot what you saved
- ↳ 🛡️ The approvals inbox said "Error" and nothing else
- ↳ 📄 A table's answers were missing from the PDF
- ↳ ◉ A single-select column let you tick every option
- ↳ 📐 The portal ignored a form's field widths
- ↳ 📋 The tasks board stopped taking clicks
- ↳ 🗂️ #121 The index list is out of date after upgrading
- ↳ 📅 #133 The calendar subscription was empty
- ↳ 📋 #131 Tasks always reopened on the board
- ↳ 💥 #129 Every page returned HTTP 500 after upgrading
- ↳ 🐳 #127 A PHP warning above the System page
- ↳ 🕐 #126 Notes stamped with the server's clock
- ↳ 🌍 Storing every date in UTC
- ↳ 🚪 The portal was down for everyone signed in
- ↳ ⚙️ #120 Workflow notes could never be written
- ↳ ⚙️ #123 Three errors when running Database Verification
- ↳ 📝 #122 The description box was a stub in the corner
- ↳ 💣 Demo data deleted real accounts
- ↳ 🔐 #117 Sign-in redirected to the wrong address
- ↳ 🎨 #108 The priority dot was invisible
- ↳ ⏱️ #116 Time logged from the right-click menu
- ↳ 🔑 #114 API keys refused by our own guard
- ↳ 🗂️ #110 Assigning a task told nobody
- ↳ 🚪 #107 Signed out while still working
- ↳ 📎 #103 "Share with Requester" reached nobody
- ↳ 🔍 #102 Search found nothing for hyphens
- ↳ 🪟 #101 Source code editor opened behind
- ↳ ☑️ #88 Subtasks could not be ticked off
- ↳ 💻 #84 Asset deep link selected nothing
- ↳ 🎫 #79 A new ticket arrived with no status
- ↳ 📧 #79 A ticket from email did not say so
- ↳ 🔔 #78 Bell opened to nothing
- ↳ 📬 #77 Mail only collected from Inbox
- ↳ 🔐 #74 The default password could not be changed
- ↳ 🚦 #70 Renaming an impact level
- ↳ 📤 #67 App-only mailboxes could not send
- ↳ 📭 #45 Verify only ever worked for Microsoft
- ↳ 📭 #45 IMAP reported as not authenticated
- ↳ ✉️ An email template stopped escaping itself
- ↳ 🕐 The portal dashboard showed the wrong time
- ↳ 🔢 The folder said 99 and the list showed 96