Rate Translations

Rating UI

Goals

• Let users rate individual translations (1–5 stars) per verse × language × source.
• Collect overall verse feedback (free text) per user.
• Keep it fast (cached aggregates), fair (anti-abuse), and evolvable (new sources/languages).

Database Structure:

For setting up the translation rating system, we first need a flexible DB structure that can be used to store translations with an id, and then use that id to reference their rating value.

Translation Language

CREATE TABLE translation_language (
  id    SMALLSERIAL PRIMARY KEY,
  code  TEXT UNIQUE NOT NULL,        -- 'en', 'pa', etc.
  name  TEXT NOT NULL
);

Translation Source

-- Translation sources (not tied to a single language)
CREATE TABLE translation_source (
  id    SMALLSERIAL PRIMARY KEY,
  name  TEXT UNIQUE NOT NULL,        -- e.g., 'BaniDB', 'Sant Singh Khalsa'
  notes TEXT
);

Translation text

CREATE TABLE translation (
  id           BIGSERIAL PRIMARY KEY,
  verse_id     BIGINT      NOT NULL REFERENCES verse(id) ON DELETE CASCADE,
  language_id  SMALLINT    NOT NULL REFERENCES translation_language(id),
  source_id    SMALLINT    NOT NULL REFERENCES translation_source(id),
  content      TEXT        NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- one current translation per (verse, language, source)
  CONSTRAINT uq_translation UNIQUE (verse_id, language_id, source_id)
);

Translation Reaction

-- Reaction on specific translation, by user
CREATE TABLE translation_reaction (
  id              BIGSERIAL PRIMARY KEY,
  translation_id  BIGINT      NOT NULL REFERENCES translation(id) ON DELETE CASCADE,
  user_id         UUID        NOT NULL REFERENCES user(id) ON DELETE CASCADE,
  reaction        TEXT        NOT NULL,
  comment         TEXT,                     -- optional per-source comment
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT uq_rating_one_per_user UNIQUE (translation_id, user_id)
);

Overall Verse Feedback

-- Overall free-text comment on a verse’s translations (per user)
CREATE TABLE verse_feedback (
  id          BIGSERIAL PRIMARY KEY,
  verse_id    BIGINT      NOT NULL REFERENCES verse(id) ON DELETE CASCADE,
  user_id     UUID        NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  comment     TEXT        NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT uq_feedback_one_per_user_per_verse UNIQUE (verse_id, user_id)
);

API Design

GET    /api/verses/:verseId/translations?lang=en
GET    /api/translations/:id            # details + reactions
POST   /api/translations/:id/reactions  # {key: 'accurate'|'clear'|'contextual'}
DELETE /api/translations/:id/review
DELETE /api/translations/:id/reactions/:key