carto_version_control.sql

-- CREATE A STORED PROCEDURE AND TRIGGER TO KEEP A LOG OF CHANGES ON ANY CARTO TABLE
---- based on @andrewxhill's: https://gist.github.com/andrewxhill/0645add72c093f2f4908

--
--- Create the log table and cartodbfy it
CREATE TABLE version_control();
SELECT CDB_CartodbfyTable('username','version_control'); -- username parameter is only needed in organizational accounts

--
--- Add extra columns
ALTER TABLE version_control ADD COLUMN data json; -- to store the complete row in JSON format
ALTER TABLE version_control ADD COLUMN source_id integer; -- cartodb_id of the row
ALTER TABLE version_control ADD COLUMN table_name text; -- table name 
ALTER TABLE version_control ADD COLUMN tg_op text; -- writing operation that was performed
ALTER TABLE version_control ADD COLUMN logged_at timestamp; -- timestamp for the operation

--
--- Create the function itself
CREATE OR REPLACE FUNCTION carto_version_control() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at) 
              SELECT OLD.the_geom, 'DELETE', row_to_json(OLD), OLD.cartodb_id, TG_TABLE_NAME::text, now();
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at)
              SELECT NEW.the_geom, 'UPDATE', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now();
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at) 
              SELECT NEW.the_geom, 'INSERT', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now();
            RETURN NEW;
        END IF;
        RETURN NULL; 
    END;
$$ LANGUAGE plpgsql;

--
--- Add the trigger to any table you want to log
CREATE TRIGGER carto_version_trigger
AFTER UPDATE OR DELETE OR INSERT ON dummy_dataset -- Remember to use the proper table name
    FOR EACH ROW EXECUTE PROCEDURE version_control();

--
--- Explode json data in a row, filtering by any column in `version_control`

SELECT (json_populate_record(null::dummy_dataset, data)).* FROM version_control WHERE table_name LIKE 'dummy_dataset';