Lovable Prompts for Building a Blog Backend

Build a minimal blog CMS backend using Vite + React + TypeScript + Tailwind CSS + shadcn/ui, backed by Supabase (Lovable Cloud). Here is the exact schema, RLS, routes, and components you should scaffold:

**Database migration (run as 0001_blog_schema.sql):**

Create four tables:

1. `profiles` — columns: id uuid references auth.users primary key, full_name text, bio text, avatar_path text, twitter_handle text, role text default 'author'. RLS: public-read for full_name, bio, avatar_path (for author bio); per-user write own where id = auth.uid().

2. `categories` — columns: id uuid primary key default gen_random_uuid(), slug text unique not null, name text not null, description text. RLS: public-read, write only if profiles.role = 'admin' (use a SECURITY DEFINER plpgsql function `is_admin()` that checks `(SELECT role FROM profiles WHERE id = auth.uid()) = 'admin'` — never inline SQL in the policy).

3. `posts` — columns: id uuid primary key default gen_random_uuid(), author_id uuid references auth.users not null, slug text unique not null, title text not null, summary text, body_markdown text not null default '', cover_image_path text, status text check (status in ('draft','scheduled','published','archived')) default 'draft', published_at timestamptz, scheduled_for timestamptz, created_at timestamptz default now(), updated_at timestamptz default now(). RLS: public-read where `status = 'published' AND published_at <= now()`, per-author write where `author_id = auth.uid()`, admin write via is_admin(). Add a trigger `update_posts_updated_at` that sets updated_at = now() on every UPDATE. Add a partial index: `CREATE INDEX posts_published_idx ON posts(published_at DESC) WHERE status = 'published';`

4. `post_categories` — columns: post_id uuid references posts, category_id uuid references categories, primary key (post_id, category_id). RLS: inherit via post ownership for write, public-read.

Also create the `is_admin()` SECURITY DEFINER plpgsql function:
```sql
CREATE OR REPLACE FUNCTION is_admin() RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN (SELECT role FROM public.profiles WHERE id = auth.uid()) = 'admin';
END;
$$;
```

**Layouts:**
- `PublicLayout.tsx` — top nav with Logo / Blog / Categories / About links and a minimal footer.
- `AdminLayout.tsx` — left sidebar with: Posts, Categories, Settings. Wrap in `<AdminGuard>` that reads the Supabase auth session and the `profiles.role` field; redirect to /signin if not admin.

**Pages and routes:**
- `/` — post grid (newest published first), 3-column desktop / 1-column mobile, using PostCard components.
- `/[slug]` — post detail with PostBody (renders body_markdown via react-markdown + rehype-highlight). Show author bio from profiles. Show category chips. Show prev/next post links.
- `/category/[slug]` — filtered post grid.
- `/author/[handle]` — author bio + all their published posts.
- `/admin` — posts list with status filter chips (All / Draft / Scheduled / Published / Archived). Each row shows title, status, updated_at, Edit and Publish/Unpublish actions.
- `/admin/posts/new` and `/admin/posts/[id]/edit` — MarkdownEditor with a preview tab, cover image upload to Storage bucket 'post-media', category multi-select, status selector, and DraftSaver component that auto-saves to status='draft' every 30 seconds.
- `/signin` — Supabase Auth email/password form.

**Key components:**
- `PostCard.tsx` — image, title, summary, category chip, date, author avatar.
- `PostBody.tsx` — renders body_markdown with react-markdown + rehype-highlight. Import rehype-highlight and import 'highlight.js/styles/github.css'.
- `MarkdownEditor.tsx` — shadcn Tabs with Write and Preview tabs. Write tab: textarea bound to body_markdown. Preview tab: renders PostBody.
- `DraftSaver.tsx` — auto-saves body_markdown to the DB every 30 seconds. Show 'Saved 5s ago' using a lastSaved timestamp. Use useEffect + setInterval.
- `CategoryChip.tsx` — small pill badge linking to /category/[slug].
- `AdminGuard.tsx` — checks auth + role, shows loading spinner, redirects to /signin.

**Styling:** editorial reading experience. Use Source Serif Pro (Google Font import) for post body text. Max content width 720px for post detail. Line height 1.75. Use shadcn Card for PostCard, Tabs for MarkdownEditor, Toast for autosave feedback.

**Deliverables:** The build must produce a working public homepage with published post grid, a post detail page that renders markdown with syntax highlighting, an admin posts list with status filtering, and a markdown editor with 30-second autosave. Everything backed by the exact RLS schema above.