AX CMS — Supabase + Vue 3 Blog CMS

CREATE TABLE blogs (
  id               UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  title            TEXT        NOT NULL,
  slug             TEXT        NOT NULL UNIQUE,
  excerpt          TEXT,
  content          TEXT        NOT NULL,
  cover_image_url  TEXT,
  category         TEXT,
  tags             TEXT[]      DEFAULT '{}',
  meta_title       TEXT,
  meta_description TEXT,
  author_id        UUID        REFERENCES auth.users(id) ON DELETE SET NULL,
  author_name      TEXT,
  published        BOOLEAN     DEFAULT false,
  published_at     TIMESTAMPTZ,
  created_at       TIMESTAMPTZ DEFAULT NOW(),
  updated_at       TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX blogs_slug_idx   ON blogs(slug);
CREATE INDEX blogs_published_idx     ON blogs(published, published_at DESC);
CREATE INDEX blogs_category_idx      ON blogs(category);
CREATE INDEX blogs_tags_idx          ON blogs USING GIN(tags);
CREATE INDEX blogs_fts_idx           ON blogs
  USING GIN(to_tsvector('english', title || ' ' || COALESCE(excerpt,'') || ' ' || content));

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER blogs_updated_at
  BEFORE UPDATE ON blogs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Row Level Security
ALTER TABLE blogs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public read published" ON blogs FOR SELECT            USING (published = true);
CREATE POLICY "Auth read all"         ON blogs FOR SELECT TO authenticated USING (true);
CREATE POLICY "Auth insert"           ON blogs FOR INSERT TO authenticated WITH CHECK (auth.uid() = author_id);
CREATE POLICY "Auth update own"       ON blogs FOR UPDATE TO authenticated USING (auth.uid() = author_id);
CREATE POLICY "Auth delete own"       ON blogs FOR DELETE TO authenticated USING (auth.uid() = author_id);

CREATE POLICY "Auth upload" ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'blog-images');

CREATE POLICY "Public read" ON storage.objects
  FOR SELECT USING (bucket_id = 'blog-images');

CREATE POLICY "Auth delete own" ON storage.objects
  FOR DELETE TO authenticated
  USING (bucket_id = 'blog-images');