Multi-Tenant PostgreSQL MCP Server

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE SCHEMA IF NOT EXISTS dare; CREATE SCHEMA IF NOT EXISTS nutriart; -- Dare schema tables CREATE TABLE IF NOT EXISTS dare.app_event ( event_id BIGSERIAL NOT NULL, user_id UUID NOT NULL DEFAULT uuid_generate_v4(), event_type TEXT NOT NULL, event_timestamp TIMESTAMPTZ NOT NULL DEFAULT now(), payload JSONB, PRIMARY KEY (event_timestamp, event_id) ); CREATE TABLE IF NOT EXISTS dare.app_event_metadata ( metadata_id BIGSERIAL NOT NULL, event_timestamp TIMESTAMPTZ NOT NULL, event_id BIGINT NOT NULL, meta_key TEXT NOT NULL, meta_value TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (metadata_id), CONSTRAINT fk_dare_event FOREIGN KEY (event_timestamp, event_id) REFERENCES dare.app_event(event_timestamp, event_id) ON DELETE CASCADE ); -- Nutriart schema tables CREATE TABLE IF NOT EXISTS nutriart.app_event ( event_id BIGSERIAL NOT NULL, user_id UUID NOT NULL DEFAULT uuid_generate_v4(), event_type TEXT NOT NULL, event_timestamp TIMESTAMPTZ NOT NULL DEFAULT now(), payload JSONB, PRIMARY KEY (event_timestamp, event_id) ); CREATE TABLE IF NOT EXISTS nutriart.app_event_metadata ( metadata_id BIGSERIAL NOT NULL, event_timestamp TIMESTAMPTZ NOT NULL, event_id BIGINT NOT NULL, meta_key TEXT NOT NULL, meta_value TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (metadata_id), CONSTRAINT fk_nutriart_event FOREIGN KEY (event_timestamp, event_id) REFERENCES nutriart.app_event(event_timestamp, event_id) ON DELETE CASCADE ); -- Seed data for dare schema CREATE TEMP TABLE dare_inserted_events AS WITH ins AS ( INSERT INTO dare.app_event (user_id, event_type, payload) VALUES ('a3c9e5b2-1f6d-4c8a-b7e2-0d5f8a9c1234', 'login', '{"ip":"192.168.1.10"}'), ('b7f1d2c9-3e44-4d71-ae23-9fae6c7b5678', 'purchase', '{"item_id":42,"price":19.99}') RETURNING event_timestamp, event_id ) SELECT event_timestamp, event_id FROM ins; INSERT INTO dare.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'device', 'mobile' FROM dare_inserted_events WHERE event_id = (SELECT event_id FROM dare_inserted_events LIMIT 1); INSERT INTO dare.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'app_version', '3.2.1' FROM dare_inserted_events WHERE event_id = (SELECT event_id FROM dare_inserted_events LIMIT 1); INSERT INTO dare.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'currency', 'USD' FROM dare_inserted_events OFFSET 1 LIMIT 1; INSERT INTO dare.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'payment_method','credit_card' FROM dare_inserted_events OFFSET 1 LIMIT 1; -- Seed data for nutriart schema CREATE TEMP TABLE nutriart_inserted_events AS WITH ins AS ( INSERT INTO nutriart.app_event (user_id, event_type, payload) VALUES ('c1d2e3f4-5678-90ab-cdef-1234567890ab', 'recipe_view', '{"recipe_id":101}'), ('d9e8f7a6-5432-10fe-bcda-0987654321fe', 'ingredient_add', '{"ingredient":"avocado","quantity":2}') RETURNING event_timestamp, event_id ) SELECT event_timestamp, event_id FROM ins; INSERT INTO nutriart.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'referrer', 'search' FROM nutriart_inserted_events WHERE event_id = (SELECT event_id FROM nutriart_inserted_events LIMIT 1); INSERT INTO nutriart.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'duration_seconds','45' FROM nutriart_inserted_events WHERE event_id = (SELECT event_id FROM nutriart_inserted_events LIMIT 1); INSERT INTO nutriart.app_event_metadata (event_timestamp, event_id, meta_key, meta_value) SELECT event_timestamp, event_id, 'source', 'manual_entry' FROM nutriart_inserted_events OFFSET 1 LIMIT 1;