flaxo/ghost

public

Source code of Ghost - On Ghost

add issues: rows, labels, assignees, comments, timeline, search119a0a6a month ago105 lines · 5.1 KB

1	# 0017 — Issues are rows, not branches
2
3	Status: adopted
4
5	## Decision
6
7	An issue is a database row naming one repository. It stores no git state of its
8	own: no ref, no commit, no tree. Everything it needs — title, markdown body,
9	open/closed state, labels, assignees, comments — is relational, so unlike
10	[0016](0016-a-pull-request-spans-two-logs.md)
11	no object store to lend, and no cache to keep coherent.
12
13	Six tables carry the feature:
14
15	```
16	issue the issue itself; `number` is per repository, 1-based
17	issue_comment timeline comments, oldest first
18	label repository-scoped name + description + 6-char hex color
19	issue_label issue ↔ label, composite primary key
20	issue_assignee issue ↔ user, composite primary key
21	issue_event opened/closed/reopened/renamed/edited/labeled/unlabeled/
22	assigned/unassigned, oldest first
23	```
24
25	`issue_event` is what GitHub renders between comments. Only the columns relevant
26	to `type` are set — `labelName` for labeled/unlabeled, `
27	assigned/unassigned, `oldTitle`/`newTitle` for renamed. The timeline endpoint
28	returns comments and events interleaved oldest-first, exactly as rendered.
29
30	## The problem this exists to solve
31
32	Pull requests are diffs with a conversation attached. Issues are the
33	conversation without the diff: a bug report, a task, an idea. They need the same
34	social machinery — numbers, states, comments, labels, assignees, search — but
35	none of the git machinery. Modeling them as rows keeps that split explicit: the
36	write-ahead log never learns about issues, and issues never touch the log.
37
38	## What the API exposes
39
40	```
41	POST /api/repositories/:username/:repo/issues
42	GET /api/repositories/:username/:repo/issues
43	GET /api/repositories/:username/:repo/issues/:number
44	PATCH /api/repositories/:username/:repo/issues/:number
45	POST /api/repositories/:username/:repo/issues/:number/close
46	POST /api/repositories/:username/:repo/issues/:number/reopen
47	GET /api/repositories/:username/:repo/issues/:number/comments
48	POST /api/repositories/:username/:repo/issues/:number/comments
49	PATCH /api/repositories/:username/:repo/issues/:number/comments/:commentId
50	DELETE /api/repositories/:username/:repo/issues/:number/comments/:commentId
51	GET /api/repositories/:username/:repo/issues/:number/timeline
52	PUT /api/repositories/:username/:repo/issues/:number/labels
53	PUT /api/repositories/:username/:repo/issues/:number/assignees
54	GET /api/repositories/:username/:repo/labels
55	POST /api/repositories/:username/:repo/labels
56	PATCH /api/repositories/:username/:repo/labels/:labelId
57	DELETE /api/repositories/:username/:repo/labels/:labelId
58	```
59
60	The list endpoint filters by `state`, full-text `q` over title and body,
61	`author`, `assignee`, and `labels
62	must carry all of them), and sorts by `created`, `updated`, or `
63	either direction. It answers `total` plus `openCount`/`
64	same filters minus `state`, which is what the Open/Closed tabs render.
65
66	Authorization follows the pull-request rule. Reading or opening an issue, or
67	commenting on one, requires read on the repository — the same bar GitHub sets
68	for a public repository. Editing, closing, labeling, or assigning requires the
69	author or write on the repository. Managing the label list itself requires
70	write.
71
72	`number` is allocated per repository with
73	`select coalesce(max(number), 0) + 1`, with the same unique-index retry as pull
74	requests: two concurrent opens read the same max, the loser is rejected, and
75	the retry reads the winner's number.
76
77	## Why a denormalized comment count
78
79	`sort=comments` needs an orderable column. Counting comments per issue at list
80	time is a join plus group-by on every call; storing `commentCount` on the issue
81	makes it one indexed query. Writers bump it on comment create and decrement
82	(floored at zero) on delete, which is the only place it can drift, and the only
83	cost of drift is sort order — never correctness of the comments themselves.
84
85	## Why label filtering is two queries
86
87	An AND over N labels is a group-by-having over the join table, which returns
88	issue ids, not issues. Running that first and then a single indexed scan with
89	`inArray` keeps the main query shaped like every other list endpoint — one scan
90	plus keyset pagination — instead of a bespoke join whose plan changes with the
91	number of labels. The intermediate id set is bounded by the repository, and an
92	empty set short-circuits before the main query runs.
93
94	## Consequences
95
96	- Issues cost no git processes at all. Every endpoint is Postgres plus
97	authorization.
98	- Labels belong to the repository, not the issue, so renaming a label renames
99	it everywhere it is used, and deleting a label unlabels every issue carrying
100	it through the foreign key.
101	- Events are write-only history. There is no endpoint to edit or delete one,
102	so the timeline can only grow, and "who did what" survives title edits and
103	label deletions.
104	- `commentCount` can drift under concurrent comment create/delete. The drift
105	window is one statement wide and self-heals on the next write to that issue.