| 1 | -- Reasonix registry: published skills/MCP servers, their versions, and the |
| 2 | -- activity/install event log. Apply: |
| 3 | -- wrangler d1 migrations apply reasonix-registry --local (or --remote) |
| 4 | |
| 5 | -- One row per published capability. The registry stores only metadata and a |
| 6 | -- source pointer; it never fetches or executes the content — install happens |
| 7 | -- client-side through the install_source tool (SSRF-guarded, risk-rated). |
| 8 | CREATE TABLE IF NOT EXISTS packages ( |
| 9 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 10 | kind TEXT NOT NULL, -- skill | plugin | mcp |
| 11 | scope_handle TEXT NOT NULL, -- publisher handle (namespace) |
| 12 | name TEXT NOT NULL, -- capability slug within the scope |
| 13 | slug TEXT NOT NULL UNIQUE, -- '<handle>/<name>' canonical id |
| 14 | summary TEXT NOT NULL DEFAULT '', -- one-liner (SKILL.md description) |
| 15 | description TEXT NOT NULL DEFAULT '', -- long markdown (README) |
| 16 | source TEXT NOT NULL DEFAULT '', -- what install_source pulls from |
| 17 | install_kind TEXT NOT NULL DEFAULT 'auto', -- auto | skill | plugin | mcp |
| 18 | homepage TEXT NOT NULL DEFAULT '', |
| 19 | repo_url TEXT NOT NULL DEFAULT '', |
| 20 | tags TEXT NOT NULL DEFAULT '', -- comma-separated |
| 21 | latest_version TEXT NOT NULL DEFAULT '', |
| 22 | install_count INTEGER NOT NULL DEFAULT 0, |
| 23 | star_count INTEGER NOT NULL DEFAULT 0, |
| 24 | verified INTEGER NOT NULL DEFAULT 0, -- owner/admin trust badge |
| 25 | status TEXT NOT NULL DEFAULT 'active', -- active | hidden | removed |
| 26 | publisher_id INTEGER NOT NULL, -- accounts user id (owner) |
| 27 | created_at TEXT NOT NULL, |
| 28 | updated_at TEXT NOT NULL |
| 29 | ); |
| 30 | CREATE INDEX IF NOT EXISTS packages_kind ON packages (kind, status); |
| 31 | CREATE INDEX IF NOT EXISTS packages_installs ON packages (install_count DESC); |
| 32 | CREATE INDEX IF NOT EXISTS packages_created ON packages (created_at DESC); |
| 33 | CREATE INDEX IF NOT EXISTS packages_publisher ON packages (publisher_id); |
| 34 | CREATE INDEX IF NOT EXISTS packages_active_created |
| 35 | ON packages (created_at DESC) |
| 36 | WHERE status = 'active'; |
| 37 | CREATE INDEX IF NOT EXISTS packages_active_kind_created |
| 38 | ON packages (kind, created_at DESC) |
| 39 | WHERE status = 'active'; |
| 40 | CREATE INDEX IF NOT EXISTS packages_active_installs |
| 41 | ON packages (install_count DESC, created_at DESC) |
| 42 | WHERE status = 'active'; |
| 43 | |
| 44 | -- Immutable version history: one row per published version, carrying the source |
| 45 | -- snapshot and content hash so a consumer can audit provenance before install. |
| 46 | CREATE TABLE IF NOT EXISTS package_versions ( |
| 47 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 48 | package_id INTEGER NOT NULL, |
| 49 | version TEXT NOT NULL, |
| 50 | source TEXT NOT NULL DEFAULT '', |
| 51 | manifest TEXT NOT NULL DEFAULT '', -- JSON: frontmatter / mcp config snapshot |
| 52 | content_hash TEXT NOT NULL DEFAULT '', -- sha256 of the source |
| 53 | risk_level TEXT NOT NULL DEFAULT '', -- echoed from install_source plan |
| 54 | created_at TEXT NOT NULL, |
| 55 | UNIQUE (package_id, version) |
| 56 | ); |
| 57 | CREATE INDEX IF NOT EXISTS versions_package ON package_versions (package_id, created_at DESC); |
| 58 | |
| 59 | -- Per-user stars, so star_count is a real de-duplicated tally. |
| 60 | CREATE TABLE IF NOT EXISTS stars ( |
| 61 | package_id INTEGER NOT NULL, |
| 62 | user_id INTEGER NOT NULL, |
| 63 | created_at TEXT NOT NULL, |
| 64 | PRIMARY KEY (package_id, user_id) |
| 65 | ); |
| 66 | |
| 67 | -- Activity log: powers the homepage live feed and the 7-day "trending" ranking. |
| 68 | CREATE TABLE IF NOT EXISTS events ( |
| 69 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 70 | type TEXT NOT NULL, -- publish | update | install | star |
| 71 | package_id INTEGER, |
| 72 | actor_handle TEXT NOT NULL DEFAULT '', |
| 73 | summary TEXT NOT NULL DEFAULT '', |
| 74 | created_at TEXT NOT NULL |
| 75 | ); |
| 76 | CREATE INDEX IF NOT EXISTS events_created ON events (created_at DESC); |
| 77 | CREATE INDEX IF NOT EXISTS events_trending ON events (type, created_at); |
| 78 | |
| 79 | -- Daily install counts replace one-row-per-install events for trending. Legacy |
| 80 | -- install events remain readable for audit/backfill compatibility; new writes |
| 81 | -- go to this bounded aggregate instead. |
| 82 | CREATE TABLE IF NOT EXISTS package_install_daily ( |
| 83 | date TEXT NOT NULL, |
| 84 | package_id INTEGER NOT NULL, |
| 85 | count INTEGER NOT NULL DEFAULT 0, |
| 86 | PRIMARY KEY (date, package_id) |
| 87 | ); |
| 88 | |
| 89 | CREATE TABLE IF NOT EXISTS registry_migration_meta ( |
| 90 | key TEXT PRIMARY KEY, |
| 91 | value TEXT NOT NULL |
| 92 | ); |
| 93 |