max / makenotwork

1.1 KB · 31 lines History Blame Raw

1	-- Add slug to sync_apps for public URL-friendly identifiers
2	ALTER TABLE sync_apps ADD COLUMN slug TEXT;
3	CREATE UNIQUE INDEX idx_sync_apps_slug ON sync_apps(slug) WHERE slug IS NOT NULL;
4
5	-- OTA releases table
6	CREATE TABLE ota_releases (
7	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
8	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
9	version TEXT NOT NULL,
10	notes TEXT NOT NULL DEFAULT '',
11	signature TEXT NOT NULL DEFAULT '',
12	pub_date TIMESTAMPTZ NOT NULL DEFAULT now(),
13	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
14	UNIQUE(app_id, version)
15	);
16
17	-- OTA artifacts (one per target/arch combo per release)
18	CREATE TABLE ota_artifacts (
19	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
20	release_id UUID NOT NULL REFERENCES ota_releases(id) ON DELETE CASCADE,
21	target TEXT NOT NULL,
22	arch TEXT NOT NULL,
23	s3_key TEXT NOT NULL,
24	file_size BIGINT NOT NULL DEFAULT 0,
25	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
26	UNIQUE(release_id, target, arch)
27	);
28
29	CREATE INDEX idx_ota_releases_app_id ON ota_releases(app_id);
30	CREATE INDEX idx_ota_artifacts_release_id ON ota_artifacts(release_id);
31