The check, Rules

Every program needs every gate in the catalog, in order. A missing or reversed gate breaks stage-gate reporting from the first load.

Findings, BRC-28

1

All programs

2

TypeScript

R1.run(ctx: CheckContext): Finding[]

-- R1 Gate sequence integrity (BLOCK)
-- Query 1: gates in the catalog with no milestone in the program.
SELECT 'program:' || p.code || ':' || g.code AS record_ref,
       p.code AS program_code, g.code AS missing_gate
FROM program p
CROSS JOIN gate_catalog g
LEFT JOIN milestone m ON m.program_code = p.code AND m.gate_code = g.code
WHERE m.id IS NULL;

-- Query 2: a gate dated on or after the next gate in the catalog.
SELECT 'milestone:' || m1.id AS record_ref,
       m1.program_code, m1.gate_code, m2.gate_code AS next_gate,
       m1.date, m2.date AS next_date
FROM milestone m1
JOIN gate_catalog g1 ON g1.code = m1.gate_code
JOIN gate_catalog g2 ON g2.sequence = g1.sequence + 1
JOIN milestone m2 ON m2.program_code = m1.program_code AND m2.gate_code = g2.code
WHERE julianday(m2.date) <= julianday(m1.date);

A successor which starts before its predecessor finishes makes the critical path meaningless. A loop stops any scheduler from computing dates.

Findings, BRC-28

2

All programs

6

TypeScript

R2.run(ctx: CheckContext): Finding[]

-- R2 Dependency logic (BLOCK)
-- Query 1: a finish-to-start successor starting before predecessor finish plus lag.
SELECT 'dependency:' || d.id AS record_ref,
       p.id AS predecessor_id, s.id AS successor_id,
       p.finish_date, s.start_date, d.lag_days
FROM dependency d
JOIN activity p ON p.id = d.predecessor_id
JOIN activity s ON s.id = d.successor_id
WHERE d.type = 'FS'
  AND julianday(s.start_date) < julianday(p.finish_date) + d.lag_days;

-- Query 2: activities inside a loop. UNION removes repeated pairs, so the walk
-- ends on its own once every reachable pair is listed.
WITH RECURSIVE walk(start_id, current_id) AS (
  SELECT predecessor_id, successor_id FROM dependency
  UNION
  SELECT w.start_id, d.successor_id
  FROM walk w
  JOIN dependency d ON d.predecessor_id = w.current_id
)
SELECT DISTINCT 'activity:' || start_id AS record_ref, start_id AS activity_in_loop
FROM walk
WHERE start_id = current_id;

A milestone on a weekend or holiday usually means the legacy tool stored calendar days. The row loads, but the date needs a decision.

Findings, BRC-28

1

All programs

4

TypeScript

R3.run(ctx: CheckContext): Finding[]

-- R3 Working calendar (REVIEW)
-- Milestones on a Saturday, a Sunday, or a sample holiday.
SELECT 'milestone:' || m.id AS record_ref,
       m.id, m.name, m.date,
       strftime('%w', m.date) AS weekday, h.name AS holiday
FROM milestone m
LEFT JOIN holiday h ON h.date = m.date
WHERE strftime('%w', m.date) IN ('0', '6') OR h.date IS NOT NULL;

Resource codes and cost centers must exist in the master lists. An unknown key either fails the load or lands hours on nobody.

Findings, BRC-28

1

All programs

3

TypeScript

R4.run(ctx: CheckContext): Finding[]

-- R4 Key reconciliation (BLOCK)
-- Query 1: assignments whose resource code is not in the resource master.
SELECT 'assignment:' || a.id AS record_ref,
       a.id AS assignment_id, a.resource_code
FROM assignment a
LEFT JOIN resource r ON r.code = a.resource_code
WHERE r.code IS NULL;

-- Query 2: WBS nodes whose cost center is not in the cost center master.
SELECT 'wbs:' || w.id AS record_ref,
       w.id AS wbs_id, w.cost_center
FROM wbs w
LEFT JOIN cost_center c ON c.code = w.cost_center
WHERE c.code IS NULL;

One milestone spelled four ways splits every report which groups by name. The alias table maps each spelling to one canonical name.

Findings, BRC-28

1

All programs

3

TypeScript

R5.run(ctx: CheckContext): Finding[]

-- R5 Naming variants (REVIEW)
-- Names which normalize to an alias key but differ from the canonical text.
SELECT 'milestone:' || m.id AS record_ref,
       m.id, m.name, al.canonical
FROM milestone m
JOIN milestone_alias al
  ON lower(replace(replace(replace(m.name, '.', ''), ' ', ''), '-', '')) = al.variant_key
WHERE m.name <> al.canonical;

Assigned hours above capacity in a month mean the plan relies on time nobody has. The plan loads, and resource planning decides what moves.

Findings, BRC-28

1

All programs

3

TypeScript

R6.run(ctx: CheckContext): Finding[]

-- R6 Over-allocation (REVIEW)
-- Assigned hours above capacity for a resource in a period.
SELECT 'resource:' || a.resource_code || '@' || a.period AS record_ref,
       a.resource_code, a.period,
       SUM(a.hours) AS assigned, c.capacity_hours
FROM assignment a
JOIN resource_capacity c ON c.resource_code = a.resource_code AND c.period = a.period
GROUP BY a.resource_code, a.period, c.capacity_hours
HAVING SUM(a.hours) > c.capacity_hours;

A current date far from the approved baseline with no change record is an unapproved slip. The PMO decides whether to re-baseline or log the change.

Findings, BRC-28

1

All programs

3

TypeScript

R7.run(ctx: CheckContext): Finding[]

-- R7 Baseline drift (REVIEW)
-- More than 10 days from baseline with no change record.
SELECT 'milestone:' || m.id AS record_ref,
       m.id, m.baseline_date, m.date,
       julianday(m.date) - julianday(m.baseline_date) AS drift_days
FROM milestone m
LEFT JOIN change_record c ON c.milestone_id = m.id
WHERE ABS(julianday(m.date) - julianday(m.baseline_date)) > 10
  AND c.id IS NULL;

A parent budget must equal the sum of its children, in the program currency. Otherwise portfolio totals disagree with program totals on day one.

Findings, BRC-28

1

All programs

3

TypeScript

R8.run(ctx: CheckContext): Finding[]

-- R8 Financial rollup (BLOCK)
-- Query 1: a parent budget more than 0.5 away from the sum of its children.
SELECT 'wbs:' || w.id AS record_ref,
       w.id AS wbs_id, w.budget, SUM(c.budget) AS children_total
FROM wbs w
JOIN wbs c ON c.parent_id = w.id
GROUP BY w.id, w.budget
HAVING ABS(w.budget - SUM(c.budget)) > 0.5;

-- Query 2: a WBS node in a currency other than the program currency.
SELECT 'wbs:' || w.id AS record_ref,
       w.id AS wbs_id, w.currency, p.currency AS program_currency
FROM wbs w
JOIN program p ON p.code = w.program_code
WHERE w.currency <> p.currency;

An activity without a parent WBS node has no cost owner and drops out of every rollup. The loader rejects the row or parks the activity outside the structure.

Findings, BRC-28

1

All programs

2

TypeScript

R9.run(ctx: CheckContext): Finding[]

-- R9 Orphans (BLOCK)
-- Activities without a parent WBS node.
SELECT 'activity:' || a.id AS record_ref,
       a.id, a.wbs_id
FROM activity a
LEFT JOIN wbs w ON w.id = a.wbs_id
WHERE w.id IS NULL;

A required field left empty either fails the row or, worse, loads with a default nobody chose. The mapping table marks which fields the loader needs.

Findings, BRC-28

1

All programs

2

TypeScript

R10.run(ctx: CheckContext): Finding[]

-- R10 Required fields for load (BLOCK)
-- Generated from the mapping table: one SELECT per required field.
SELECT 'wbs:' || id AS record_ref, 'program_code' AS field
FROM wbs WHERE program_code IS NULL OR CAST(program_code AS TEXT) = ''
UNION ALL
SELECT 'wbs:' || id AS record_ref, 'name' AS field
FROM wbs WHERE name IS NULL OR CAST(name AS TEXT) = ''
UNION ALL
SELECT 'wbs:' || id AS record_ref, 'cost_center' AS field
FROM wbs WHERE cost_center IS NULL OR CAST(cost_center AS TEXT) = ''
UNION ALL
SELECT 'wbs:' || id AS record_ref, 'budget' AS field
FROM wbs WHERE budget IS NULL OR CAST(budget AS TEXT) = ''
UNION ALL
SELECT 'wbs:' || id AS record_ref, 'currency' AS field
FROM wbs WHERE currency IS NULL OR CAST(currency AS TEXT) = ''
UNION ALL
SELECT 'activity:' || id AS record_ref, 'program_code' AS field
FROM activity WHERE program_code IS NULL OR CAST(program_code AS TEXT) = ''
UNION ALL
SELECT 'activity:' || id AS record_ref, 'name' AS field
FROM activity WHERE name IS NULL OR CAST(name AS TEXT) = ''
UNION ALL
SELECT 'activity:' || id AS record_ref, 'wbs_id' AS field
FROM activity WHERE wbs_id IS NULL OR CAST(wbs_id AS TEXT) = ''
UNION ALL
SELECT 'activity:' || id AS record_ref, 'start_date' AS field
FROM activity WHERE start_date IS NULL OR CAST(start_date AS TEXT) = ''
UNION ALL
SELECT 'activity:' || id AS record_ref, 'finish_date' AS field
FROM activity WHERE finish_date IS NULL OR CAST(finish_date AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'program_code' AS field
FROM milestone WHERE program_code IS NULL OR CAST(program_code AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'name' AS field
FROM milestone WHERE name IS NULL OR CAST(name AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'date' AS field
FROM milestone WHERE date IS NULL OR CAST(date AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'baseline_date' AS field
FROM milestone WHERE baseline_date IS NULL OR CAST(baseline_date AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'is_gate' AS field
FROM milestone WHERE is_gate IS NULL OR CAST(is_gate AS TEXT) = ''
UNION ALL
SELECT 'milestone:' || id AS record_ref, 'owner' AS field
FROM milestone WHERE owner IS NULL OR CAST(owner AS TEXT) = ''
UNION ALL
SELECT 'dependency:' || id AS record_ref, 'predecessor_id' AS field
FROM dependency WHERE predecessor_id IS NULL OR CAST(predecessor_id AS TEXT) = ''
UNION ALL
SELECT 'dependency:' || id AS record_ref, 'successor_id' AS field
FROM dependency WHERE successor_id IS NULL OR CAST(successor_id AS TEXT) = ''
UNION ALL
SELECT 'dependency:' || id AS record_ref, 'type' AS field
FROM dependency WHERE type IS NULL OR CAST(type AS TEXT) = ''
UNION ALL
SELECT 'dependency:' || id AS record_ref, 'lag_days' AS field
FROM dependency WHERE lag_days IS NULL OR CAST(lag_days AS TEXT) = ''
UNION ALL
SELECT 'assignment:' || id AS record_ref, 'activity_id' AS field
FROM assignment WHERE activity_id IS NULL OR CAST(activity_id AS TEXT) = ''
UNION ALL
SELECT 'assignment:' || id AS record_ref, 'resource_code' AS field
FROM assignment WHERE resource_code IS NULL OR CAST(resource_code AS TEXT) = ''
UNION ALL
SELECT 'assignment:' || id AS record_ref, 'period' AS field
FROM assignment WHERE period IS NULL OR CAST(period AS TEXT) = ''
UNION ALL
SELECT 'assignment:' || id AS record_ref, 'hours' AS field
FROM assignment WHERE hours IS NULL OR CAST(hours AS TEXT) = '';