How to add metadata for every record in Azure postgres tables
I followed the below steps to add metadata for every record in Azure postgres tables, it Identify which records were migrated from legacy systems. Uses is_migrated = TRUE
for legacy data during the metadata initialization and also it Identify which records are newly created by users in this application by using sets is_migrated = FALSE
and created_by = 'user_app'
and logs the action to audit_log
with action = 'insert'
. So, this enables detection of user-inserted data. Also, Identify migrated records that were later updated by user activity by implementing a trigger track_user_updates_on_migrated_inventory
that sets this column to TRUE
, this gives a clear signal for user-modified migrated data.
Steps I followed:
CREATE TABLE inventory (item_id SERIAL PRIMARY KEY, item_name TEXT, stock INT );
INSERT INTO inventory (item_name, stock) VALUES ('Chair', 10), ('Desk', 7);
CREATE OR REPLACE FUNCTION add_metadata_to_inventory_table() RETURNS VOID AS $$ BEGIN DO $do$ BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'is_migrated') THEN ALTER TABLE inventory ADD COLUMN is_migrated BOOLEAN DEFAULT FALSE; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'was_updated_by_user') THEN ALTER TABLE inventory ADD COLUMN was_updated_by_user BOOLEAN DEFAULT FALSE; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'created_by') THEN ALTER TABLE inventory ADD COLUMN created_by TEXT; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'created_at') THEN ALTER TABLE inventory ADD COLUMN created_at TIMESTAMP DEFAULT NOW(); END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'updated_by') THEN ALTER TABLE inventory ADD COLUMN updated_by TEXT; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'inventory' AND column_name = 'updated_at') THEN ALTER TABLE inventory ADD COLUMN updated_at TIMESTAMP DEFAULT NOW(); END IF; END $do$;
UPDATE inventory SET is_migrated = TRUE, was_updated_by_user = FALSE, created_by = 'migration_script', created_at = NOW(), updated_by = 'migration_script', updated_at = NOW() WHERE created_by IS NULL; END; $$ LANGUAGE plpgsql;
SELECT add_metadata_to_inventory_table();
INSERT INTO inventory (item_name, stock, is_migrated, created_by, updated_by) VALUES ('Notebook', 20, FALSE, 'user_app', 'user_app'), ('Pen', 100, FALSE, 'user_app', 'user_app');
CREATE OR REPLACE FUNCTION track_user_updates_on_migrated_inventory() RETURNS TRIGGER AS $$ BEGIN IF OLD.is_migrated = TRUE AND NEW.updated_by = 'user_app' THEN NEW.was_updated_by_user := TRUE; END IF; NEW.updated_at := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_track_user_updates_on_inventory ON inventory; CREATE TRIGGER trg_track_user_updates_on_inventory BEFORE UPDATE ON inventory FOR EACH ROW EXECUTE FUNCTION track_user_updates_on_migrated_inventory();
UPDATE inventory SET stock = 11, updated_by = 'user_app' WHERE item_name = 'Chair';
CREATE TABLE IF NOT EXISTS audit_log ( log_id SERIAL PRIMARY KEY, table_name TEXT NOT NULL, record_id INT NOT NULL, action TEXT NOT NULL, performed_by TEXT NOT NULL, action_time TIMESTAMP DEFAULT NOW() );
CREATE OR REPLACE FUNCTION log_inventory_user_activity() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' AND NEW.created_by = 'user_app' THEN INSERT INTO audit_log (table_name, record_id, action, performed_by) VALUES ('inventory', NEW.item_id, 'insert', NEW.created_by); ELSIF TG_OP = 'UPDATE' AND OLD.is_migrated = TRUE AND NEW.updated_by = 'user_app' AND (OLD.item_name IS DISTINCT FROM NEW.item_name OR OLD.stock IS DISTINCT FROM NEW.stock OR OLD.updated_by IS DISTINCT FROM NEW.updated_by) THEN INSERT INTO audit_log (table_name, record_id, action, performed_by) VALUES ('inventory', NEW.item_id, 'update', NEW.updated_by); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_log_inventory_user_activity ON inventory; CREATE TRIGGER trg_log_inventory_user_activity AFTER INSERT OR UPDATE ON inventory FOR EACH ROW EXECUTE FUNCTION log_inventory_user_activity();
SELECT ROW_NUMBER() OVER (ORDER BY i.updated_at) AS edit_number, i.item_id, i.item_name, i.stock, i.is_migrated, i.was_updated_by_user, i.created_by, i.updated_by, CASE WHEN i.is_migrated = TRUE AND i.was_updated_by_user = TRUE THEN 'Migrated and Modified by User' WHEN i.is_migrated = FALSE THEN 'User Inserted' ELSE 'Migrated Only' END AS data_origin, COALESCE(a.table_name, 'inventory') AS table_name, COALESCE(a.action, 'none') AS action, COALESCE(a.performed_by, i.updated_by) AS audit_user, COALESCE(a.action_time, i.updated_at) AS action_time FROM inventory i LEFT JOIN audit_log a ON a.table_name = 'inventory' AND a.record_id = i.item_id AND a.performed_by = 'user_app' ORDER BY action_time;``
Output:


![]()