Support post revisions

[fennifith]

(Note: this is all TBD - we need to consider how this will be represented in the database)

Example of post revisions in use today: https://playfulprogramming.com/posts/ffg-fundamentals-derived-values

When a post revision is created, it should have a new slug as well as an incremented "version name" in the frontmatter.

---

Proposal:

erDiagram

    posts {
        uuid id PK
        string slug
        string locale
        string branch
        uuid group_id FK
        string collection_slug FK
        int collection_order
        string version_name
        int version_order
        string title
        string description
        int word_count
        string social_image
        string banner_image
        string original_link
        bool noindex
        timestamptz edited_at
        timestamptz published_at
        jsonb meta
    }

    posts }|--o| post_groups : "grouped by"

    post_groups {
        uuid id PK
    }

    posts ||--o{ post_authors : "authored by"
    posts ||--o{ post_tags : "tagged with"

    post_authors {
        uuid post_id PK
        string author_slug PK
    }

    post_tags {
        uuid post_id PK
        string tag PK
    }

Loading

Key changes:

• posts contains a unique constraint on slug/locale/branch, equivalent to the current composite PK on post_data. Any post queried using a slug will also need to know the branch (default: main) and locale (default: en) to obtain the current post_id reference.
• post_authors and post_tags both reference posts.id - so each instance of a post can have different relations
• post_groups is a new table with a single PK, referenced by posts - this will group revisions with unique slugs (such as ffg-fundamentals-derived-values)

The "current version" of any post can be solved by querying for the known slug, locale, and branch on posts. The post_id always points to the current version for that branch/locale.

To determine the list of post versions, the frontend obtains group_id from the current post, then queries for other posts records with the same group_id, locale, and branch. This will return a list of slugs and post_ids, which can then be ordered by posts.version_order and displayed with posts.version_name.

The sync-post task no longer needs to worry about upserts - each sync can fully replace the previous post record, creating a new post ID.