ebaf4c9c99Fixes #228 ## Motivation and Context See #19 ## How Has This Been Tested? - Run locally - Run in docker compose - Run infra in minikube - Unit and integration tests all passing :) ## Breaking Changes Yes: users will not be able to access any data they used to have in custom mongodb. We are accepting this breaking change, given the registry is in experimental development status. ## Types of changes <!-- What types of changes does your code introduce? Put an `x` in all the boxes that apply: --> - [ ] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [x] Breaking change (fix or feature that would cause existing functionality to change) - [ ] Documentation update ## Checklist <!-- Go over all the following points, and put an `x` in all the boxes that apply. --> - [x] I have read the [MCP Documentation](https://modelcontextprotocol.io) - [x] My code follows the repository's style guidelines - [x] New and existing tests pass locally - [x] I have added appropriate error handling - [x] I have added or updated documentation as needed ## Additional context I have not implemented connection pooling support for simplicity, and because cloudnative-pg already provides built-in connection pooling with pgBouncer so if we want it later we can switch to this endpoint easily.
58 lines
1.9 KiB
PL/PgSQL
58 lines
1.9 KiB
PL/PgSQL
-- PostgreSQL schema for MCP Registry
|
|
-- This schema uses a hybrid approach with relational tables for core entities
|
|
-- and JSONB columns for complex nested data structures
|
|
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
|
|
|
-- Servers table - core server information
|
|
CREATE TABLE servers (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
status VARCHAR(50) DEFAULT 'active',
|
|
|
|
-- Repository information stored as JSONB for flexibility
|
|
repository JSONB,
|
|
|
|
-- Version details
|
|
version VARCHAR(255) NOT NULL,
|
|
release_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
is_latest BOOLEAN DEFAULT true,
|
|
|
|
-- Packages and remotes stored as JSONB arrays
|
|
packages JSONB DEFAULT '[]'::jsonb,
|
|
remotes JSONB DEFAULT '[]'::jsonb,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE UNIQUE INDEX idx_servers_id ON servers(id);
|
|
CREATE INDEX idx_servers_name ON servers(name);
|
|
CREATE UNIQUE INDEX idx_servers_name_version ON servers(name, version);
|
|
CREATE INDEX idx_servers_latest ON servers(is_latest) WHERE is_latest = true;
|
|
CREATE INDEX idx_servers_status ON servers(status);
|
|
|
|
-- GIN indexes for JSONB fields
|
|
CREATE INDEX idx_servers_repository_gin ON servers USING GIN(repository);
|
|
CREATE INDEX idx_servers_packages_gin ON servers USING GIN(packages);
|
|
CREATE INDEX idx_servers_remotes_gin ON servers USING GIN(remotes);
|
|
|
|
-- Function to update the updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Trigger to automatically update updated_at
|
|
CREATE TRIGGER update_servers_updated_at
|
|
BEFORE UPDATE ON servers
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|