BEGIN;
CREATE TABLE guidance_documents(id text PRIMARY KEY CHECK(id ~ '^user-[a-z0-9-]{1,74}$'), owner_id uuid NOT NULL REFERENCES account_users(id), latest integer NOT NULL DEFAULT 0, deleted_at timestamptz, CHECK(id LIKE 'user-' || owner_id::text || '-%'));
CREATE TABLE guidance_revisions(document_id text NOT NULL REFERENCES guidance_documents(id), revision integer NOT NULL CHECK(revision>0), content jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(document_id,revision));
CREATE FUNCTION guidance_immutable() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'Published revisions are immutable'; END $$;
CREATE TRIGGER guidance_immutable BEFORE UPDATE OR DELETE ON guidance_revisions FOR EACH ROW EXECUTE FUNCTION guidance_immutable();
INSERT INTO schema_migrations(version) VALUES('015_guidance_documents');
COMMIT;