SQL for Rosebud

-- Versatile SQL Schema for Apps With Login:

-- Table: users (for authentication and profiles)
CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT auth.uid(),
  username text NOT NULL,
  email text NOT NULL UNIQUE,
  bio text,
  extra_data jsonb,
  created_at timestamptz DEFAULT now()
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their profile" ON users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can insert their profile" ON users FOR INSERT WITH CHECK (auth.uid() = id);

-- Table: posts
CREATE TABLE IF NOT EXISTS posts (
  id serial PRIMARY KEY,
  title text NOT NULL,
  content text,
  author_id uuid REFERENCES users(id),
  category text,
  tags text[],
  extra jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their posts" ON posts FOR SELECT USING (auth.uid() = author_id);
CREATE POLICY "Users can insert their posts" ON posts FOR INSERT WITH CHECK (auth.uid() = author_id);

-- Table: comments
CREATE TABLE IF NOT EXISTS comments (
  id serial PRIMARY KEY,
  post_id int REFERENCES posts(id),
  author_id uuid REFERENCES users(id),
  content text NOT NULL,
  extra jsonb,
  created_at timestamptz DEFAULT now()
);
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their comments" ON comments FOR SELECT USING (auth.uid() = author_id);
CREATE POLICY "Users can insert their comments" ON comments FOR INSERT WITH CHECK (auth.uid() = author_id);

-- Table: files
CREATE TABLE IF NOT EXISTS files (
  id serial PRIMARY KEY,
  file_name text NOT NULL,
  file_url text NOT NULL,
  file_type text,
  metadata jsonb,
  owner_id uuid REFERENCES users(id),
  uploaded_at timestamptz DEFAULT now()
);
ALTER TABLE files ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their files" ON files FOR SELECT USING (auth.uid() = owner_id);
CREATE POLICY "Users can insert their files" ON files FOR INSERT WITH CHECK (auth.uid() = owner_id);

-- Table: generic_data (for miscellaneous app data)
CREATE TABLE IF NOT EXISTS generic_data (
  id serial PRIMARY KEY,
  data jsonb,
  owner_id uuid REFERENCES users(id),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
ALTER TABLE generic_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their generic data" ON generic_data FOR SELECT USING (auth.uid() = owner_id);
CREATE POLICY "Users can insert their generic data" ON generic_data FOR INSERT WITH CHECK (auth.uid() = owner_id);