Stitch any CI into Tangled
1package main
2
3// Schema definition and migration logic for the SQLite store. Pulled out
4// of store.go so the big SQL block doesn't sit in the middle of the
5// runtime API surface.
6
7import (
8 "context"
9 "fmt"
10)
11
12// schema is the full set of CREATE statements applied at startup. It is
13// idempotent and additive only — no `DROP`s — so future changes can be
14// layered on as additional statements without needing a separate
15// migration tool until the project actually outgrows that.
16const schema = `
17CREATE TABLE IF NOT EXISTS meta (
18 key TEXT PRIMARY KEY,
19 value TEXT NOT NULL
20);
21
22-- Records of sh.tangled.spindle.member. The owner of a spindle publishes
23-- one of these per authorized member. (did, rkey) is the natural ATProto
24-- key — did identifies the publisher's PDS, rkey identifies the record
25-- within that PDS's collection.
26CREATE TABLE IF NOT EXISTS spindle_members (
27 did TEXT NOT NULL,
28 rkey TEXT NOT NULL,
29 instance TEXT NOT NULL,
30 subject TEXT NOT NULL,
31 created_at TEXT NOT NULL,
32 PRIMARY KEY (did, rkey)
33);
34
35-- Records of sh.tangled.repo. We keep the full set so that when a
36-- pipeline trigger arrives we can look up which knot/spindle/repo_did
37-- it corresponds to without another round-trip.
38CREATE TABLE IF NOT EXISTS repos (
39 did TEXT NOT NULL,
40 rkey TEXT NOT NULL,
41 knot TEXT NOT NULL,
42 name TEXT NOT NULL,
43 spindle TEXT,
44 repo_did TEXT,
45 created_at TEXT NOT NULL,
46 PRIMARY KEY (did, rkey)
47);
48
49-- Records of sh.tangled.repo.collaborator. Used together with repos to
50-- decide whether a triggering DID is allowed to push builds to us.
51CREATE TABLE IF NOT EXISTS repo_collaborators (
52 did TEXT NOT NULL,
53 rkey TEXT NOT NULL,
54 repo TEXT,
55 repo_did TEXT,
56 subject TEXT NOT NULL,
57 created_at TEXT NOT NULL,
58 PRIMARY KEY (did, rkey)
59);
60
61-- Outbound event log. Each row is one record we want to fan out to
62-- connected /events websocket subscribers (typically the Tangled
63-- appview) — today only sh.tangled.pipeline.status.
64--
65-- We persist instead of pushing through an in-memory channel so that
66-- (a) a reconnecting subscriber can resume from a cursor without
67-- missing events that happened during the gap, and
68-- (b) slow subscribers can't make us drop events for fast ones — they
69-- simply lag behind in the rowid space.
70--
71-- AUTOINCREMENT (vs plain INTEGER PRIMARY KEY) guarantees rowids
72-- strictly increase and never get reused if a row is ever deleted, so
73-- treating the created column as a monotonic cursor is safe forever.
74CREATE TABLE IF NOT EXISTS events (
75 created INTEGER PRIMARY KEY AUTOINCREMENT,
76 rkey TEXT NOT NULL,
77 nsid TEXT NOT NULL,
78 event_json TEXT NOT NULL,
79 inserted_at TEXT NOT NULL
80);
81
82-- Mapping from a Buildkite build back to the Tangled pipeline that
83-- spawned it. The Buildkite webhook receiver only knows the build
84-- UUID; everything we need to publish a pipeline.status record
85-- (knot, pipeline rkey, workflow name, full pipeline ATURI) lives
86-- on this row.
87--
88-- pipeline_uri is denormalized off (knot, pipeline_rkey) so the
89-- webhook handler doesn't have to recompute the at:// string on
90-- every event — it's a constant for the lifetime of the build and
91-- the webhook is the hot path for status fan-out.
92--
93-- The (knot, pipeline_rkey, workflow) index supports the /logs
94-- handler, which only knows that tuple at request time.
95CREATE TABLE IF NOT EXISTS buildkite_builds (
96 build_uuid TEXT PRIMARY KEY,
97 build_number INTEGER NOT NULL,
98 pipeline_slug TEXT NOT NULL,
99 knot TEXT NOT NULL,
100 pipeline_rkey TEXT NOT NULL,
101 workflow TEXT NOT NULL,
102 pipeline_uri TEXT NOT NULL,
103 created_at TEXT NOT NULL
104);
105CREATE INDEX IF NOT EXISTS buildkite_builds_lookup
106 ON buildkite_builds (knot, pipeline_rkey, workflow);
107`
108
109// migrate applies the schema. Safe to call repeatedly.
110func (s *store) migrate(ctx context.Context) error {
111 if _, err := s.db.ExecContext(ctx, schema); err != nil {
112 return fmt.Errorf("apply schema: %w", err)
113 }
114 return nil
115}