Skip to content

Build master_plans DuckDB SQL metrics pipeline for milestone dashboards

Problem

Milestone progress tracking in master_plans is manual and not queryable — status counts are maintained by hand in Markdown macros, and there is no way to run SQL against planning data or generate reproducible visualizations. This means milestone dashboards are brittle, go stale easily, and cannot be validated programmatically. The dashboard factory workstream itself lacks the kind of data-driven progress views it is supposed to enable for others.

Context

  • Planning artifacts are exported and materialized as canonical Parquet tables (milestones, workstreams, tasks, optional task_events/snapshots).
  • A DuckDB build step imports source planning docs and exposes stable SQL views for progress metrics.
  • Milestone pages can render a compact Dataface dashboard header (for example, completed x / y and percent complete) from SQL results.
  • Metrics and visuals update automatically on docs watch/build without manual refresh steps.

Possible Solutions

Plan

  • Confirm storage direction: Parquet outputs under generated paths, ignored by git.
  • Validate Dataface render path constraints for generated dashboards.
  • Define source-to-Parquet schema contract and export script under master_plans/tools/.
  • Add DuckDB build script (typed normalization views + Parquet materialization) and deterministic output path.
  • Create SQL views for milestone rollups (completed/total, % complete, open by priority, by workstream).
  • Add a small Dataface dashboard YAML template for milestone header visuals using SQL-derived outputs.
  • Wire generation into just plans watch/build flow and document local run/debug steps.
  • Add basic verification checks so missing/invalid status fields fail loudly.

Implementation Progress

  • Standard task status vocabulary is in place and consistently used.
  • M1 milestone/task frontmatter remains the source of truth for progress state.
  • Master plans docs build pipeline supports generated assets inclusion.

Progress so far

  • Task created and scoped for M1 handoff.
  • Decision captured to store analytics artifacts as Parquet and git-ignore generated outputs.
  • Verified dft render works for static HTML with CSV-backed queries.
  • Verified dft render currently fails for inline values query type in this flow (No adapter found for query type: values), so generated dashboard inputs should be emitted as files (CSV/derived extracts) even when source of truth is Parquet.

Cbox handoff brief

  1. Goal: add a build step that materializes milestone/workstream/task planning data to Parquet and renders static milestone header dashboard HTML for MkDocs embedding.
  2. Source of truth: Markdown frontmatter under master_plans/milestones/*.md, master_plans/workstreams/*/index.md, and master_plans/workstreams/*/tasks/*.md.
  3. Artifacts to produce:
  4. master_plans/.generated/parquet/milestones.parquet
  5. master_plans/.generated/parquet/workstreams.parquet
  6. master_plans/.generated/parquet/tasks.parquet
  7. master_plans/.generated/parquet/milestone_metrics.parquet
  8. master_plans/.generated/charts/milestones/<milestone-slug>.html
  9. Implementation sequence:
  10. Create master_plans/tools/build_plan_dataset.py to parse frontmatter and write Parquet.
  11. Create DuckDB SQL model step (in same script or master_plans/tools/build_plan_metrics.sql) for milestone metrics views.
  12. Export file-backed chart input CSV(s) per milestone from DuckDB query results (required for current dft render path).
  13. Add milestone chart template YAML under master_plans/dashboards/milestone_header.yml.
  14. Render one static HTML per milestone with dft render ... --format html --output ....
  15. Hook dataset+chart generation into just plans startup and MkDocs build flow.
  16. Acceptance checks:
  17. Running just plans regenerates Parquet + milestone HTML assets.
  18. Milestone page top section shows chart widget and matches completed x / y values from macros.
  19. A task status change in any workstream updates both macro text and chart after refresh.
  20. Generated artifacts are ignored by git.
  21. Out of scope for this task:
  22. Historical trend snapshots/backfill.
  23. Rich multi-chart milestone dashboards beyond compact header summary.

Review Feedback

  • Review cleared