Database Schema

Meridian Themes uses dedicated tables in Supabase for theme management.

Schema Overview

┌─────────────────┐     ┌─────────────────┐
│     themes      │────▶│  theme_tickers  │
└────────┬────────┘     └─────────────────┘
         │
         │              ┌─────────────────┐
         ├─────────────▶│ research_prompts│
         │              └─────────────────┘
         │
         │              ┌─────────────────┐
         └─────────────▶│ theme_insights  │
                        └─────────────────┘

Tables

themes

Stores the high-level investment thesis:

CREATE TABLE themes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active',  -- 'active', 'watchlist', 'archived'
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	uuid	Primary key
title	text	Theme name
description	text	Investment thesis
status	text	active/watchlist/archived
created_at	timestamptz	Creation timestamp

theme_tickers

Tickers associated with a theme:

CREATE TABLE theme_tickers (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  theme_id UUID REFERENCES themes(id) ON DELETE CASCADE,
  symbol TEXT NOT NULL,
  exchange TEXT,
  instrument_id UUID REFERENCES sable.instrument(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	uuid	Primary key
theme_id	uuid	Parent theme
symbol	text	Ticker symbol (e.g., 'HG=F')
exchange	text	Exchange code
instrument_id	uuid	Link to sable.instrument

research_prompts

Instructions for the AI research agent:

CREATE TABLE research_prompts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  theme_id UUID REFERENCES themes(id) ON DELETE CASCADE,
  prompt_text TEXT NOT NULL,
  frequency TEXT DEFAULT 'daily',  -- 'daily', 'weekly', 'on_demand'
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	uuid	Primary key
theme_id	uuid	Parent theme
prompt_text	text	AI prompt
frequency	text	Research schedule
is_active	boolean	Prompt enabled

theme_insights

Output of the AI research jobs:

CREATE TABLE theme_insights (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  theme_id UUID REFERENCES themes(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  sources JSONB,
  model TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	uuid	Primary key
theme_id	uuid	Parent theme
content	text	Markdown-formatted report
sources	jsonb	Array of citation URLs
model	text	AI model used

Integration with Sable

Instrument Linking

Theme tickers can link to the canonical instrument master:

-- Link to sable.instrument
SELECT t.*, i.name, i.sector
FROM theme_tickers t
LEFT JOIN sable.instrument i ON t.instrument_id = i.id
WHERE t.theme_id = $1;

Organization Context

Themes are scoped to organizations:

-- Add org_id to themes table (optional)
ALTER TABLE themes ADD COLUMN org_id UUID REFERENCES sable.org(id);

Row Level Security

Basic RLS Policies

-- Enable RLS
ALTER TABLE themes ENABLE ROW LEVEL SECURITY;

-- Users see their own themes
CREATE POLICY "Users can view own themes"
ON themes FOR SELECT
USING (auth.uid() = user_id);

-- Cascade to related tables
CREATE POLICY "Users can view own theme_tickers"
ON theme_tickers FOR SELECT
USING (theme_id IN (
  SELECT id FROM themes WHERE user_id = auth.uid()
));

Indexes

Recommended indexes for performance:

-- Theme lookups
CREATE INDEX idx_themes_status ON themes(status);
CREATE INDEX idx_themes_user ON themes(user_id);

-- Ticker lookups
CREATE INDEX idx_theme_tickers_theme ON theme_tickers(theme_id);
CREATE INDEX idx_theme_tickers_symbol ON theme_tickers(symbol);

-- Insight queries
CREATE INDEX idx_theme_insights_theme ON theme_insights(theme_id);
CREATE INDEX idx_theme_insights_created ON theme_insights(created_at DESC);