a postgres table with created, modified, deleted

timely_table.sql

-- https://stackoverflow.com/questions/9556474/how-do-i-automatically-update-a-timestamp-in-postgresql/9556527#9556527
-- https://dba.stackexchange.com/questions/9759/postgresql-multi-column-unique-constraint-and-null-values
-- this might be better as a view...
BEGIN;

	CREATE OR REPLACE FUNCTION set_modified_column()
	RETURNS TRIGGER AS $$
	BEGIN
		NEW.modified = now();
		RETURN NEW;
	END;
	$$ language 'plpgsql';

	CREATE TABLE timely_table (
		id bigserial PRIMARY KEY,
		data text,
		created timestamp NOT NULL DEFAULT now(),
		modified timestamp NOT NULL DEFAULT now(),
		deleted timestamp DEFAULT NULL
	);

	-- this essetially duplicates the PRIMARY KEY index
	-- we could just make id not a PRIMARY KEY
	-- but we need to keep (even deleted objects) uniquely identifyable
	-- by id or foreign key references into this table will be unecessarily
	-- complicated. FOREIGN KEY (timely_id, NULL) REFERENCES timely_table(id, deleted)
	CREATE UNIQUE INDEX
		ON timely_table(id)
	WHERE
		deleted IS NULL;

	CREATE TRIGGER set_updated
		BEFORE UPDATE ON timely_table
		FOR EACH ROW EXECUTE PROCEDURE set_modified_column();

COMMIT;

This pattern could be abstracted with a timify function that takes an existing table as a parameter