Recording Editorial Decisions

The current schema meets the functional requirements of HopPress for its initial release. Let’s see whether the non-functional requirements are met. Take the legal constraint from Chapter 1: the university must retain a record of every editorial decision. Stated as a requirement, HopPress must record who made each editorial decision, what the decision was, and when it was made.

Check the schema against it. editor_id says which editor is assigned to a post. published_at says when a post was published. That is all. The schema cannot say:

• when a post was submitted, or by whom
• when a post was returned, or what the editor said
• whether a post was returned more than once
• whether a different editor was assigned before the current one

One fix is to add columns to posts: submitted_at, returned_at, return_note. That records one submission and one return. When the author resubmits and the editor returns the post a second time, the new values overwrite the old ones, and the first decision is gone. The requirement asks for every decision, not the latest.

The solution is to store each decision in its own row in a new table, post_state_changes. Here is a schema for it:

post_state_changes
  change_id          integer   primary key
  post_id            integer   foreign key -> posts, not null
  actor_id           integer   foreign key -> users, not null
  from_state         text      null, check in ('draft', 'submitted', 'published', 'returned')
  to_state           text      not null, check in ('draft', 'submitted', 'published', 'returned')
  note               text      null
  occurred_at        timestamp not null

One row per transition. Creating a draft adds the first row for a post to post_state_changes, with from_state null. Submitting, publishing, and returning each add a row. actor_id is the user who made the change, whether author or editor. note holds the editor’s comment when a post is returned.

erDiagram
    direction LR
    posts ||--o{ post_state_changes : has
    users ||--o{ post_state_changes : makes

The posts table still holds state, editor_id, and published_at. Each can now be derived from post_state_changes. Whether to keep them is the next question.