| 1 | -- Additive Registry indexes and the install rollup used by trending. |
| 2 | -- Apply before deploying the matching folded crash/registry Worker: |
| 3 | -- wrangler d1 execute reasonix-registry --remote --file=registry-migrate-performance-indexes.sql |
| 4 | |
| 5 | CREATE INDEX IF NOT EXISTS packages_active_created |
| 6 | ON packages (created_at DESC) |
| 7 | WHERE status = 'active'; |
| 8 | |
| 9 | CREATE INDEX IF NOT EXISTS packages_active_kind_created |
| 10 | ON packages (kind, created_at DESC) |
| 11 | WHERE status = 'active'; |
| 12 | |
| 13 | CREATE INDEX IF NOT EXISTS packages_active_installs |
| 14 | ON packages (install_count DESC, created_at DESC) |
| 15 | WHERE status = 'active'; |
| 16 | |
| 17 | CREATE TABLE IF NOT EXISTS package_install_daily ( |
| 18 | date TEXT NOT NULL, |
| 19 | package_id INTEGER NOT NULL, |
| 20 | count INTEGER NOT NULL DEFAULT 0, |
| 21 | PRIMARY KEY (date, package_id) |
| 22 | ); |
| 23 | |
| 24 | CREATE TABLE IF NOT EXISTS registry_migration_meta ( |
| 25 | key TEXT PRIMARY KEY, |
| 26 | value TEXT NOT NULL |
| 27 | ); |
| 28 | |
| 29 | -- Backfill deterministically. Re-running the migration replaces each day's |
| 30 | -- aggregate with the exact count from the legacy event log rather than adding |
| 31 | -- it a second time. |
| 32 | INSERT INTO package_install_daily (date, package_id, count) |
| 33 | SELECT substr(created_at, 1, 10), package_id, COUNT(*) |
| 34 | FROM events |
| 35 | WHERE type = 'install' AND package_id IS NOT NULL |
| 36 | AND NOT EXISTS (SELECT 1 FROM registry_migration_meta WHERE key = 'package_install_daily_v1') |
| 37 | GROUP BY substr(created_at, 1, 10), package_id |
| 38 | ON CONFLICT (date, package_id) DO UPDATE SET count = excluded.count; |
| 39 | |
| 40 | INSERT OR IGNORE INTO registry_migration_meta (key, value) |
| 41 | VALUES ('package_install_daily_v1', datetime('now')); |
| 42 |