BEGIN;

	-- core data
	CREATE TABLE link (
		id TEXT NOT NULL PRIMARY KEY,
		url TEXT NOT NULL UNIQUE,
		created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		last_access TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
		accesses INTEGER NOT NULL DEFAULT 0,
		expires TIMESTAMP WITH TIME ZONE,
		auth_token TEXT
	);

	-- send notices to invalidate cacheable link data
	CREATE OR REPLACE FUNCTION cache_invalidation_link()
		RETURNS TRIGGER
		LANGUAGE plpgsql
		AS $$
		DECLARE
			payload varchar;
		BEGIN
			IF NEW.url != OLD.url OR NEW.expires != OLD.expires
			THEN
				payload = 'link|' || CAST(NEW.id AS text);
				PERFORM pg_notify('cache_invalidation', payload);
			END IF;
			RETURN NEW;
		END;
		$$
	;
	CREATE TRIGGER cache_invalidation_link
		AFTER UPDATE
		ON link
		FOR EACH ROW
			EXECUTE PROCEDURE cache_invalidation();

	-- better auth TBD
	CREATE TABLE auth (
		id TEXT NOT NULL PRIMARY KEY,
		secret TEXT NOT NULL,
		password TEXT
	);

	-- migration complete
	INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
COMMIT;
