schema.sql

-- NextAuth required tables
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT,
  email TEXT UNIQUE,
  "emailVerified" TIMESTAMPTZ,
  image TEXT
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE TABLE IF NOT EXISTS accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "userId" UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  provider TEXT NOT NULL,
  "providerAccountId" TEXT NOT NULL,
  refresh_token TEXT,
  access_token TEXT,
  expires_at BIGINT,
  token_type TEXT,
  scope TEXT,
  id_token TEXT,
  session_state TEXT,
  UNIQUE(provider, "providerAccountId")
);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE TABLE IF NOT EXISTS sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "sessionToken" TEXT UNIQUE NOT NULL,
  "userId" UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires TIMESTAMPTZ NOT NULL
);
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;

CREATE TABLE IF NOT EXISTS verification_tokens (
  identifier TEXT NOT NULL,
  token TEXT UNIQUE NOT NULL,
  expires TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (identifier, token)
);
ALTER TABLE verification_tokens ENABLE ROW LEVEL SECURITY;

-- App-specific notes table
CREATE TABLE IF NOT EXISTS notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  date TEXT NOT NULL,           -- "YYYY-MM-DD"
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  tags TEXT[] NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE INDEX IF NOT EXISTS idx_notes_user_id ON notes(user_id);
CREATE INDEX IF NOT EXISTS idx_notes_user_date ON notes(user_id, date);

-- User settings table
CREATE TABLE IF NOT EXISTS user_settings (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  speech_provider TEXT NOT NULL DEFAULT 'web-speech-api',
  autocorrect_provider TEXT NOT NULL DEFAULT 'languagetool',
  autocorrect_language TEXT NOT NULL DEFAULT 'en-US',
  title_model TEXT NOT NULL DEFAULT 'groq/openai/gpt-oss-20b',
  tag_model TEXT NOT NULL DEFAULT 'groq/openai/gpt-oss-20b',
  summary_model TEXT NOT NULL DEFAULT 'groq/openai/gpt-oss-20b',
  pinned_tags TEXT[] NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;

-- Deny-all policies: app connects as postgres (bypasses RLS),
-- these block access via Supabase anon/authenticated keys
CREATE POLICY deny_all ON users FOR ALL TO anon, authenticated USING (false);
CREATE POLICY deny_all ON accounts FOR ALL TO anon, authenticated USING (false);
CREATE POLICY deny_all ON sessions FOR ALL TO anon, authenticated USING (false);
CREATE POLICY deny_all ON verification_tokens FOR ALL TO anon, authenticated USING (false);
CREATE POLICY deny_all ON notes FOR ALL TO anon, authenticated USING (false);
CREATE POLICY deny_all ON user_settings FOR ALL TO anon, authenticated USING (false);