Problem / Opportunity

Open Library has no way for patrons to express appreciation for content. A generic like/dislike system would:

• Surface community signal data for Works, Authors, Lists, Series, Tags, Prompts — any record with an infogami key
• Enable future ranking, discovery, and community curation features
• Complement existing patron activity (reading log, booknotes, observations)

Success criteria: A logged-in patron can like or dislike any OL record via a backend API. Like counts are queryable. A patron cannot like the same record twice. An unauthenticated patron cannot like anything.

Proposal

Add a likes table to the Open Library database, a openlibrary/core/likes.py model, and a small set of backend API endpoints. No frontend UI is in scope for this issue.

The table is intentionally generic: it maps a patron username to any infogami key (e.g. /works/OL45883W, /authors/OL23919A, /type/list/OL123L) rather than being restricted to works.

Schema

CREATE TABLE likes (
    username    TEXT        NOT NULL,
    key         TEXT        NOT NULL,   -- full infogami key, e.g. /works/OL123W
    value       SMALLINT    NOT NULL DEFAULT 1 CHECK (value IN (1, -1)),
    created     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (username, key)
);
CREATE INDEX likes_key_idx      ON likes (key);
CREATE INDEX likes_username_idx ON likes (username);

• username — patron account username string (consistent with follows and bookshelves_books tables)
• key — full infogami key path (any type: works, authors, lists, series, tags, etc.)
• value — 1 = like, -1 = dislike. MVP may ignore -1, but the column keeps the door open for downvotes without a future schema migration.
• PRIMARY KEY (username, key) — enforces one like per patron per record at the DB level

Related files

• openlibrary/core/bookshelves.py — primary reference for the db.CommonExtras / web.py SQL pattern
• openlibrary/core/follows.py — simpler reference: insert / delete / select helpers without CommonExtras
• openlibrary/core/models.py — resolve_redirect_chain / resolve_redirects_bulk (see redirect/merge note below)
• openlibrary/core/db.py — get_db(), CommonExtras
• Refer to this map of common Endpoints for where to register new API routes

openlibrary/core/likes.py (new file)

Model the class closely on follows.py. Minimum interface:

class Likes:
    TABLENAME = "likes"
    PRIMARY_KEY = ("username", "key")

    @classmethod
    def like(cls, username: str, key: str, value: int = 1) -> None:
        """Insert or update a like. value must be 1 or -1."""
        ...

    @classmethod
    def unlike(cls, username: str, key: str) -> None:
        """Remove a like record."""
        ...

    @classmethod
    def get_count(cls, key: str) -> dict[str, int]:
        """Return {'likes': N, 'dislikes': N} for a given key."""
        ...

    @classmethod
    def get_for_patron(cls, username: str, limit: int = 50, offset: int = 0) -> list:
        """Return all likes by a patron, paginated."""
        ...

    @classmethod
    def patron_liked(cls, username: str, key: str) -> bool:
        """Return whether the patron has liked this key."""
        ...

API endpoints

Register in openlibrary/plugins/upstream/code.py (or a new openlibrary/plugins/upstream/likes.py):

Auth rule: a patron may only like/unlike as themselves. Verify web.ctx.site.get_user().key matches the requester before writing.

⚠️ The redirect / merge problem (important — out of scope here, must be tracked)

infogami records are merged and deleted over time. When /works/OL1W is merged into /works/OL2W, a redirect object (/type/redirect) is left at the old key.

The existing resolve_redirects_bulk process rewrites stale keys in bookshelves_books, ratings, and booknotes — but only for Works, and it operates on numeric work IDs extracted from the key (e.g. OL1234W → 1234).

likes introduces two new complications:

1. Any record type can be liked. The existing resolver only queries "/works/*" redirects. Authors (/authors/*), lists (/type/list/*), and other types have no equivalent resolver today.

2. Full key strings, not numeric IDs. likes.key stores /works/OL123W verbatim. The existing resolver extracts numeric IDs and calls update_work_id(old_id, new_id). A key-string-based table needs a different update pattern, and must guard against duplicates:

-- Rewrite stale key to canonical key, skipping patrons who already liked the canonical key
UPDATE likes SET key = $new_key, modified = NOW()
WHERE key = $old_key
  AND username NOT IN (SELECT username FROM likes WHERE key = $new_key);

-- Remove any remaining rows pointing at the old key (patron liked both old + new)
DELETE FROM likes WHERE key = $old_key;

The NOT IN guard is essential — without it, the UPDATE violates the primary key constraint when a patron liked both the old and canonical key.

For the initial implementation: store the full key as-is and add a # TODO comment in likes.py pointing to this gap. A follow-up issue should extend resolve_redirects_bulk (or introduce a generic key-rewrite pass) to cover likes and any future key-based tables.

Requirements Checklist

• likes table DDL added to migration / scripts/db_setup.py
• openlibrary/core/likes.py — Likes class with like, unlike, get_count, get_for_patron, patron_liked
• Auth enforcement: logged-in patron only; patron can only act as themselves
• POST /api/like endpoint
• DELETE /api/like endpoint
• GET /api/likes endpoint (count + patron_liked flag for authenticated users)
• Unit tests: like, unlike, double-like idempotency, value constraint, unauthenticated rejection
• # TODO comment in likes.py documenting the redirect/merge gap and pointing to resolve_redirects_bulk

Stakeholders

• Lead: @mekarpeles — sign-off required before merge

Out of scope for this issue

• Frontend UI (heart/thumbs button, like counts on record pages)
• Resolving stale likes.key values after merges/deletions (follow-up issue)
• Notifications or activity feeds based on likes
• Rate limiting

Instructions for Contributors

• Before creating a new branch or pushing up changes to a PR, please first run these commands to ensure your repository is up to date, as the pre-commit bot may add commits to your PRs upstream.
• Start Docker and verify the app loads (curl -s -o /dev/null -w "%{http_code}" http://localhost:8080 → 200) before opening a PR.
• Run docker compose run --rm home make test — 0 failures required.