Repository Public MIT
1-- Skills registry — initial schema.2-- Releases are immutable and audit history is append-only; both are enforced by triggers below,3-- so application bugs cannot silently rewrite history.45CREATE EXTENSION IF NOT EXISTS citext;6CREATE EXTENSION IF NOT EXISTS pg_trgm;78CREATE TABLE users (9 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),10 email citext NOT NULL UNIQUE,11 email_verified_at timestamptz,12 display_name text NOT NULL,13 password_hash text,14 platform_role text NOT NULL DEFAULT 'member' CHECK (platform_role IN ('owner','admin','reviewer','member')),15 status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','disabled')),16 must_change_password boolean NOT NULL DEFAULT false,17 bio text,18 website text,19 created_at timestamptz NOT NULL DEFAULT now(),20 updated_at timestamptz NOT NULL DEFAULT now()21);22-- At most one platform owner.23CREATE UNIQUE INDEX users_single_owner ON users ((true)) WHERE platform_role = 'owner';2425CREATE TABLE auth_identities (26 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),27 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,28 provider text NOT NULL,29 subject text NOT NULL,30 created_at timestamptz NOT NULL DEFAULT now(),31 UNIQUE (provider, subject)32);3334CREATE TABLE sessions (35 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),36 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,37 token_hash text NOT NULL UNIQUE,38 csrf_secret text NOT NULL,39 created_at timestamptz NOT NULL DEFAULT now(),40 last_seen_at timestamptz NOT NULL DEFAULT now(),41 reauth_at timestamptz NOT NULL DEFAULT now(),42 expires_at timestamptz NOT NULL,43 revoked_at timestamptz,44 ip text,45 user_agent text46);47CREATE INDEX sessions_user ON sessions (user_id);4849CREATE TABLE organizations (50 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),51 slug citext NOT NULL UNIQUE,52 display_name text NOT NULL,53 verification_state text NOT NULL DEFAULT 'unverified' CHECK (verification_state IN ('unverified','verified')),54 created_at timestamptz NOT NULL DEFAULT now()55);5657CREATE TABLE memberships (58 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,59 organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,60 role text NOT NULL CHECK (role IN ('org_admin','publisher','reviewer','member')),61 invited_by uuid REFERENCES users(id),62 created_at timestamptz NOT NULL DEFAULT now(),63 updated_at timestamptz NOT NULL DEFAULT now(),64 PRIMARY KEY (user_id, organization_id)65);6667CREATE TABLE namespaces (68 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),69 name citext NOT NULL UNIQUE CHECK (name::text ~ '^[a-z0-9]([a-z0-9]|-[a-z0-9]){0,38}$'),70 display_name text,71 owner_user_id uuid REFERENCES users(id),72 owner_org_id uuid REFERENCES organizations(id),73 identity_verified boolean NOT NULL DEFAULT false,74 created_at timestamptz NOT NULL DEFAULT now(),75 CHECK ((owner_user_id IS NULL) <> (owner_org_id IS NULL))76);7778CREATE TABLE artifacts (79 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),80 namespace_id uuid NOT NULL REFERENCES namespaces(id),81 namespace text NOT NULL,82 slug text NOT NULL CHECK (slug ~ '^[a-z0-9]([a-z0-9]|-[a-z0-9]){0,63}$'),83 type text NOT NULL CHECK (type IN ('skill','mcp-server','agent','prompt-pack','workflow','connector','hook','bundle','eval-kit','template')),84 summary text NOT NULL,85 description text,86 license text,87 repository_url text,88 homepage_url text,89 issues_url text,90 visibility text NOT NULL DEFAULT 'public' CHECK (visibility IN ('public','unlisted','private')),91 state text NOT NULL DEFAULT 'draft' CHECK (state IN ('draft','active','deprecated','archived','taken_down')),92 deprecation_reason text,93 capabilities text[] NOT NULL DEFAULT '{}',94 keywords text[] NOT NULL DEFAULT '{}',95 source_verified boolean NOT NULL DEFAULT false,96 latest_release_id uuid,97 created_by uuid REFERENCES users(id),98 created_at timestamptz NOT NULL DEFAULT now(),99 updated_at timestamptz NOT NULL DEFAULT now(),100 search tsvector,101 UNIQUE (namespace_id, slug),102 UNIQUE (namespace, slug)103);104CREATE INDEX artifacts_search ON artifacts USING gin (search);105CREATE INDEX artifacts_trgm ON artifacts USING gin ((namespace || '/' || slug || ' ' || summary) gin_trgm_ops);106CREATE INDEX artifacts_type ON artifacts (type, updated_at DESC);107CREATE INDEX artifacts_keywords ON artifacts USING gin (keywords);108109CREATE FUNCTION artifacts_search_refresh() RETURNS trigger LANGUAGE plpgsql AS $$110BEGIN111 NEW.search :=112 setweight(to_tsvector('simple', coalesce(NEW.slug, '') || ' ' || replace(coalesce(NEW.slug, ''), '-', ' ') || ' ' || coalesce(NEW.namespace, '')), 'A') ||113 setweight(to_tsvector('english', coalesce(NEW.summary, '')), 'A') ||114 setweight(to_tsvector('simple', array_to_string(NEW.keywords, ' ')), 'B') ||115 setweight(to_tsvector('english', left(coalesce(NEW.description, ''), 100000)), 'C');116 RETURN NEW;117END $$;118CREATE TRIGGER artifacts_search_trg BEFORE INSERT OR UPDATE OF slug, namespace, summary, keywords, description ON artifacts119 FOR EACH ROW EXECUTE FUNCTION artifacts_search_refresh();120121CREATE TABLE api_tokens (122 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),123 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,124 name text NOT NULL,125 prefix text NOT NULL UNIQUE,126 token_hash text NOT NULL,127 scopes text[] NOT NULL,128 namespaces text[] NOT NULL DEFAULT '{}',129 ip_allowlist text[] NOT NULL DEFAULT '{}',130 elevated boolean NOT NULL DEFAULT false,131 expires_at timestamptz NOT NULL,132 last_used_at timestamptz,133 last_used_ip text,134 revoked_at timestamptz,135 revoked_reason text,136 rotated_from uuid REFERENCES api_tokens(id),137 created_at timestamptz NOT NULL DEFAULT now()138);139CREATE INDEX api_tokens_user ON api_tokens (user_id);140141CREATE TABLE releases (142 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),143 artifact_id uuid NOT NULL REFERENCES artifacts(id),144 version text NOT NULL,145 v_major int NOT NULL,146 v_minor int NOT NULL,147 v_patch int NOT NULL,148 v_prerelease text,149 manifest_json jsonb NOT NULL,150 archive_object_key text NOT NULL,151 sha256 text NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),152 size_bytes bigint NOT NULL,153 unpacked_bytes bigint NOT NULL,154 file_count int NOT NULL,155 source_commit text,156 release_notes text,157 readme text,158 published_by uuid REFERENCES users(id),159 published_by_token uuid REFERENCES api_tokens(id),160 published_at timestamptz NOT NULL DEFAULT now(),161 immutable boolean NOT NULL DEFAULT true CHECK (immutable),162 state text NOT NULL DEFAULT 'published' CHECK (state IN ('published','quarantined','revoked')),163 security_status text NOT NULL CHECK (security_status IN ('passed','flagged','failed','not_scanned')),164 revoked_at timestamptz,165 revoked_by uuid REFERENCES users(id),166 revocation_reason text,167 UNIQUE (artifact_id, version)168);169CREATE INDEX releases_artifact ON releases (artifact_id, published_at DESC);170ALTER TABLE artifacts ADD CONSTRAINT artifacts_latest_fk FOREIGN KEY (latest_release_id) REFERENCES releases(id);171172-- Immutability: content columns can never change; rows can never be deleted.173-- Only the revocation/quarantine state and the release notes are mutable.174CREATE FUNCTION releases_immutable() RETURNS trigger LANGUAGE plpgsql AS $$175BEGIN176 IF TG_OP = 'DELETE' THEN177 RAISE EXCEPTION 'releases are immutable and cannot be deleted' USING ERRCODE = 'check_violation';178 END IF;179 IF NEW.artifact_id IS DISTINCT FROM OLD.artifact_id OR NEW.version IS DISTINCT FROM OLD.version180 OR NEW.manifest_json IS DISTINCT FROM OLD.manifest_json OR NEW.archive_object_key IS DISTINCT FROM OLD.archive_object_key181 OR NEW.sha256 IS DISTINCT FROM OLD.sha256 OR NEW.size_bytes IS DISTINCT FROM OLD.size_bytes182 OR NEW.unpacked_bytes IS DISTINCT FROM OLD.unpacked_bytes OR NEW.file_count IS DISTINCT FROM OLD.file_count183 OR NEW.source_commit IS DISTINCT FROM OLD.source_commit OR NEW.readme IS DISTINCT FROM OLD.readme184 OR NEW.published_by IS DISTINCT FROM OLD.published_by OR NEW.published_by_token IS DISTINCT FROM OLD.published_by_token185 OR NEW.published_at IS DISTINCT FROM OLD.published_at OR NEW.security_status IS DISTINCT FROM OLD.security_status186 OR NEW.v_major IS DISTINCT FROM OLD.v_major OR NEW.v_minor IS DISTINCT FROM OLD.v_minor187 OR NEW.v_patch IS DISTINCT FROM OLD.v_patch OR NEW.v_prerelease IS DISTINCT FROM OLD.v_prerelease THEN188 RAISE EXCEPTION 'published release content is immutable; publish a new version instead' USING ERRCODE = 'check_violation';189 END IF;190 RETURN NEW;191END $$;192CREATE TRIGGER releases_immutable_trg BEFORE UPDATE OR DELETE ON releases FOR EACH ROW EXECUTE FUNCTION releases_immutable();193194CREATE TABLE release_files (195 release_id uuid NOT NULL REFERENCES releases(id),196 path text NOT NULL,197 size_bytes bigint NOT NULL,198 sha256 text NOT NULL,199 mime text NOT NULL,200 mode int NOT NULL,201 is_text boolean NOT NULL,202 content_text text,203 PRIMARY KEY (release_id, path)204);205CREATE FUNCTION append_only() RETURNS trigger LANGUAGE plpgsql AS $$206BEGIN207 RAISE EXCEPTION '% is append-only', TG_TABLE_NAME USING ERRCODE = 'check_violation';208END $$;209CREATE TRIGGER release_files_append_only BEFORE UPDATE OR DELETE ON release_files FOR EACH ROW EXECUTE FUNCTION append_only();210211CREATE TABLE release_dependencies (212 release_id uuid NOT NULL REFERENCES releases(id),213 dep_namespace text NOT NULL,214 dep_slug text NOT NULL,215 version_range text NOT NULL,216 optional boolean NOT NULL DEFAULT false,217 PRIMARY KEY (release_id, dep_namespace, dep_slug)218);219CREATE TRIGGER release_dependencies_append_only BEFORE UPDATE OR DELETE ON release_dependencies FOR EACH ROW EXECUTE FUNCTION append_only();220221CREATE TABLE release_compatibility (222 release_id uuid NOT NULL REFERENCES releases(id),223 client text NOT NULL,224 platform text NOT NULL DEFAULT 'any',225 min_version text,226 install_strategy text NOT NULL,227 -- "verified" is set only by a reviewer after testing; publisher claims are unverified.228 verified boolean NOT NULL DEFAULT false,229 PRIMARY KEY (release_id, client, platform)230);231232CREATE TABLE uploads (233 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),234 artifact_id uuid NOT NULL REFERENCES artifacts(id),235 version text NOT NULL,236 declared_sha256 text NOT NULL CHECK (declared_sha256 ~ '^[0-9a-f]{64}$'),237 declared_size bigint NOT NULL,238 release_notes text,239 source_commit text,240 state text NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','uploaded','validating','security_review','published','rejected','quarantined','expired')),241 staging_key text,242 received_sha256 text,243 received_size bigint,244 release_id uuid REFERENCES releases(id),245 rejection jsonb,246 created_by uuid NOT NULL REFERENCES users(id),247 created_by_token uuid REFERENCES api_tokens(id),248 created_at timestamptz NOT NULL DEFAULT now(),249 updated_at timestamptz NOT NULL DEFAULT now(),250 expires_at timestamptz NOT NULL251);252CREATE INDEX uploads_artifact ON uploads (artifact_id, created_at DESC);253254CREATE TABLE scan_results (255 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),256 upload_id uuid REFERENCES uploads(id),257 release_id uuid REFERENCES releases(id),258 scanner_version text NOT NULL,259 findings jsonb NOT NULL,260 max_severity text,261 blocking_count int NOT NULL DEFAULT 0,262 completed_at timestamptz NOT NULL DEFAULT now()263);264CREATE INDEX scan_results_release ON scan_results (release_id);265266CREATE TABLE tags (267 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),268 name text NOT NULL UNIQUE269);270CREATE TABLE artifact_tags (271 artifact_id uuid NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,272 tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,273 PRIMARY KEY (artifact_id, tag_id)274);275276CREATE TABLE collections (277 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),278 slug text NOT NULL UNIQUE,279 title text NOT NULL,280 description text NOT NULL,281 category text NOT NULL DEFAULT 'research',282 position int NOT NULL DEFAULT 0,283 curated_by uuid REFERENCES users(id),284 created_at timestamptz NOT NULL DEFAULT now(),285 updated_at timestamptz NOT NULL DEFAULT now()286);287CREATE TABLE collection_items (288 collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE,289 artifact_id uuid NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,290 position int NOT NULL DEFAULT 0,291 note text,292 pinned_version text,293 added_at timestamptz NOT NULL DEFAULT now(),294 PRIMARY KEY (collection_id, artifact_id)295);296297CREATE TABLE audit_events (298 id bigserial PRIMARY KEY,299 at timestamptz NOT NULL DEFAULT now(),300 actor_user_id uuid,301 actor_token_id uuid,302 actor_label text NOT NULL,303 action text NOT NULL,304 target_type text,305 target_id text,306 target_label text,307 result text NOT NULL CHECK (result IN ('success','denied','error')),308 ip text,309 user_agent text,310 request_id text,311 detail jsonb NOT NULL DEFAULT '{}'312);313CREATE INDEX audit_actor ON audit_events (actor_user_id, at DESC);314CREATE INDEX audit_target ON audit_events (target_type, target_id, at DESC);315CREATE TRIGGER audit_events_append_only BEFORE UPDATE OR DELETE ON audit_events FOR EACH ROW EXECUTE FUNCTION append_only();316317-- Privacy-conscious download counting: daily aggregates only; the dedupe table holds salted318-- hashes that are purged after two days.319CREATE TABLE download_daily (320 release_id uuid NOT NULL REFERENCES releases(id),321 day date NOT NULL,322 count int NOT NULL DEFAULT 0,323 PRIMARY KEY (release_id, day)324);325CREATE TABLE download_dedupe (326 day date NOT NULL,327 hash text NOT NULL,328 PRIMARY KEY (day, hash)329);330331CREATE TABLE reports (332 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),333 artifact_id uuid REFERENCES artifacts(id),334 release_id uuid REFERENCES releases(id),335 category text NOT NULL CHECK (category IN ('malware','secret-leak','license','impersonation','spam','broken','other')),336 details text NOT NULL,337 reporter_user_id uuid REFERENCES users(id),338 reporter_contact text,339 state text NOT NULL DEFAULT 'open' CHECK (state IN ('open','triaged','resolved','dismissed')),340 resolution_note text,341 resolved_by uuid REFERENCES users(id),342 resolved_at timestamptz,343 created_at timestamptz NOT NULL DEFAULT now()344);345CREATE INDEX reports_state ON reports (state, created_at DESC);346347CREATE TABLE moderation_actions (348 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),349 actor_user_id uuid NOT NULL REFERENCES users(id),350 action text NOT NULL,351 target_type text NOT NULL,352 target_id text NOT NULL,353 reason text NOT NULL,354 report_id uuid REFERENCES reports(id),355 created_at timestamptz NOT NULL DEFAULT now()356);357358CREATE TABLE security_advisories (359 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),360 artifact_id uuid NOT NULL REFERENCES artifacts(id),361 release_id uuid REFERENCES releases(id),362 title text NOT NULL,363 body text NOT NULL,364 severity text NOT NULL,365 published_by uuid REFERENCES users(id),366 published_at timestamptz NOT NULL DEFAULT now()367);368369-- Schema present for Phase 4 features (not exposed in V1; see docs/IMPLEMENTATION_PLAN.md).370CREATE TABLE ownership_transfers (371 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),372 namespace_id uuid REFERENCES namespaces(id),373 artifact_id uuid REFERENCES artifacts(id),374 from_user_id uuid NOT NULL REFERENCES users(id),375 to_user_id uuid NOT NULL REFERENCES users(id),376 state text NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','accepted','declined','cancelled','admin_approved')),377 from_confirmed_at timestamptz,378 to_confirmed_at timestamptz,379 reviewed_by uuid REFERENCES users(id),380 created_at timestamptz NOT NULL DEFAULT now()381);382CREATE TABLE webhooks (383 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),384 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,385 namespace text NOT NULL,386 url text NOT NULL,387 secret_hash text NOT NULL,388 events text[] NOT NULL,389 active boolean NOT NULL DEFAULT true,390 created_at timestamptz NOT NULL DEFAULT now()391);392CREATE TABLE webhook_deliveries (393 id bigserial PRIMARY KEY,394 webhook_id uuid NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,395 event text NOT NULL,396 payload jsonb NOT NULL,397 status_code int,398 attempt int NOT NULL DEFAULT 1,399 delivered_at timestamptz,400 created_at timestamptz NOT NULL DEFAULT now()401);402403CREATE TABLE idempotency_keys (404 actor text NOT NULL,405 key text NOT NULL,406 route text NOT NULL,407 request_hash text NOT NULL,408 status int NOT NULL,409 body jsonb NOT NULL,410 created_at timestamptz NOT NULL DEFAULT now(),411 PRIMARY KEY (actor, key)412);413