How to add metadata for every record in Azure postgres tables
Hi,
I have a situation in one of the applications I am working on where prior to going live in production, we are going to migrate data from legacy systems over the period of few weeks. And at the same time, there will be activity from users in this production application. i.e, new records generated and existing (migrated ) records updated from user activity.
I want to be able to identify what records have been migrated from legacy systems, what records are newly created by user activity in this application and what records were initially migrated but later on updated from user activity in this application.
I am exploring options to implement this . Any ideas are appreciated.
Thanks,
Azure Database for PostgreSQL
-
Sai Raghunadh M • 4,120 Reputation points • Microsoft External Staff • Moderator
2025-06-10T16:51:10.3933333+00:00 Hi @ Parag Kale
Thank you for reaching out with your question about tracking data origin and changes in your Azure PostgreSQL database during your phased migration.
To help you differentiate between migrated records, newly created user records, and migrated records that are later updated by users, here are some recommended approaches:
We suggest adding metadata columns to each relevant table:
ALTER TABLE your_table ADD COLUMN source VARCHAR(20), -- 'migration' or 'user' ADD COLUMN migration_status VARCHAR(30), -- 'migrated', 'user_created', 'migrated_updated_by_user' ADD COLUMN created_at TIMESTAMP DEFAULT now(), ADD COLUMN updated_at TIMESTAMP DEFAULT now();
These fields help track:
- How a record was created (source)
- Its lifecycle (migration_status)
- When it was created/updated
To automate the metadata updates, use PostgreSQL triggers that populate or update these fields on insert or update actions. This ensures consistent tracking without requiring manual updates in your application logic.
If a full change history is needed, you can implement an audit table that logs each change along with metadata. This gives full visibility into record modifications over time.
If you prefer flexibility, consider using a JSONB column to store metadata dynamically. This approach is schema-less and can evolve over time as your tracking needs grow.
Hope this helps. Do let us know if you any further queries.
-
Sai Raghunadh M • 4,120 Reputation points • Microsoft External Staff • Moderator
2025-06-11T17:20:09.4966667+00:00 Hi @ Parag Kale
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Parag Kale • 25 Reputation points
2025-06-11T20:07:28.51+00:00 Can you please explain a bit more on below
If a full change history is needed, you can implement an audit table that logs each change along with metadata. This gives full visibility into record modifications over time.
If you prefer flexibility, consider using a JSONB column to store metadata dynamically. This approach is schema-less and can evolve over time as your tracking needs grow.
-
Sai Raghunadh M • 4,120 Reputation points • Microsoft External Staff • Moderator
2025-06-11T20:26:26.77+00:00 Hi @ Parag Kale
Sure, here is the explanation for your ask:
Audit Table for Full Change History:
An audit table is a separate table that logs every change made to records in your main table. This allows you to track modifications over time with full visibility.
How It Works:
- Create an audit table with columns for the record ID, timestamp, operation type (INSERT, UPDATE, DELETE), and metadata.
- Use PostgreSQL triggers to automatically insert a new row into the audit table whenever a record is modified.
- This ensures that every change is recorded without requiring manual intervention.
Using JSONB for Flexible Metadata Storage:
If you need flexibility in storing metadata, a JSONB column allows you to store structured metadata without rigid schema constraints.
Advantages:
- Schema-less: You can store different metadata structures without modifying the table schema.
- Easily queryable: PostgreSQL provides powerful JSONB functions for filtering and extracting data.
- Evolves with your needs: You can add new metadata fields dynamically.
-
Sai Raghunadh M • 4,120 Reputation points • Microsoft External Staff • Moderator
2025-06-13T02:10:42.6133333+00:00 Hi @ Parag Kale
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Narendra Pakkirigari • 395 Reputation points • Microsoft External Staff • Moderator
2025-06-13T10:38:15.3666667+00:00 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 setsis_migrated = FALSE
andcreated_by = 'user_app'
and logs the action toaudit_log
withaction = 'insert'
. So, this enables detection of user-inserted data. Also, Identify migrated records that were later updated by user activity by implementing a triggertrack_user_updates_on_migrated_inventory
that sets this column toTRUE
, 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:
-
Narendra Pakkirigari • 395 Reputation points • Microsoft External Staff • Moderator
2025-06-16T00:56:50.5233333+00:00 Hi Parag Kale
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment