Content tables

Reference. This page lists the content tables in your generated store, their real column names, and the rules that govern them.

Your store ships with a blog and an SEO layer. Both live in your own Supabase database, alongside the catalog and commerce tables. This page documents the six content tables, the two status enums that drive them, the per-minute job that publishes scheduled posts, and the row-level rules that decide who reads what. Every name and default below comes from the schema in templates/backend/supabase/migrations/.

The six content tables

The blog is six tables. One holds posts, one holds their categories, three hold the satellite data for each post (SEO, tags, comments), and the post and category tables each carry their own SEO columns.

Table	Primary key	Purpose
blog_posts	id (uuid)	One row per article. Body lives in content_json as a Tiptap document.
blog_categories	id (uuid)	One row per blog category. Posts reference it through blog_posts.category_id.
blog_post_seo	post_id (uuid)	Per-post SEO overrides. One-to-one with blog_posts, deleted with it.
blog_post_tags	(post_id, tag)	Free-text tags. A post can carry many; a tag is plain text, not its own table.
blog_comments	id (uuid)	Reader comments awaiting moderation. Deleted when the post is deleted.
marketing_page_seo	path (text)	SEO overrides for static storefront pages that have no database row behind them.

Two catalog tables carry SEO data too, so you see them on the SEO screen next to the blog: product_seo (keyed by product_id) and category_seo (keyed by category_id). They live with the catalog, not the blog, but they share the same column vocabulary as blog_post_seo.

blog_posts

blog_posts is the spine of the blog. Each row has a unique slug, a title, an excerpt, a cover image (cover_image_url, cover_caption), and an author (author_id, referencing your staff users). The body is content_json, a jsonb column holding a Tiptap document, not an HTML string.

Two columns are computed from the body when you save: words_count and reading_minutes. The store walks the Tiptap tree, counts whitespace-separated words, and divides by 220 words per minute, with a floor of 1 minute for any non-empty post. An empty post reports 0. The logic lives in src/lib/blog/reading-stats.ts.

The rebuild migration 20260625000000_backoffice_rebuild_phase0.sql added three flags to this table:

• is_featured (boolean, default false) marks a post for a featured slot.
• allow_comments (boolean, default true) toggles whether the post accepts comments.
• views_count (integer, default 0) is a view counter.

An updated_at trigger (trg_blog_posts_updated_at) sets the timestamp on every update, so you never write it by hand.

The blog_post_status enum

A post is always in one of four states. The column is blog_posts.status, typed by the blog_post_status enum, defaulting to draft.

Value	Meaning
draft	Not public. The default for a new post.
scheduled	Has a future published_at; waiting to go live.
published	Public. This is the only state readers can see.
archived	Pulled from the site but kept in the database.

Scheduled posts publish themselves

You do not flip a scheduled post to published by hand. Migration 20260516000011_pg_cron_blog_publish.sql registers a Postgres cron job named blog_publish_scheduled that runs every minute and promotes any due post:

UPDATE public.blog_posts
SET status = 'published'
WHERE status = 'scheduled'
 AND published_at IS NOT NULL
 AND published_at <= now();

The migration only registers the job when the pg_cron extension is installed. On a Supabase tier without pg_cron, the migration is a safe no-op, and scheduled-post publication has to be driven by an external cron that hits an internal route. So a scheduled post sitting past its time without going live points at one cause: pg_cron is not enabled on that database.

blog_categories

A category has a name, a unique slug, and a position for ordering. The rebuild migration added parent_id (a self-reference), so categories form a tree. Migration 20260613000001_blog_category_fields.sql added the per-category knobs the category editor exposes:

• description (text) shows under the name in the list and on the archive page.
• color (text) is an accent hex value, not a preset id.
• is_visible (boolean, default true) drives the published-versus-hidden state.
• show_in_menu (boolean, default true) adds a direct link in the blog navigation.
• is_indexed (boolean, default true) includes the archive in the sitemap.
• has_custom_archive (boolean, default false) switches the archive to a custom layout.
• meta_title and meta_description (text) override the archive page SEO.

blog_comments and moderation

Each comment carries an author_name, an optional author_email, the content, and a status typed by the comment_status enum. The enum has three values, defaulting to pending:

Value	Meaning
pending	Awaiting moderation. The default for a new comment.
approved	Cleared to show under the post.
spam	Flagged as spam.

The dashboard moderation actions write these states. Approving a comment sets status = 'approved'; flagging sets status = 'spam'; the bulk action flips every pending comment to approved in one statement and returns the count it changed. All four moderation actions require the editor role. The code is in src/lib/blog/mutations.ts.

The SEO tables

blog_post_seo holds the meta layer for a post: meta_title, meta_description, og_image_url, and schema_jsonld. The rebuild migration added the fields the SEO scoring needs: focus_keyword, secondary_keywords (a text array), og_title, og_description, canonical_url, is_indexed, and is_followed. The last two default to true. A post with no row in this table has no overrides yet.

marketing_page_seo covers the static pages that have no entity row, such as the home page or an about page. It is keyed by the public URL path (for example / or /about), with no foreign key, because a static route is not a database row. It carries the same vocabulary as the other SEO tables plus the indexing and sitemap knobs: twitter_card (default summary_large_image), keywords (a text array), is_indexed, is_followed, and in_sitemap, all defaulting to true. A row appears only once you edit a path; the SEO list left-joins this table against the static-route catalogue, so an unedited page still shows up with no override.

Note that marketing_page_seo is defined in migration 20260613000002 but is not yet applied to the shared sandbox project. When this migration has not run on a database, the SEO list falls back to the catalogue with no per-path overrides.

Who can read each row

Every content table has row-level security enabled. The read and write rules follow the staff-role pattern used across the schema.

• Staff read. Any signed-in staff member (anyone with a staff_roles row) can read all rows in blog_posts, blog_categories, blog_post_seo, blog_post_tags, and blog_comments, including drafts and pending comments.
• Staff write. Writing to those tables requires the owner, admin, or editor role.
• Public read of published posts. The public_read_blog_posts policy lets the anon role read blog_posts rows where status = 'published', and only those. A matching GRANT SELECT... TO anon backs it. Drafts, scheduled, and archived posts stay invisible to the public.
• marketing_page_seo is staff-only: any staff member reads, and owner, admin, or editor writes. It has no public-read policy.

The catalog SEO tables differ from the post SEO table on the public side: product_seo has a public-read policy scoped to active products, so a storefront can render product meta tags without a login. blog_post_seo has no public-read policy.

Related references

• Schema overview for how the content tables sit next to catalog and commerce.
• Row-level security for the full staff-role model.
• Catalog tables for product_seo and category_seo.
• Blog editor for editing posts in the dashboard.
• Your Supabase project for where this database lives.

Your prompts and generated code are not sent to OpenAI or Anthropic.