Set Up @packages/format

Overview

Build the Format layer at @packages/format. Reads all 4 dbt mart tables from DuckDB, applies period filtering, assembles a typed Report JSON per entity + period, and writes it to output/monthly/. The Format layer is a pure data assembler — it applies no business logic beyond what is already in the marts, except for joining targets to revenue at query time.

Result: bun run format --entity IONS --period 2026-02 writes output/monthly/2026-02-report.json with all sections populated from real mart data.

Goals

• Define the full Report TypeScript type hierarchy (types.ts) — source of truth for the JSON schema consumed by the Present layer
• Scaffold @packages/format as a Bun workspace package
• Implement a DuckDB client helper for the format layer
• Implement all 4 section readers: revenue.ts, orders.ts, marketing.ts, schools.ts
• Implement the report assembler (report.ts) that joins all sections and writes report.json
• Wire the CLI with --entity, --period, --unit flags
• Verify: bun run format --entity IONS --period 2026-02 produces a valid, non-empty report.json

Non-Goals

• Rendering the report (Plan 006 — Present layer)
• Building @packages/db Drizzle schema (Plan 005)
• Dashboard integration (Plan 007)
• Weekly report format (monthly is the current scope)
• Multi-period comparison in the format output (Format reads one period; cross-period comparison is computed at query time inside each section reader)

Phases

• Phase 1: Package scaffold + types
• Phase 2: DuckDB client helper
• Phase 3: Section readers (revenue, orders, marketing, schools)
• Phase 4: Report assembler + CLI
• Phase 5: Verification

Success

• @packages/format scaffolded as Bun workspace package
• types.ts defines Report, UnitReport, RevenueSection, ProgramProgressSection, ChannelMarketingSection, SchoolProgressSection
• bun run test:type --filter @packages/format passes
• bun run format --entity IONS --period 2026-02 exits 0
• output/monthly/2026-02-report.json exists and contains all 4 sections with non-null data
• Revenue section includes revenue_actuals, revenue_target, gap_to_target, achievement_pct, last_period, last_year, best_year fields

Requirements

• Plan 003 completed: all 4 mart tables exist in atlas.db
• DuckDB node bindings available in workspace catalogs (@duckdb/node-api)
• @repo/typescript/lib tsconfig preset
• @repo/lint/base ESLint config
• output/monthly/ directory (gitignored)

Context

Why This Approach

• Format layer reads marts directly via DuckDB node client — no HTTP layer needed (local-first)
• Revenue section joins mart_revenue × stg_targets at query time (per analytics.md spec) — targets are not materialized into the mart itself
• Cross-period comparison (last_period, last_year, best_year) is computed by the revenue section reader via multiple mart queries, not stored in the mart
• schools.ts pivots mart_school_progress to a year × organization matrix in TypeScript (not SQL PIVOT) for flexibility
• Report is written to disk as JSON for auditability; also returned in-memory so the Present layer can call Format directly without disk I/O

Key Constraints

• --period accepts YYYY-MM format (e.g. 2026-02) and is parsed into year and month integers
• --unit is optional; if omitted, all units for the entity are included
• Revenue section must compute: last_period (previous calendar month), last_year (same month prior year), best_year (highest all-time value for same month across all years in mart)
• achievement_pct = revenue_actuals / revenue_target * 100; handle null target gracefully (NULL target → NULL achievement_pct)
• School progress matrix: rows are organizations, columns are years (sorted ascending); cell values are student counts (0 if no students that year)
• report.json output path: output/monthly/{period}-report.json (e.g. output/monthly/2026-02-report.json)

Edge Cases

• No target data for a unit+period: revenue_target = null, gap_to_target = null, achievement_pct = null
• No enrollment data for a unit+period: section returns empty arrays, not an error
• --unit flag with a unit code not in the entity: warn and skip (no hard error)
• output/monthly/ directory may not exist — create it if missing

Tradeoffs

• In-memory return + disk write: slight duplication but enables both direct Present invocation and disk-based audit trail
• TypeScript pivot for school matrix: more readable than SQL PIVOT; acceptable since it's a small dataset (hundreds of organizations × 4 years)

Skills

• plan — plan file format and conventions

Boundaries

• Always: Update Progress.md after each task completion
• Always: All field names in types.ts must match the glossary.md terminology
• Always: Handle null/missing mart data gracefully — never throw on missing data, return null values
• Ask first: Any new report section not in the spec
• Ask first: Changes to the Report type shape (downstream Present layer depends on it)
• Never: Apply business logic that belongs in dbt (e.g. computing customer type classification)
• Never: Write to LibSQL or DuckDB — format layer is read-only

Questions

• Output path format? → output/monthly/{period}-report.json
• What fields does the revenue section need? → actuals, target, gap, achievement_pct, last_period, last_year, best_year
• Should best_year be computed per unit, or globally across all units? → Per unit (same-month max for the current unit)
• Should the Report type be exported from @packages/format for use by @services/present and @services/dashboard? → Yes (export from @source/index.ts)