How to add metadata for every record in Azure postgres tables

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: