| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Initial migration: a generic key/value `meta` table used for |
| 4 | -- schema-version-friendly metadata, plus the canonical `tg_set_updated_at` |
| 5 | -- trigger function reused by every later table that has an `updated_at` |
| 6 | -- column. |
| 7 | -- |
| 8 | -- This is the only migration where we bootstrap the trigger; subsequent |
| 9 | -- migrations attach the trigger to their tables via: |
| 10 | -- |
| 11 | -- CREATE TRIGGER set_updated_at BEFORE UPDATE ON <table> |
| 12 | -- FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 13 | |
| 14 | -- +goose Up |
| 15 | -- +goose StatementBegin |
| 16 | CREATE OR REPLACE FUNCTION tg_set_updated_at() RETURNS trigger AS $$ |
| 17 | BEGIN |
| 18 | NEW.updated_at := now(); |
| 19 | RETURN NEW; |
| 20 | END; |
| 21 | $$ LANGUAGE plpgsql; |
| 22 | -- +goose StatementEnd |
| 23 | |
| 24 | CREATE TABLE meta ( |
| 25 | key text PRIMARY KEY, |
| 26 | value jsonb NOT NULL, |
| 27 | updated_at timestamptz NOT NULL DEFAULT now() |
| 28 | ); |
| 29 | |
| 30 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON meta |
| 31 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 32 | |
| 33 | INSERT INTO meta (key, value) VALUES |
| 34 | ('schema_version', '"0001"'::jsonb), |
| 35 | ('app', '"shithub"'::jsonb); |
| 36 | |
| 37 | -- +goose Down |
| 38 | DROP TABLE IF EXISTS meta; |
| 39 | DROP FUNCTION IF EXISTS tg_set_updated_at(); |
| 40 |