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

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

Authors can delete drafts and returned posts, but deletion must preserve any editorial decisions already recorded. Each history row has a foreign key to posts, so we keep the post’s row too. To support this, we add a column to posts:

  deleted_at         timestamp null

The timestamp is null until the author deletes the post. Deleting a post sets that timestamp, and the application excludes deleted posts from its ordinary lists and pages and refuses further edits or submissions. This is called soft deletion. The post and its history remain stored for recordkeeping.

Soft deletion leaves the post’s last editorial state unchanged. It does not add a row to post_state_changes, which records creation and transitions between the four editorial states. deleted_at records when the author deleted the post.

The posts table still holds state, editor_id, and published_at. state and published_at can now be derived from post_state_changes. editor_id records the current assignment. Whether to keep the duplicated values is the next question.