Mission 3: VIEW Cross-Table Report (Dieu 26 v3.5)

Date: 2026-03-28 | PR: #643 | Status: MERGED + DEPLOYED

Step 0: Checkpoint

• OR v5.7: S142. Mission 3 prompt DISPATCHED.
• Merge Rule: 4 required checks GREEN = MERGE NGAY.
• Verify: §0-AF evidence from production URL.
• Assembly Gate: Q0=YES (pivot LIVE), Q4=~400 SQL + 96 Nuxt, Q5=DOT-113/114 reuse.

Production Survey (Before)

• pivot_definitions: 21 rows, 20 active. All group_spec/metric_spec = empty defaults.
• pivot_count(): 20 rows with counts.

Changes

PG Functions

• pivot_count() fixed: FOR loop syntax for PG 16 (SELECT value FROM jsonb_array_elements)
• pivot_query() NEW: GROUP BY + metric support via group_spec/metric_spec JSON
  • Allowed funcs: count, sum, avg, min, max
  • Allowed ops: =, !=, >, <, >=, <=, in, not_in, is_null, is_not_null, like
  • Returns (pivot_code, pivot_name, source_object, group_values JSONB, metric_values JSONB)

Seed (6 cross-table pivot_definitions)

• PIV-101: meta_catalog by composition_level (6 groups)
• PIV-102: meta_catalog by identity_class
• PIV-103: entity_species by composition_level
• PIV-104: dot_tools by category (3 groups)
• PIV-105: collection_registry by classification
• PIV-106: meta_catalog by level x identity (2D)

Warm-tier VIEWs

• v_pivot_by_level: composition_level summary
• v_pivot_species_by_level: species by level
• v_pivot_dot_by_category: DOT tools by category

Nuxt Endpoint

• /api/registry/pivot-query.get.ts: reads pivot_definitions via Directus SDK

VERIFY NUXT

• Directus items/pivot_definitions: 27 rows. PG: 27. MATCH.
• Nuxt /api/registry/pivot-query?code=PIV-101: returns definition. PASS.
• PG pivot_query('PIV-101'): 6 level groups with correct counts. PASS.
• PG pivot_count() backward compat: 26 rows with count > 0. PASS.
• Nuxt registries page: HTTP 200. PASS.
• dot-pivot-health: 5/7 PASS, 2 expected warnings. PASS.

Checklist

• §0-AV bypass Directus? NO. Nuxt endpoint reads via Directus API.
• §0-AU hardcode? NO. pivot_query reads from pivot_definitions metadata.
• Local build: PASS.
• PRs: 1 (#643). Manual deploys: 0.

Mission 3 COMPLETE.