wehub-resource-sync
69 lines
3.6 KiB
SQL
69 lines
3.6 KiB
SQL
-- Reasonix registry: published skills/MCP servers, their versions, and the
|
|
-- activity/install event log. Apply:
|
|
-- wrangler d1 migrations apply reasonix-registry --local (or --remote)
|
|
|
|
-- One row per published capability. The registry stores only metadata and a
|
|
-- source pointer; it never fetches or executes the content — install happens
|
|
-- client-side through the install_source tool (SSRF-guarded, risk-rated).
|
|
CREATE TABLE IF NOT EXISTS packages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
kind TEXT NOT NULL, -- skill | mcp
|
|
scope_handle TEXT NOT NULL, -- publisher handle (namespace)
|
|
name TEXT NOT NULL, -- capability slug within the scope
|
|
slug TEXT NOT NULL UNIQUE, -- '<handle>/<name>' canonical id
|
|
summary TEXT NOT NULL DEFAULT '', -- one-liner (SKILL.md description)
|
|
description TEXT NOT NULL DEFAULT '', -- long markdown (README)
|
|
source TEXT NOT NULL DEFAULT '', -- what install_source pulls from
|
|
install_kind TEXT NOT NULL DEFAULT 'auto', -- auto | skill | mcp
|
|
homepage TEXT NOT NULL DEFAULT '',
|
|
repo_url TEXT NOT NULL DEFAULT '',
|
|
tags TEXT NOT NULL DEFAULT '', -- comma-separated
|
|
latest_version TEXT NOT NULL DEFAULT '',
|
|
install_count INTEGER NOT NULL DEFAULT 0,
|
|
star_count INTEGER NOT NULL DEFAULT 0,
|
|
verified INTEGER NOT NULL DEFAULT 0, -- owner/admin trust badge
|
|
status TEXT NOT NULL DEFAULT 'active', -- active | hidden | removed
|
|
publisher_id INTEGER NOT NULL, -- accounts user id (owner)
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS packages_kind ON packages (kind, status);
|
|
CREATE INDEX IF NOT EXISTS packages_installs ON packages (install_count DESC);
|
|
CREATE INDEX IF NOT EXISTS packages_created ON packages (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS packages_publisher ON packages (publisher_id);
|
|
|
|
-- Immutable version history: one row per published version, carrying the source
|
|
-- snapshot and content hash so a consumer can audit provenance before install.
|
|
CREATE TABLE IF NOT EXISTS package_versions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
package_id INTEGER NOT NULL,
|
|
version TEXT NOT NULL,
|
|
source TEXT NOT NULL DEFAULT '',
|
|
manifest TEXT NOT NULL DEFAULT '', -- JSON: frontmatter / mcp config snapshot
|
|
content_hash TEXT NOT NULL DEFAULT '', -- sha256 of the source
|
|
risk_level TEXT NOT NULL DEFAULT '', -- echoed from install_source plan
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (package_id, version)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS versions_package ON package_versions (package_id, created_at DESC);
|
|
|
|
-- Per-user stars, so star_count is a real de-duplicated tally.
|
|
CREATE TABLE IF NOT EXISTS stars (
|
|
package_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (package_id, user_id)
|
|
);
|
|
|
|
-- Activity log: powers the homepage live feed and the 7-day "trending" ranking.
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
type TEXT NOT NULL, -- publish | update | install | star
|
|
package_id INTEGER,
|
|
actor_handle TEXT NOT NULL DEFAULT '',
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS events_created ON events (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS events_trending ON events (type, created_at);
|