Persons Dashboard

A final table/view of wide, enriched person data ready for dashboards. Rebuild as necessary.

Caution

Every column below is an implicit contract with a tag_type produced in person_tags. If a column’s tag_type string doesn’t match what the tag query writes, the column silently fills with NULL (or 0 for flags) — no error.

Create persons_dashboard

This is a DuckDB Pivot.

CREATE OR REPLACE TABLE persons_dashboard AS
PIVOT (
  SELECT
    p.id AS person_id,
    p.status,
    p.created_at,
    p.updated_at,
    pt.tag_type,
    pt.tag_value
  FROM persons p
  LEFT JOIN person_tags pt
    ON p.id = pt.person_id
)
ON tag_type
USING max(tag_value)
GROUP BY
  person_id,
  status,
  created_at,
  updated_at;