Database: Tables and Queries

-- In Supabase SQL Editor
CREATE TABLE posts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own posts
CREATE POLICY 'Users see own posts' ON posts
  FOR SELECT USING (auth.uid() = user_id);

-- Policy: users can insert their own posts
CREATE POLICY 'Users insert own posts' ON posts
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Policy: public posts visible to all
CREATE POLICY 'Public posts readable' ON posts
  FOR SELECT USING (true);  -- or add a published column check

// SELECT
const { data, error } = await supabase
  .from('posts')
  .select('id, title, created_at')
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(10);

// SELECT with JOIN (foreign key)
const { data } = await supabase
  .from('posts')
  .select('*, profiles(name, avatar_url)');

// INSERT
const { data } = await supabase.from('posts').insert({
  title: 'My Post',
  body: 'Content here',
  user_id: user.id
}).select();

// UPDATE
await supabase.from('posts')
  .update({ title: 'New Title' })
  .eq('id', postId);

// DELETE
await supabase.from('posts').delete().eq('id', postId);