A comprehensive analytics dashboard database schema designed for building data visualization platforms. Includes workspace-based multi-tenancy, customizable dashboards with drag-and-drop widgets, pluggable data source connections, event tracking with flexible JSONB payloads, and scheduled report generation. Suitable for business intelligence tools, product analytics platforms, or internal metrics dashboards.
events is a different kind of table from everything else here. The other five hold configuration — workspaces, dashboards, widgets, connections, schedules — written occasionally by people. events is raw telemetry written continuously by machines, and it will outgrow all of them combined within days of launch.
A widget stores its query_config rather than its results. The dashboard is a saved question, not a saved answer, which is what lets the same widget show today's numbers tomorrow without anything being recomputed and stored in between.
Layout is data too. widgets carries position_x, position_y, width and height, and dashboards keeps a layout column — so a user dragging a chart is an update, and the arrangement survives being opened on another machine.
Every product tracks different things, and a column per property would mean a migration for every new event anyone wants to send. A properties blob accepts all of them. The cost lands on reading: queries have to extract fields that may be absent, misspelled or of the wrong type, and a typo in an event name creates a second event nobody notices until a chart looks wrong.
Caching results in the database would make dashboards instant and immediately stale, and would need invalidation rules for data that changes continuously. Storing query_config keeps one definition that always reflects current data. Freshness is bought with query cost, which is why a production system puts a cache in front rather than in the table.
Putting credentials on each widget would duplicate secrets across every chart that uses the same database and make rotating them a sweep. One row per connection means a credential is changed once, and status with last_synced_at makes a broken connection visible before every widget on the dashboard fails at once.
People subscribe to a view, not to a single chart. Keying the schedule to the dashboard means recipients get the whole picture and the report definition does not have to be rebuilt when a widget is added. The limitation is that a single-chart alert cannot be expressed here — that is a different feature with a threshold rather than a cron.