logicspike/docs

Content Engine

Phase 3: Domain Model — Content Engine

Last Updated: 2026-03-15

This document defines the core entities, their attributes, relationships, and state machines for the Content Engine — independent of any database implementation.

ID Convention: All entity IDs are string type (generated via crypto.randomUUID() or similar), stored as text columns — consistent with the blog-service and manager schema patterns. The notation UUID below refers to this string format, not a PostgreSQL UUID column type.

1. Entity Map

┌──────────────────────────────────────────────────────────────────┐
│                          Tenant (from core-tenant)               │
│                                                                  │
│  ┌─────────────┐    ┌─────────────────┐    ┌──────────────────┐ │
│  │  Campaign    │    │ ContentSlot     │    │SocialConnection  │ │
│  │             │◀───│                 │───▶│                  │ │
│  └─────────────┘    │  ┌───────────┐  │    └──────────────────┘ │
│                     │  │SlotTarget │  │                         │
│  ┌─────────────┐    │  └───────────┘  │    ┌──────────────────┐ │
│  │   Label     │◀───│                 │    │RecurringSchedule │ │
│  └─────────────┘    └─────────────────┘    └──────────────────┘ │
│                                                                  │
│  ┌─────────────┐                                                 │
│  │ PublishLog  │                                                 │
│  └─────────────┘                                                 │
└──────────────────────────────────────────────────────────────────┘

2. Entities

2.1 SocialConnection

Represents a linked social media account for a tenant.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
platform Enum twitter, instagram, linkedin, facebook
platformAccountId String The external account ID on the platform
platformUsername String Display name / handle (e.g., @clientbrand)
accessToken EncryptedString OAuth2 access token (AES-256-GCM)
refreshToken EncryptedString OAuth2 refresh token (AES-256-GCM)
tokenExpiresAt DateTime When the access token expires
status Enum active, expired, revoked, error
connectedBy UUID FK → User who connected it
createdAt DateTime —
updatedAt DateTime —

Business Rules:

  • A tenant can have multiple connections per platform (multi-brand agencies)
  • If tokenExpiresAt < NOW() + 48h, the proactive refresh job should refresh it
  • If refresh fails, status → expired and tenant is notified

2.2 Campaign

A logical grouping of content for planning purposes.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
name String e.g., "Q1 Product Launch"
description String Optional notes
color HexColor Calendar display color
startDate Date Campaign start (for filtering)
endDate Date Campaign end
status Enum active, completed, archived
createdAt DateTime —
updatedAt DateTime —
createdBy UUID FK → User who created it

Business Rules:

  • Campaigns are optional — content slots can exist without one
  • Archiving a campaign does not cancel its scheduled slots
  • A campaign's endDate must be >= startDate

2.3 Label

Lightweight tags for categorizing content.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
name String e.g., "ProductUpdate", "Motivational"
color HexColor Badge color
createdAt DateTime —

Relationship: Many-to-Many with ContentSlot via content_slot_labels junction table.

2.4 ContentSlot

The core entity — a single piece of content to be published.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
campaignId UUID? FK → Campaign (nullable)
contentType Enum text, image, video, carousel, story, link
caption Text The post text / copy
mediaIds UUID[] References to Media Service assets
linkUrl String? External URL to include (for link-type posts)
scheduledAt DateTime (UTC) When to publish
timezone String IANA timezone (e.g., Asia/Kolkata) for display
status Enum See state machine below
approvalNote Text? Reviewer's comment (on reject/approve)
recurringScheduleId UUID? FK → RecurringSchedule (if auto-generated)
source Enum manual, recurring, event_bus, mcp_agent
sourceAgentId String? If created by MCP agent, the agentId
createdBy UUID FK → User (or system user for auto-generated)
updatedAt DateTime —
createdAt DateTime —

State Machine (see adr_architecture.md ADR-4 for full diagram):

draft → pending_review → approved → scheduled → publishing → published
                 ↓ (reject)                            ↓ (fail)
               draft                                 failed → draft

2.5 SlotTarget

A junction entity: one ContentSlot can publish to multiple platforms.

Attribute Type Description
id UUID Primary key
contentSlotId UUID FK → ContentSlot
socialConnectionId UUID FK → SocialConnection
platformCaption Text? Per-platform override caption
publishStatus Enum pending, publishing, published, failed
externalPostId String? The ID returned by the platform after publishing
externalPostUrl String? Link to the published post
errorMessage String? Error details if failed
retryCount Int Number of publish attempts
publishedAt DateTime? When it was actually published

Business Rules:

  • A ContentSlot's overall status = published only when all SlotTargets are published
  • If any SlotTarget fails after all retries, the ContentSlot's status = partially_failed (if some targets succeeded) or failed (if all failed)
  • Maximum 3 retries per SlotTarget
  • A SlotTarget's platformCaption overrides the parent ContentSlot's caption for that platform

2.6 RecurringSchedule

Defines a recurring content pattern.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
name String e.g., "Motivation Monday"
cronExpression String Cron pattern: 0 10 * * 1 (Mon 10 AM)
timezone String IANA timezone for cron evaluation
templateCaption Text Template with {variables}
templateMediaIds UUID[]? Default media attachments
targetConnectionIds UUID[] Which social accounts to publish to
autoSchedule Boolean Skip approval?
isActive Boolean Can be paused
nextGenerateAt DateTime Next slot generation time
createdAt DateTime —
updatedAt DateTime —

Business Rules:

  • The generation job creates slots 7 days in advance
  • Generated slots are editable — the template just provides defaults
  • Deactivating a schedule does not cancel already-generated slots

2.7 PublishLog

Immutable audit trail of every publish attempt.

Attribute Type Description
id UUID Primary key
tenantId UUID FK → Tenant
slotTargetId UUID FK → SlotTarget
action Enum publish_attempt, publish_success, publish_failed, retry
platformResponse JSON? Raw response from platform API
errorCode String? Normalized error code
createdAt DateTime —

3. Aggregate Boundaries

Aggregate Root Contains
ContentSlot SlotTargets (create/update/delete together)
SocialConnection Standalone (referenced by SlotTarget)
Campaign Standalone (referenced by ContentSlot)
RecurringSchedule Standalone (generates ContentSlots)

4. Key Invariants

  1. A ContentSlot cannot be scheduled without at least 1 SlotTarget
  2. A SlotTarget must reference an active SocialConnection
  3. A ContentSlot's scheduledAt must be in the future at time of scheduling
  4. A ContentSlot in publishing status cannot be edited or deleted
  5. A published ContentSlot cannot be re-published (immutable terminal state)
  6. Deleting a SocialConnection does not cascade to ContentSlots — slots targeting a deleted connection are marked as failed with error connection_removed
Content Engine