Schema proposal for richer inline review comment cache

Problem

Current read cache now correctly stores inline review comments as review_kind=inline with path and line. That is enough to show docs/mcp-setup.md:234, but it is not enough to reliably answer whether the comment is attached to an added line, removed line, renamed file, outdated diff version, or a specific hunk in the PR diff.

Proposal

erDiagram
  sources ||--o| pr_review_comments : "comment source_id"
  pr_review_comments }o--|| pr_review_discussions : "discussion_id"
  pr_review_comments }o--o| pr_review_positions : "comment_id"
  pr_review_positions }o--o| pr_diff_lines : "path + side + line"
  pull_requests ||--o{ pr_changed_files : "pr_number"
  pr_changed_files ||--o{ pr_diff_lines : "file_id"

  pr_review_comments {
    text repo_id
    text source_id
    int pr_number
    text comment_id
    text discussion_id
    text review_kind
    text path
    int line
    text resolved
  }

  pr_review_discussions {
    text repo_id
    int pr_number
    text discussion_id
    text kind
    text resolved
    text resolvable
    text first_comment_id
  }

  pr_review_positions {
    text repo_id
    int pr_number
    text comment_id
    text discussion_id
    text position_type
    text base_sha
    text start_sha
    text head_sha
    text old_path
    text new_path
    int old_line
    int new_line
    int start_old_line
    int start_new_line
    text line_code
    int patchset_iid
    int diff_id
    text version_sha
    text side
    text is_outdated
  }

  pr_changed_files {
    text repo_id
    int pr_number
    text file_id
    text old_path
    text new_path
    text old_blob_id
    text new_blob_id
    text status
    int additions
    int deletions
  }

  pr_diff_lines {
    text repo_id
    int pr_number
    text file_id
    text side
    int old_line
    int new_line
    text line_type
    text content
    text line_code
    int hunk_index
  }

Recommended schema changes

1. Add pr_review_discussions for thread-level state: discussion_id, kind, resolved, resolvable, and first_comment_id. Today this is reconstructed from comments every read.

2. Add pr_review_positions for the raw v4 position and original_position data: base_sha, start_sha, head_sha, old_path, new_path, old_line, new_line, patchset_iid, diff_id, version_sha, line_code, and derived side. This preserves enough data to distinguish new-line, old-line, range, renamed-file, and outdated comments.

3. Add pr_changed_files and pr_diff_lines as a separate PR diff cache. pr_changed_files is file-level metadata; pr_diff_lines is line-level hunk data with old_line, new_line, side, line_type, and content.

Benefits

• Read cache can answer whether a comment is truly inline, not just a PR note with a copied path.
• Agents can map a review comment to the exact changed line or hunk without live GitCode access.
• Rename/deletion/outdated cases become deterministic because old/new path, old/new line, and head/version sha are persisted.
• list_pr_discussions can expose richer context: changed line content, side, hunk index, outdated status, and current-vs-original position.
• Future automation can filter actionable comments: unresolved inline comments on current diff only, comments on deleted files, comments whose head sha no longer matches, etc.

Resync/backfill plan

Phase 1: discussion/position backfill

• Bump schema version.
• During BulkSyncPRComments, call the v4 discussions endpoint already used for inline creation: GET /api/v4/projects/{owner}%2F{repo}/merge_requests/{iid}/discussions.
• Continue staging pr_comment sources as today.
• Additionally upsert pr_review_discussions once per discussion and pr_review_positions once per note with a position or original_position.
• For existing caches, a normal sync --pulls --comments or targeted PR comment sync is enough to populate the new tables. No destructive migration is needed.

Phase 2: diff backfill

• Add a PR diff sync path using the v4/GitCode changed-files endpoint verified for the target route.
• Upsert pr_changed_files by stable file key: repo_id + pr_number + old_path + new_path or remote file id when GitCode provides one.
• Parse/store diff hunks into pr_diff_lines. Keep this deterministic and bounded by sync limits.
• Link comments to diff lines at read time by (repo_id, pr_number, new_path, new_line) for new-side comments and (repo_id, pr_number, old_path, old_line) for old-side comments.

Phase 3: read model upgrade

• Extend PRDiscussion output with optional position and optional diff_line context.
• Keep existing kind/path/line fields for compatibility.
• If diff data is missing, return the comment position and mark diff_line unavailable instead of requiring live network.

Implementation order

1. Schema v13: pr_review_discussions + pr_review_positions.
2. Decoder/store updates for v4 discussion positions.
3. Read API exposes position metadata.
4. Schema v14: pr_changed_files + pr_diff_lines.
5. Diff sync and read-time matching.

This keeps the current PR safe: issue #30 remains about creating real inline comments and reading them as inline. The diff-line cache can be a follow-up because it has a larger blast radius and should be tested independently.

Implemented Phase 1 in PR #25: https://gitcode.com/urandon/gitcode-mcp/merge_requests/25

Commit: fb0ebaf Persist PR review discussion positions

What changed:

• schema v13 adds pr_review_discussions and pr_review_positions;
• v4 GitCode discussion/note position metadata is cached as current/original positions;
• pr-discussions now returns discussion.position plus comment.positions[];
• sync and write paths both propagate the new metadata;
• create inline fallback stores request-derived current position when the POST response is sparse.

Benefit:

• read-cache can now treat GitCode inline comments as real diff notes, not only flattened path/line rows;
• agents can match an inline discussion to new_path/new_line or old_path/old_line plus base/start/head SHA, diff_id, patchset_iid, line_code when GitCode provides them;
• this gives a stable bridge for later matching against cached source-code changes/diff hunks.

Desync/backfill model:

• migration creates empty v13 tables without guessing old position metadata;
• normal PR comment sync or add-pr-review-comment writes populate pr_review_positions going forward;
• stale rows are refreshed because PR comment content_hash now includes Positions;
• old v12 cached comments need a resync to recover full v4 position metadata.

Validation:

• go test ./... passed;
• migrated /private/tmp/gitcode-mcp-issue-inline-comments.db from schema 12 to 13;
• live smoke created inline PR comment PRCOMMENT-25-177722859;
• pr-discussions returned discussion.position and comment.positions[];
• SQLite pr_review_positions contains current/original rows for comment 177722859.

Deferred follow-up:

• cache of PR changed files and diff hunks should be schema v14; that is the part needed for full source-code-change matching, beyond storing inline note positions.

Correction to the previous note: there should be no schema v14 for PR changed files or diff hunks.

The intended model is:

• v13 stores only the GitCode review-note anchor: discussion/comment ids, current/original position metadata, base/start/head SHA, paths, lines, line_code/diff_id/patchset_iid when GitCode provides them;
• source-code-change matching should be an ephemeral matcher over local git refs/objects, using those SHAs and paths/lines as anchors;
• do not duplicate PR changed files or diff hunks into the SQLite cache.

Docs were corrected in PR #25 commit 46710a1.

$Follow-up research after reopening #30 manually.\n\nDogfood summary, 2026-06-29:\n- Private Safari/Firefox only showed comments created through the v5 pull comments surface. Authenticated/non-private Safari also showed the earlier v4-created inline comment.\n- v5 body-only creates a normal timeline comment. v5 path+line only also stayed timeline/general. v5 with body + path + line + new_line + position created an inline review comment visible in both private and authenticated browser views.\n- A new CLI dogfood marker was created with the follow-up code path: [v5-final-cli-1], using the v5 inline payload on a testing PR.\n\nArchitecture decision:\n- Do not roll cache schema v13 back to v12. v13 should be treated as a normalized review-anchor cache, not as a v4-specific schema. It can store v5/list fields and request-derived positions from confirmed v5 writes.\n- Stop depending on v4 discussions for routine create/list/sync behavior. Keep v4 only as historical compatibility evidence unless a future, frontend-compatible API exposes richer position metadata.\n\nFollow-up PR direction:\n- Move add-pr-review-comment creation from v4 discussions to v5 pulls comments.\n- Confirm writes from returned note_id/id plus matching body, then synthesize inline metadata/position from request + PR base/head SHAs for cache-first matching.\n- Remove implicit v4 discussion enrichment from ListPRComments so sync is v5-first.