Issue 1: Scaffold blog_content Module and Core Database Foundation #537

Parent Epic: #536

Objective

Create the initial blog_content module foundation, including module descriptor, domain validation files, application placeholders, core PostgreSQL schema, RLS policies, least-privilege grants, and permission seed migration.

Context

This is the foundation task for the blog_content derived module. It must build on AWCMS-Mini base capabilities and must not duplicate auth, tenant, RBAC, ABAC, audit, sync, or admin shell systems.

Scope

• Add src/modules/blog-content/module.ts
• Add src/modules/blog-content/README.md
• Add initial domain validation modules
• Add placeholder application modules
• Add core blog database migration
• Add permission seed migration
• Enable and force RLS for tenant-scoped tables
• Add least-privilege grants to awcms_mini_app
• Add initial schema support for posts, pages, terms, post-term relations, revisions, redirects, and settings

Out of Scope

• Admin UI implementation
• Public blog routes
• Template/widget/menu/media/ads implementation
• EmDash dependency or plugin system
• Rebuilding AWCMS-Mini base features

Expected Files

src/modules/blog-content/module.ts
src/modules/blog-content/README.md
src/modules/blog-content/domain/content-validation.ts
src/modules/blog-content/domain/post-status.ts
src/modules/blog-content/domain/slug-policy.ts
src/modules/blog-content/domain/seo-validation.ts
src/modules/blog-content/domain/taxonomy-policy.ts
src/modules/blog-content/application/blog-post-directory.ts
src/modules/blog-content/application/blog-taxonomy-directory.ts
sql/NNN_awcms_mini_blog_content_schema.sql
sql/NNN_awcms_mini_blog_content_permissions.sql
tests/blog-content-domain.test.ts
tests/integration/blog-content-schema.integration.test.ts

Database Tables

Create these core tables:

• awcms_mini_blog_posts
• awcms_mini_blog_pages
• awcms_mini_blog_terms
• awcms_mini_blog_post_terms
• awcms_mini_blog_revisions
• awcms_mini_blog_redirects
• awcms_mini_blog_settings

Core Data Rules

Posts

Required fields should include:

• id uuid primary key
• tenant_id uuid not null references awcms_mini_tenants(id)
• author_tenant_user_id uuid not null
• title text not null
• slug text not null
• excerpt text
• content_json jsonb not null
• content_text text not null
• status text not null
• visibility text not null default 'public'
• featured_media_id uuid null
• seo_title text
• meta_description text
• canonical_url text
• locale text not null default 'id'
• published_at timestamptz
• scheduled_at timestamptz
• created_at timestamptz not null default now()
• updated_at timestamptz not null default now()
• deleted_at timestamptz
• deleted_by uuid
• delete_reason text
• version integer not null default 1
• search_vector tsvector

Allowed status values:

• draft
• review
• scheduled
• published
• archived

Allowed visibility values:

• public
• private
• unlisted

Required indexes/constraints:

• Unique slug per tenant and locale where deleted_at is null
• Index on (tenant_id, status, published_at desc)
• Index on (tenant_id, author_tenant_user_id)
• Index on (tenant_id, deleted_at)
• GIN index on search_vector

Pages

Use the same core structure as posts, with additional fields:

• page_type text not null default 'standard'
• parent_page_id uuid null
• menu_order integer not null default 0

Allowed page_type values:

• standard
• landing
• legal
• system

Terms

Used for categories and tags.

Rules:

• Category may use parent_id.
• Tag must have parent_id = null.
• Slug must be unique per tenant and taxonomy type where not deleted.

Allowed taxonomy_type values:

• category
• tag

Revisions

Rules:

• Revisions are append-only.
• Restoring a revision must create a new revision.
• Normal endpoints must not overwrite or delete prior revision history.

RLS Requirements

Every tenant-scoped table must include:

ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table_name> FORCE ROW LEVEL SECURITY;

CREATE POLICY <table_name>_tenant_isolation
ON <table_name>
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON <table_name> TO awcms_mini_app;

Do not rely on old migrations to add FORCE ROW LEVEL SECURITY for new tables.

Permission Seed

Seed permissions using ON CONFLICT DO NOTHING.

Minimum permissions:

blog_content.posts.read
blog_content.posts.create
blog_content.posts.update
blog_content.posts.publish
blog_content.posts.schedule
blog_content.posts.archive
blog_content.posts.delete
blog_content.posts.restore
blog_content.posts.purge
blog_content.posts.export

blog_content.pages.read
blog_content.pages.create
blog_content.pages.update
blog_content.pages.publish
blog_content.pages.archive
blog_content.pages.delete
blog_content.pages.restore
blog_content.pages.purge

blog_content.taxonomies.read
blog_content.taxonomies.configure

blog_content.revisions.read
blog_content.revisions.restore

blog_content.settings.read
blog_content.settings.configure

blog_content.seo.configure
blog_content.search.read

No implicit role grants. Permissions must be assignable through existing Access & Users management.

Acceptance Criteria

• Module descriptor key is blog_content.
• Module descriptor version is 0.1.0.
• Module descriptor status is experimental.
• Core blog tables are created.
• All tenant-scoped tables have tenant_id.
• All tenant-scoped tables use ENABLE ROW LEVEL SECURITY.
• All tenant-scoped tables use FORCE ROW LEVEL SECURITY.
• Tenant isolation policies are implemented.
• Least-privilege grants are applied to awcms_mini_app.
• Permission seed uses ON CONFLICT DO NOTHING.
• Slug uniqueness is enforced per tenant and locale where applicable.
• Search vector and GIN index exist for posts/pages.
• Revision table is append-only by design.
• Domain validation tests pass.
• Integration test verifies RLS isolation.
• No duplicate auth/RBAC/ABAC/audit/sync systems are introduced.

Validation Commands

bun run db:migrate
bun run test
bun run typecheck