-- Initialize database for OSM MCP Server
-- This script sets up the initial schema for hosted service mode
-- Create extension for UUID support
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Users table for authentication
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
api_key VARCHAR(255) UNIQUE,
is_active BOOLEAN DEFAULT true,
is_admin BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP WITH TIME ZONE
);
-- API Keys table for token management
CREATE TABLE IF NOT EXISTS api_keys (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
key_name VARCHAR(255) NOT NULL,
api_key VARCHAR(255) UNIQUE NOT NULL,
is_active BOOLEAN DEFAULT true,
expires_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_used TIMESTAMP WITH TIME ZONE
);
-- Rate limit tracking table
CREATE TABLE IF NOT EXISTS rate_limits (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
endpoint VARCHAR(255) NOT NULL,
request_count INTEGER DEFAULT 0,
window_start TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, endpoint, window_start)
);
-- Request logs table for analytics
CREATE TABLE IF NOT EXISTS request_logs (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
endpoint VARCHAR(255) NOT NULL,
method VARCHAR(10) NOT NULL,
status_code INTEGER,
response_time_ms INTEGER,
ip_address INET,
user_agent TEXT,
request_body JSONB,
response_body JSONB,
error_message TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_api_key ON users(api_key);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_key ON api_keys(api_key);
CREATE INDEX IF NOT EXISTS idx_rate_limits_user_endpoint ON rate_limits(user_id, endpoint);
CREATE INDEX IF NOT EXISTS idx_request_logs_user_id ON request_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_request_logs_created_at ON request_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_request_logs_endpoint ON request_logs(endpoint);
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger to auto-update updated_at
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Insert default admin user (password: admin123 - CHANGE THIS IN PRODUCTION!)
-- Password hash for 'admin123' using bcrypt
INSERT INTO users (username, email, password_hash, is_admin, is_active)
VALUES (
'admin',
'admin@osm-mcp.local',
'$2a$10$rN9YGJ9YZ7X5q8Y9Z9Z9Z.Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9Z9',
true,
true
)
ON CONFLICT (username) DO NOTHING;
-- Grant permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO osmuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO osmuser;
-- Success message
DO $$
BEGIN
RAISE NOTICE 'Database initialized successfully!';
RAISE NOTICE 'Default admin user created: admin / admin123';
RAISE NOTICE 'IMPORTANT: Change the default admin password immediately!';
END $$;
