The content layer has four tables — writers, content_docs, content_assignments, content_activity_log. This page shows every field on each so the content team (writers, team leads) can QA the schema against the portal they'll use.
A parent order enters the content stage → a content_docs row is created → assigned to a writer → writer drafts the article → team lead QAs → doc is handed off back to fulfillment. The handoff today is manual; handed_off_at is the hook for automating it.
revision_count.
canceled is terminal from any state.
Values are documented via COMMENT ON but not CHECK-enforced — we can add new substates later without a migration.
Third-party writers; not staff. Kept distinct from users (staff) and suppliers (vendors / publishers) because they have a narrower feature set and different pay model (per-word vs. per-placement).
One Google Doc per order. The same doc may be reassigned across writers (via content_assignments); this row stays the same.
orders. One doc per order.The active writer on this doc. Prior assignments stay in the table with is_current = false. Team lead (reviewer_user_id) is from the users table — internal staff, not a third-party writer.
writers. Reassignable via new rows with is_current flipped.writer_id. ON DELETE SET NULL so review history survives if the reviewer leaves.users by email.submitted_for_qa → revision_requested transition. Signal for writer quality trends.content_docs.qa_notes is for the final QA snapshot.Doc-level QA snapshot. When handed_off_at is populated, fulfillment owns the content; the order's content_url is synced and the supplier can be notified.
users FK later.qa_approved=true and fulfillment picks it up. Null = not yet handed off.sent boolean.Append-only trail of content actions — writer picks up, submits, team lead reviews, revision requested, handed off, archived. Populated by the portal and cron; staff rarely write directly.