init.sqlā¢2.89 kB
-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Skills table
CREATE TABLE skills (
skill_id VARCHAR(255) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
category VARCHAR(255),
tags TEXT[],
author VARCHAR(255),
author_id VARCHAR(255),
ai_generated BOOLEAN DEFAULT false,
verified BOOLEAN DEFAULT false,
version VARCHAR(50),
skill_md_url TEXT NOT NULL,
skill_md_content TEXT,
visibility VARCHAR(20) DEFAULT 'public',
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
downloads INTEGER DEFAULT 0,
-- Vector embedding for semantic search (1536 dimensions for OpenAI ada-002)
embedding vector(1536)
);
-- Indexes for performance
CREATE INDEX idx_skills_category ON skills(category);
CREATE INDEX idx_skills_tags ON skills USING GIN(tags);
CREATE INDEX idx_skills_author ON skills(author_id);
CREATE INDEX idx_skills_visibility ON skills(visibility);
CREATE INDEX idx_skills_created ON skills(created_at DESC);
-- Vector similarity index (using ivfflat for fast approximate search)
CREATE INDEX idx_skills_embedding ON skills USING ivfflat(embedding vector_cosine_ops) WITH (lists = 100);
-- Ratings table
CREATE TABLE skill_ratings (
rating_id SERIAL PRIMARY KEY,
skill_id VARCHAR(255) REFERENCES skills(skill_id) ON DELETE CASCADE,
user_id VARCHAR(255) NOT NULL,
rating INTEGER CHECK (rating >= 1 AND rating <= 5),
review TEXT,
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(skill_id, user_id)
);
CREATE INDEX idx_ratings_skill ON skill_ratings(skill_id);
CREATE INDEX idx_ratings_user ON skill_ratings(user_id);
-- Favorites table
CREATE TABLE skill_favorites (
favorite_id SERIAL PRIMARY KEY,
skill_id VARCHAR(255) REFERENCES skills(skill_id) ON DELETE CASCADE,
user_id VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(skill_id, user_id)
);
CREATE INDEX idx_favorites_user ON skill_favorites(user_id);
CREATE INDEX idx_favorites_skill ON skill_favorites(skill_id);
-- Usage analytics table
CREATE TABLE skill_usage (
usage_id SERIAL PRIMARY KEY,
skill_id VARCHAR(255) REFERENCES skills(skill_id) ON DELETE CASCADE,
user_id VARCHAR(255),
used_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_usage_timestamp ON skill_usage(used_at DESC);
CREATE INDEX idx_usage_skill ON skill_usage(skill_id);
CREATE INDEX idx_usage_skill_time ON skill_usage(skill_id, used_at);
-- View for skill statistics
CREATE VIEW skill_stats AS
SELECT
s.skill_id,
s.name,
COUNT(DISTINCT sr.rating_id) as rating_count,
AVG(sr.rating) as rating_avg,
COUNT(DISTINCT sf.favorite_id) as favorite_count,
COUNT(DISTINCT su.usage_id) as usage_count
FROM skills s
LEFT JOIN skill_ratings sr ON s.skill_id = sr.skill_id
LEFT JOIN skill_favorites sf ON s.skill_id = sf.skill_id
LEFT JOIN skill_usage su ON s.skill_id = su.skill_id
GROUP BY s.skill_id, s.name;