g1t/services/work/migrations/0025_requested_by.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| g1t is the stored author of what it opens; the person who asked is requested_by and keeps the author's rights | 1 | -- g1t is the author of the pull requests it makes and of the issues its |
| 2 | -- agent files while at work. The person who asked for the work is kept | |
| 3 | -- beside it as requested_by, and answers for it as its author would: | |
| 4 | -- every rule that read the author reads COALESCE(requested_by, author) | |
| 5 | -- now (Pull::owner in the contracts). | |
| 6 | ALTER TABLE pulls ADD COLUMN requested_by_id TEXT; | |
| 7 | ALTER TABLE pulls ADD COLUMN requested_by_name TEXT; | |
| 8 | ALTER TABLE issues ADD COLUMN requested_by_id TEXT; | |
| 9 | ALTER TABLE issues ADD COLUMN requested_by_name TEXT; | |
| 10 | ||
| 11 | -- A person's own work, and the work g1t made for them, for their lists: | |
| 12 | -- what they are working on, and their profile. | |
| 13 | CREATE INDEX IF NOT EXISTS pulls_by_owner ON pulls (COALESCE(requested_by_id, author_id), status); | |
| 14 | CREATE INDEX IF NOT EXISTS issues_by_owner ON issues (COALESCE(requested_by_id, author_id), created_at); | |
| 15 | ||
| 16 | -- The pull requests g1t made until now, stored as the person who asked. | |
| 17 | -- What marks one is what made_by_g1t reads: g1t's agent, hosted, in a | |
| 18 | -- fork of its own. Work g1t started itself is stored as g1t already | |
| 19 | -- (its id, or an older one) and nobody asked for it: it stays as it is. | |
| 20 | UPDATE pulls | |
| 21 | SET requested_by_id = author_id, | |
| 22 | requested_by_name = author_name, | |
| 23 | author_id = 'usr_g1t_agent', | |
| 24 | author_name = 'g1t' | |
| 25 | WHERE agent = 'g1t' | |
| 26 | AND runtime = 'hosted' | |
| 27 | AND fork_namespace IS NOT NULL | |
| 28 | AND fork_name IS NOT NULL | |
| 29 | AND requested_by_id IS NULL | |
| 30 | AND author_id NOT IN ('usr_g1t_agent', 'g1t', 'g1t_policy', 'svc_runner', 'g1t_runner'); | |
| 31 | ||
| 32 | -- The issues g1t's agent filed are stored as g1t already; who it was | |
| 33 | -- working for was not kept. It is the person who asked for the pull | |
| 34 | -- request whose run was going in that workspace when the issue was | |
| 35 | -- filed, where exactly one person's was. Where none or several were, | |
| 36 | -- nobody is named rather than the wrong person. | |
| 37 | UPDATE issues | |
| 38 | SET requested_by_id = ( | |
| 39 | SELECT p.requested_by_id | |
| 40 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id | |
| 41 | WHERE p.requested_by_id IS NOT NULL | |
| 42 | AND (r.repo_id = issues.repo_id | |
| 43 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) | |
| 44 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at | |
| 45 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at | |
| 46 | LIMIT 1), | |
| 47 | requested_by_name = ( | |
| 48 | SELECT p.requested_by_name | |
| 49 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id | |
| 50 | WHERE p.requested_by_id IS NOT NULL | |
| 51 | AND (r.repo_id = issues.repo_id | |
| 52 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) | |
| 53 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at | |
| 54 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at | |
| 55 | LIMIT 1) | |
| 56 | WHERE author_id = 'usr_g1t_agent' | |
| 57 | AND requested_by_id IS NULL | |
| 58 | AND ( | |
| 59 | SELECT count(DISTINCT p.requested_by_id) | |
| 60 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id | |
| 61 | WHERE p.requested_by_id IS NOT NULL | |
| 62 | AND (r.repo_id = issues.repo_id | |
| 63 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) | |
| 64 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at | |
| 65 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at | |
| 66 | ) = 1; |