AIDA Project Plan: data model for the Project Board

AIDA Labs · Library · Design · Draft v0.1 for review · 3 October 2026

This document maps out the relational database behind the AIDA Project Board, the editable, web-managed version of a project plan. The read-only Project Blueprint is generated from it. It uses the Sample Bank AML Intelligence Hub plan as the worked example and becomes the definition of the AIDA Project Plan product.

1. Decisions so far (Mike, 3 October 2026)

2. What PMI guidance this follows

The current PMBOK Guide is the Eighth Edition (November 2025). It's organised around principles, seven performance domains (governance, scope, schedule, finance, stakeholders, resources, risk) and non-prescriptive process guidance. The model's table groups follow those domains (section 4).

One gap in the Sample Bank plan as it stands. Its WBS is organised by phase and work area ("1. Initiation and governance setup", "2. Discovery and design"…), and most rows are activities. PMI recommends organising the WBS around deliverables. The model supports both: a wbs_element can be a deliverable, a work package, a task or a milestone. Whether to restructure the Sample Bank WBS around deliverables is a decision for you (see section 9).

3. How it fits together

 People edit here                Published, read-only
 ┌────────────────────┐ publish  ┌────────────────────┐ build  ┌──────────────────────┐
 │ Project Board      │ ───────► │ plan_snapshot      │ ─────► │ Project Blueprint    │
 │ (web app, editable)│ (button, │ (frozen JSON of    │        │ (static pages, never │
 │ PostgreSQL or      │  logged) │  the whole plan)   │        │  edited directly)    │
 │ PocketBase         │          └────────────────────┘        └──────────────────────┘
 └────────────────────┘                    │
        │ exports                          └──► portal Project Plan pop-up (same JSON)
        ▼
 CSV / MS Project XML / Jira CSV / Salesforce Data Loader CSV / Databricks (Delta)

• The board is the system of record. The Blueprint and the portal's Project Plan pop-up read only from a published snapshot. They can't change the board, and the board can't change them without a new publish.
• The snapshot has exactly the shape the Blueprint uses today (the WBS, MILESTONES, LANES, RES and release lists), so the current Blueprint and the portal pop-up work unchanged.

4. Tables

Naming is portable: lower-case snake_case, a id text primary key (UUID), created_at / updated_at / created_by / updated_by on every table. Weeks are kept alongside dates, because the Sample Bank plan is written in weeks 1–52.

4.1 Governance: projects, people, access

Security and visibility. A user sees a project only if they're a project_member. What they can view, edit or approve comes from their role's permission rows, plus any groups they belong to. Items assigned to a person, a role or a group (section 4.4) are editable by that assignee.

4.2 Scope and schedule

4.3 Baselines and change control

4.4 Assignments and resources

4.5 Progress and earned value

How earned value is calculated (in hours, until cost rates are agreed):

• PV (planned value): baseline hours scheduled to be done by the period end.
• EV (earned value): baseline hours × percent complete.
• AC (actual cost): actual hours from time_entry.
• SPI = EV ÷ PV and CPI = EV ÷ AC. 1.0 is on plan; below 1.0 needs attention.

4.6 Risks, stakeholders and the rest of the Blueprint

4.7 Publishing

The Blueprint build reads the latest snapshot. Nothing reads the live tables directly.

5. The two database versions

Both versions use the same table and column names, so the same exports and the same web front end work against either. The front end talks to an API layer, not to the database directly.

6. Exports and compatibility

7. The web front end (Project Board)

Each screen reuses the read-only views already built for the portal pop-up, made editable:

• Plan: a Gantt chart and table. Edit names, weeks, owners and dependencies, with drag-to-reschedule, and a check that flags open ends and summary-row logic.
• My work: tasks assigned to me, my roles or my groups. Update status, health, percent complete, actual dates and hours here.
• Milestones and gates: approve gates and record evidence.
• Baselines and change requests: compare the current plan with any baseline and approve changes.
• Risks, stakeholders, RACI: registers people can edit.
• Reports: SPI and CPI trends, variance against baseline, and the critical path.
• Publish: a button that creates a plan_snapshot and rebuilds the Blueprint and portal through the usual reviewed deploy script.
• Admin: members, roles, groups and permissions.

8. Loading the Sample Bank plan as the first project

Everything in the Sample Bank Blueprint maps directly:

Baseline v0.1 would be created from it as-is.

9. Open questions. Not guessed; need your answer

1. Project start date and calendar. The Sample Bank plan runs in weeks 1–52 with no calendar dates. What date is week 1, and which holidays should the calendar skip?
2. Restructure the WBS around deliverables? PMI recommends it. The Sample Bank plan is organised by phase and work area. Keep it as is for the first load, or restructure?
3. Who approves a baseline and change requests on Sample Bank: the Sponsor alone, or the Sponsor and the Project Lead?
4. Cost in earned value. Earned value is in hours for now. Should it also use cost rates per role, and in what currency?
5. Planned hours per task. The plan has hours per role per period (RES), not per task. For earned value, hours have to be spread onto tasks. Spread them by duration within each role, or enter them task by task?
6. Who gets accounts. Only AIDA Labs, or Sample Bank people too? This decides the first set of roles and permissions.
7. Which version first: PostgreSQL or PocketBase?

Sources

• [PMI: What is a Work Breakdown Structure?](https://www.pmi.org/blog/work-breakdown-structure)
• [PMI: Scheduling 101, the basics of best practices](https://www.pmi.org/learning/library/schedule-101-basic-best-practices-6701)
• [PMI: Monitoring performance against baseline](https://www.pmi.org/learning/library/monitoring-performance-against-baseline-10416)
• [PMI: PMBOK Guide](https://www.pmi.org/standards/pmbok)
• [PMI: Practice Standard for Scheduling](https://www.pmi.org/-/media/pmi/documents/public/pdf/certifications/practice-standard-scheduling.pdf)
• [PMI: Work Breakdown Structures practice standard](https://www.pmi.org/standards/work-breakdown-structures-third-edition)

‹ Back to the library