feat(social-publishing): add secure social auto-posting outbox and connector foundation #643

Depends on: #631, #632, #636, #640

Context

News portal deployments need optional automatic posting to supported social platforms after a news article is published. Each platform has different permission, token, review, rate limit, and content rules, so AWCMS-Mini needs a secure provider-neutral foundation before adding platform adapters.

This is a full-online feature. It must not apply to offline/LAN profiles.

Objective

Add a provider-neutral social publishing foundation with account connections, outbound publishing queue/outbox, approval controls, retry/backoff, audit logs, idempotency, and readiness checks.

Scope

Add a new optional module area such as:

social_publishing

Core entities:

awcms_mini_social_accounts
awcms_mini_social_publish_rules
awcms_mini_social_publish_jobs
awcms_mini_social_publish_attempts
awcms_mini_social_publish_templates

Supported provider model:

provider_key
provider_account_id
provider_account_name
provider_account_type
connection_status
token_reference
scopes_json
expires_at
last_verified_at

Publishing job status:

pending
requires_approval
approved
scheduled
publishing
published
failed
cancelled
skipped
rate_limited
needs_reauth

Suggested permissions:

social_publishing.accounts.read
social_publishing.accounts.connect
social_publishing.accounts.disconnect
social_publishing.rules.read
social_publishing.rules.configure
social_publishing.jobs.read
social_publishing.jobs.approve
social_publishing.jobs.cancel
social_publishing.jobs.retry
social_publishing.logs.read

Required behavior

• Auto-posting can be disabled globally and per tenant.
• Auto-posting can be enabled per platform/account.
• Rules can trigger on post published, scheduled published, or manual editor action.
• Optional approval step before external publishing.
• Use outbox/queue pattern; never publish to external APIs inside the DB transaction that publishes the article.
• Use idempotency key per article/account/platform/action.
• Use retry/backoff with dead-letter/failed state.
• Store external post ID/URL after success.
• Store sanitized error codes/messages after failure.
• Support reauthorization flow when token expires.
• Provide admin UI for jobs, attempts, and retry/cancel actions.

Integration with news content

• Only publish public/published articles.
• Do not publish draft, private, archived, review, or soft-deleted posts.
• Use canonical /news/... URL.
• Use title, excerpt, canonical URL, and verified R2 image where provider supports image/article preview.
• Keep provider-specific media upload in provider adapter issues.

Out of scope

• Provider-specific Facebook/Instagram/LinkedIn/Telegram implementation.
• Social listening/comment sync.
• Scraping social metrics.
• Posting to personal accounts without supported platform permission.
• WhatsApp broadcast or unsolicited messaging.

Acceptance criteria

• Module can be enabled only for full-online profile.
• Social accounts are tenant-scoped and RLS-protected.
• Secret tokens are stored only as references to secret storage, not plain text in regular settings.
• Publishing jobs are created after eligible article publish event.
• Jobs are idempotent per article/platform/account.
• Jobs can require approval before external posting.
• Retry/backoff and terminal failed state are implemented.
• Admin UI shows accounts, rules, jobs, attempts, external IDs, and safe error messages.
• Audit log records account connection, rule changes, approval, publish attempts, success, failure, retry, cancellation, and disconnect.
• Readiness check fails if enabled provider is missing required credentials/scopes.
• OpenAPI/AsyncAPI are updated.
• Tests cover RLS, ABAC, idempotency, publish event job creation, approval workflow, retry/backoff, secret redaction, and no draft/private posting.
• bun run api:spec:check passes.
• bun run test passes.
• bun run build passes.
• bun run check passes.

Security notes

• Never log access tokens, refresh tokens, client secrets, or full OAuth callback payloads.
• Do not expose provider tokens to browser JavaScript.
• Use least-privilege scopes and platform review requirements.
• Keep correlation ID across publish job attempts.
• External API calls must have timeout, retry/backoff, and circuit breaker behavior.
• Default-deny ABAC for social publishing actions.