forked from Blendman974/faceclaw
58 lines
2.1 KiB
SQL
58 lines
2.1 KiB
SQL
-- Schema for the analytics reports (PROTOCOL.md). Idempotent: deploy.sh
|
|
-- applies it on every deploy, so changes must be written as additive
|
|
-- `if not exists` / `create or replace` statements.
|
|
|
|
create table if not exists reports (
|
|
report_id uuid primary key,
|
|
install_id uuid not null,
|
|
received_at timestamptz not null default now(),
|
|
created_at timestamptz not null,
|
|
schema_version integer not null,
|
|
level text not null,
|
|
app_version text not null,
|
|
platform text not null,
|
|
build text not null,
|
|
-- The validated report as received, for anything the tables below don't cover.
|
|
payload jsonb not null
|
|
);
|
|
create index if not exists reports_received_at_idx on reports (received_at);
|
|
create index if not exists reports_install_idx on reports (install_id, received_at);
|
|
|
|
create table if not exists report_counters (
|
|
report_id uuid not null references reports (report_id) on delete cascade,
|
|
day date not null,
|
|
name text not null,
|
|
value bigint not null,
|
|
primary key (report_id, day, name)
|
|
);
|
|
create index if not exists report_counters_day_name_idx on report_counters (day, name);
|
|
|
|
create table if not exists report_snapshot (
|
|
report_id uuid not null references reports (report_id) on delete cascade,
|
|
key text not null,
|
|
value jsonb not null,
|
|
primary key (report_id, key)
|
|
);
|
|
|
|
-- Counter totals per day, split by version, platform and build, with how
|
|
-- many installs reported each.
|
|
create or replace view daily_counters as
|
|
select r.app_version, r.platform, r.build, c.day, c.name,
|
|
sum(c.value) as total, count(distinct r.install_id) as installs
|
|
from report_counters c
|
|
join reports r using (report_id)
|
|
group by r.app_version, r.platform, r.build, c.day, c.name;
|
|
|
|
-- Each install's most recent report.
|
|
create or replace view latest_reports as
|
|
select distinct on (install_id) *
|
|
from reports
|
|
order by install_id, received_at desc;
|
|
|
|
-- Snapshot values from each install's most recent report, counted.
|
|
create or replace view latest_snapshot_values as
|
|
select r.app_version, r.platform, s.key, s.value, count(*) as installs
|
|
from latest_reports r
|
|
join report_snapshot s using (report_id)
|
|
group by r.app_version, r.platform, s.key, s.value;
|