| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Smoke checks for the restore drill. Each \echo line is the human |
| 4 | -- name of the check. We use psql's ON_ERROR_STOP=1 so any failed |
| 5 | -- assertion exits non-zero and the drill reports failure. |
| 6 | -- |
| 7 | -- Keep these checks coarse: count tables, sanity-check primary |
| 8 | -- foreign-key relationships, ensure the migration tracking table |
| 9 | -- exists. Don't compare counts to fixed numbers — counts grow over |
| 10 | -- time. Compare to internal invariants instead. Don't assert that |
| 11 | -- non-system tables are non-empty (that breaks day-one drills). |
| 12 | |
| 13 | \echo === goose_db_version exists and has rows === |
| 14 | SELECT 1 / (CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END) AS ok |
| 15 | FROM goose_db_version; |
| 16 | |
| 17 | \echo === users table exists (the only table guaranteed non-empty) === |
| 18 | SELECT 1 / (CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END) FROM users; |
| 19 | |
| 20 | \echo === repos table exists (may legitimately be empty on a fresh instance) === |
| 21 | SELECT COUNT(*) FROM repos; |
| 22 | |
| 23 | \echo === every repo has a real owner === |
| 24 | -- A repo's owner is exactly one of {owner_user_id, owner_org_id}; the |
| 25 | -- other is NULL. Both being NULL or both being non-NULL is corruption. |
| 26 | SELECT 1 / (CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END) AS orphan_repos |
| 27 | FROM repos r |
| 28 | LEFT JOIN users u ON u.id = r.owner_user_id |
| 29 | LEFT JOIN orgs o ON o.id = r.owner_org_id |
| 30 | WHERE (r.owner_user_id IS NULL AND r.owner_org_id IS NULL) |
| 31 | OR (r.owner_user_id IS NOT NULL AND u.id IS NULL) |
| 32 | OR (r.owner_org_id IS NOT NULL AND o.id IS NULL); |
| 33 | |
| 34 | \echo === every push_event references a real repo === |
| 35 | SELECT 1 / (CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END) AS orphan_push_events |
| 36 | FROM push_events pe |
| 37 | LEFT JOIN repos r ON r.id = pe.repo_id |
| 38 | WHERE r.id IS NULL; |
| 39 | |
| 40 | \echo === every issue belongs to a repo === |
| 41 | SELECT 1 / (CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END) AS orphan_issues |
| 42 | FROM issues i |
| 43 | LEFT JOIN repos r ON r.id = i.repo_id |
| 44 | WHERE r.id IS NULL; |
| 45 | |
| 46 | \echo === auth_audit_log columns intact === |
| 47 | SELECT actor_id, action, target_type, created_at FROM auth_audit_log LIMIT 1; |
| 48 | |
| 49 | \echo === migrations applied through latest known === |
| 50 | SELECT version_id FROM goose_db_version ORDER BY id DESC LIMIT 1; |
| 51 |