Implement book-level awards, resources, endorsements, reviews & feature video

[ja573]

Goals

• Add database tables and Rust models for:
  • work resources (incl. optional file-backed resources)
  • work awards
  • work endorsements
  • work reviews
  • work feature video
• Expose these via GraphQL on the Work type.
• Support book-level resourcesDescription text.
• Ensure we only attach these entities to book records (not chapters).
• Keep data models generic and future-proof where possible.

Data model proposals

All tables should:

• Use uuid_generate_v4() primary keys.
• Reference work(work_id) with ON DELETE CASCADE.
• Include created_at / updated_at timestamps.
• Use *_ordinal fields for deterministic ordering per work.

1. Additional resources

Resources are linked to a book but not embedded in the book text (e.g. blog posts, datasets, appendices, media files).

File uploads must be allowed (but not required) for additional resources. The upload URL structure is: /{doi_prefix}/{doi_suffix}/resources/{uuid}.{file_extension}

resource_type enum

CREATE TYPE resource_type AS ENUM (
  'AUDIO',
  'VIDEO',
  'IMAGE',
  'BLOG',
  'WEBSITE',
  'DOCUMENT',
  'BOOK',
  'ARTICLE',
  'MAP',
  'SOURCE',
  'DATASET',
  'SPREADSHEET',
  'OTHER'
);

additional_resource table

CREATE TABLE additional_resource (
  additional_resource_id   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  work_id            UUID NOT NULL REFERENCES work(work_id) ON DELETE CASCADE,

  -- Core metadata
  title              TEXT NOT NULL,
  description   TEXT,
  attribution        TEXT,
  resource_type      resource_type NOT NULL,

  -- Linking identifiers
  doi                TEXT,
  handle             TEXT,
  url                TEXT,

  resource_ordinal   INTEGER NOT NULL DEFAULT 1 CHECK (resource_ordinal > 0),

  created_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Ensure deterministic ordering per-work:
CREATE UNIQUE INDEX idx_work_resource_workid_ordinal ON work_resource (work_id, resource_ordinal);

Work-level description

Add a description field on work:

ALTER TABLE work
ADD COLUMN resources_description TEXT;

GrahQL schema

type Work {
  workId: UUID!
  doi: String!
  # returns converted markup (string) for requested locale/format
  resourcesDescription(markupFormat: MarkupFormat = JATS): String
  additionalResources(
    markupFormat: MarkupFormat = JATS,
    limit: Int = 50,
    offset: Int = 0
  ): [WorkResource!]!
}

type WorkResource {
  workResourceId: UUID!
  title(markupFormat: MarkupFormat = JATS): String!
  description(markupFormat: MarkupFormat = JATS): String
  attribution: String
  resourceType: String
  doi: String
  handle: String
  url: String
}

2. Awards

CREATE TABLE award (
    award_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    work_id         UUID NOT NULL REFERENCES work(work_id) ON DELETE CASCADE,

    title           TEXT NOT NULL,
    url             TEXT,
    category        TEXT,
    note            TEXT,
    award_ordinal   INTEGER NOT NULL DEFAULT 1 CHECK (award_ordinal > 0),

    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX idx_work_award_workid_ordinal ON work_award (work_id, award_ordinal);

Similar to resources, allow markupFormat on title and note

3. Endorsements

CREATE TABLE endorsement (
    endorsement_id      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    work_id             UUID NOT NULL REFERENCES work(work_id) ON DELETE CASCADE,

    -- Core endorsement metadata
    author_name         TEXT,
    author_role         TEXT,
    url                 TEXT,

    -- canonical JATS text of the endorsement (nullable)
    text           TEXT,

    endorsement_ordinal INTEGER NOT NULL DEFAULT 1 CHECK (endorsement_ordinal > 0),

    created_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Optional deterministic ordering per-work:
CREATE UNIQUE INDEX idx_endorsement_workid_ordinal ON work_endorsement (work_id, endorsement_ordinal);

Allow markupFormat on text

4. Book Reviews

CREATE TABLE book_review (
    book_review_id      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    work_id             UUID NOT NULL REFERENCES work(work_id) ON DELETE CASCADE,

    -- bibliographic metadata
    title               TEXT,
    author_name         TEXT,
    url                 TEXT,
    doi                 TEXT,
    review_date         DATE,         -- date of review/publication of the review
    journal_name        TEXT,
    journal_volume      TEXT,
    journal_number      TEXT,
    journal_issn        TEXT,

    -- canonical JATS text of the review (nullable)
    text                TEXT,

    review_ordinal      INTEGER NOT NULL DEFAULT 1 CHECK (review_ordinal > 0),

    created_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- deterministic ordering per work
CREATE UNIQUE INDEX idx_book_review_workid_ordinal ON book_review (work_id, review_ordinal);

Allow markupFormat on text

5. Featured video

CREATE TABLE work_featured_video (
  work_featured_video_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  work_id                UUID NOT NULL UNIQUE REFERENCES work(work_id) ON DELETE CASCADE,

  -- Core fields (from Strapi)
  video_id               TEXT,   -- platform-specific id
  title                  TEXT,            -- human title/caption (plain text)
  width                  INTEGER NOT NULL DEFAULT 560 CHECK (width > 0),
  height                 INTEGER NOT NULL DEFAULT 315 CHECK (height > 0),

  created_at             TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);