--- a/PostgreSQL/CMakeLists.txt	Wed Apr 23 17:39:48 2025 +0200
+++ b/PostgreSQL/CMakeLists.txt	Tue Jun 03 17:55:51 2025 +0200
@@ -94,7 +94,7 @@
   POSTGRESQL_UPGRADE_REV1_TO_REV2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql
   POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql
   POSTGRESQL_UPGRADE_REV3_TO_REV4    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev3ToRev4.sql
-  POSTGRESQL_UPGRADE_REV4_TO_REV5    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev4ToRev5.sql
+  POSTGRESQL_UPGRADE_REV4_TO_REV99   ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev4ToRev99.sql
   )
 
 

--- a/PostgreSQL/NEWS	Wed Apr 23 17:39:48 2025 +0200
+++ b/PostgreSQL/NEWS	Tue Jun 03 17:55:51 2025 +0200
@@ -13,6 +13,8 @@
 
 Maintenance:
 * Optimized the CreateInstance SQL query.
+* Internal: The PatientRecyclingOrder has been refactored and is now stored in
+  Patient metadata (18: IsProtected, 19: PatientRecyclingOrder)
 
 
 Release 7.2 (2025-02-27)

--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Apr 23 17:39:48 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Tue Jun 03 17:55:51 2025 +0200
@@ -49,7 +49,7 @@
   static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
-#define CURRENT_DB_REVISION 5
+#define CURRENT_DB_REVISION 99
 
 namespace OrthancDatabases
 {
@@ -242,10 +242,10 @@
             std::string query;
 
             Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV4_TO_REV5);
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV4_TO_REV99);
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
             hasAppliedAnUpgrade = true;
-            currentRevision = 5;
+            currentRevision = 99;
           }
 
           if (hasAppliedAnUpgrade)
@@ -783,6 +783,111 @@
     throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
   }
 
+  void PostgreSQLIndex::SetProtectedPatient(DatabaseManager& manager,
+                                            int64_t internalId, 
+                                            bool isProtected)
+  {
+    std::unique_ptr<DatabaseManager::CachedStatement> statement;
+    Dictionary args;
+
+    if (isProtected)
+    {
+      statement.reset(new DatabaseManager::CachedStatement(
+                        STATEMENT_FROM_HERE, manager,
+                        "SELECT ProtectPatient(${id})"));
+    }
+    else
+    {
+      statement.reset(new DatabaseManager::CachedStatement(
+                        STATEMENT_FROM_HERE, manager,
+                        "SELECT UnprotectPatient(${id})"));
+    }        
+
+    statement->SetParameterType("id", ValueType_Integer64);
+    args.SetIntegerValue("id", internalId);
+      
+    statement->Execute(args);
+  }
+
+  bool PostgreSQLIndex::IsProtectedPatient(DatabaseManager& manager,
+                                        int64_t internalId)
+  {
+    std::string value;
+    int64_t revision;
+    
+    if (LookupMetadata(value, revision, manager, internalId, 18)) // 18 = IsProtected
+    {
+      return value == "true";
+    }
+
+    return false;
+  }
+
+  bool PostgreSQLIndex::SelectPatientToRecycle(int64_t& internalId /*out*/,
+                                               DatabaseManager& manager)
+  {
+    DatabaseManager::CachedStatement statement(
+      STATEMENT_FROM_HERE, manager,
+      "SELECT r.internalId "
+      "FROM Resources r "
+      "JOIN Metadata m ON r.internalId = m.id AND m.type = 19 " // 19 = PatientRecyclingOrder
+      "WHERE r.resourceType = 0 "
+      "  AND NOT EXISTS "
+      "   (SELECT 1 FROM Metadata m "
+      "    WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') "  // 18 = IsProtected
+      " ORDER BY CAST(m.value AS INTEGER) ASC LIMIT 1;");
+    
+    statement.SetReadOnly(true);
+    statement.Execute();
+
+    if (statement.IsDone())
+    {
+      return false;
+    }
+    else
+    {
+      internalId = statement.ReadInteger64(0);
+      return true;
+    }
+  }
+
+    
+  bool PostgreSQLIndex::SelectPatientToRecycle(int64_t& internalId /*out*/,
+                                               DatabaseManager& manager,
+                                               int64_t patientIdToAvoid)
+  {
+    DatabaseManager::CachedStatement statement(
+      STATEMENT_FROM_HERE, manager,
+      "SELECT r.internalId "
+      "FROM Resources r "
+      "JOIN Metadata m ON r.internalId = m.id AND m.type = 19 " // 19 = PatientRecyclingOrder
+      "WHERE r.resourceType = 0 "
+      "  AND r.internalId != ${id} "
+      "  AND NOT EXISTS "
+      "   (SELECT 1 FROM Metadata m "
+      "    WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') "  // 18 = IsProtected
+      " ORDER BY CAST(m.value AS INTEGER) ASC LIMIT 1;");
+
+    statement.SetReadOnly(true);
+    statement.SetParameterType("id", ValueType_Integer64);
+
+    Dictionary args;
+    args.SetIntegerValue("id", patientIdToAvoid);
+
+    statement.Execute(args);
+
+    if (statement.IsDone())
+    {
+      return false;
+    }
+    else
+    {
+      internalId = statement.ReadInteger64(0);
+      return true;
+    }
+  }
+
+
   bool PostgreSQLIndex::HasPerformDbHousekeeping()
   {
     return !IsReadOnly(); // Don't start HK on ReadOnly databases !

--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Apr 23 17:39:48 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Tue Jun 03 17:55:51 2025 +0200
@@ -112,9 +112,24 @@
 
     virtual int64_t GetLastChangeIndex(DatabaseManager& manager) ORTHANC_OVERRIDE;
 
+    // This is now obsolete
     virtual void TagMostRecentPatient(DatabaseManager& manager,
                                       int64_t patient) ORTHANC_OVERRIDE;
 
+    virtual void SetProtectedPatient(DatabaseManager& manager,
+                                     int64_t internalId, 
+                                     bool isProtected) ORTHANC_OVERRIDE;
+
+    virtual bool IsProtectedPatient(DatabaseManager& manager,
+                                    int64_t internalId) ORTHANC_OVERRIDE;
+
+    virtual bool SelectPatientToRecycle(int64_t& internalId /*out*/,
+                                        DatabaseManager& manager) ORTHANC_OVERRIDE;
+    
+    virtual bool SelectPatientToRecycle(int64_t& internalId /*out*/,
+                                        DatabaseManager& manager,
+                                        int64_t patientIdToAvoid) ORTHANC_OVERRIDE;
+
     // New primitive since Orthanc 1.12.0
     virtual bool HasLabelsSupport() const ORTHANC_OVERRIDE
     {

--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev5ToRev4.sql	Wed Apr 23 17:39:48 2025 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,71 +0,0 @@
--- This file contains an SQL procedure to downgrade from schema Rev5 to Rev4 (version = 6).
-  -- It re-installs the old CreateInstance method
-
-
------------
-
-CREATE OR REPLACE FUNCTION CreateInstance(
-  IN patient_public_id TEXT,
-  IN study_public_id TEXT,
-  IN series_public_id TEXT,
-  IN instance_public_id TEXT,
-  OUT is_new_patient BIGINT,
-  OUT is_new_study BIGINT,
-  OUT is_new_series BIGINT,
-  OUT is_new_instance BIGINT,
-  OUT patient_internal_id BIGINT,
-  OUT study_internal_id BIGINT,
-  OUT series_internal_id BIGINT,
-  OUT instance_internal_id BIGINT) AS $body$
-
-BEGIN
-	is_new_patient := 1;
-	is_new_study := 1;
-	is_new_series := 1;
-	is_new_instance := 1;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_patient := 0;
-            SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
-    END;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_study := 0;
-            SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
-    END;
-
-	BEGIN
-	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_series := 0;
-            SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
-    END;
-
-  	BEGIN
-		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_instance := 0;
-            SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
-    END;
-
-    IF is_new_instance > 0 THEN
-        -- Move the patient to the end of the recycling order.
-        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
-    END IF;  
-END;
-$body$ LANGUAGE plpgsql;
-
-----------
-
--- set the global properties that actually documents the DB version, revision and some of the capabilities
--- modify only the ones that have changed
-DELETE FROM GlobalProperties WHERE property IN (4, 11);
-INSERT INTO GlobalProperties VALUES (4, 4); -- GlobalProperty_DatabasePatchLevel

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql	Tue Jun 03 17:55:51 2025 +0200
@@ -0,0 +1,149 @@
+-- This file contains an SQL procedure to downgrade from schema Rev99 to Rev4 (version = 6).
+
+
+-- Re-installs the old PatientRecycling
+-----------
+
+CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
+       seq BIGSERIAL NOT NULL PRIMARY KEY,
+       patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       CONSTRAINT UniquePatientId UNIQUE (patientId)
+       );
+
+CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
+
+DROP TRIGGER IF EXISTS PatientAdded ON Resources;
+
+CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
+    IN patient_id BIGINT,
+    IN is_update BIGINT
+    )
+RETURNS VOID AS $body$
+BEGIN
+    DECLARE
+        newSeq BIGINT;
+    BEGIN
+        IF is_update > 0 THEN
+            -- Note: Protected patients are not listed in this table !  So, they won't be updated
+            WITH deleted_rows AS (
+                DELETE FROM PatientRecyclingOrder
+                WHERE PatientRecyclingOrder.patientId = patient_id
+                RETURNING patientId
+            )
+            INSERT INTO PatientRecyclingOrder (patientId)
+            SELECT patientID FROM deleted_rows
+            WHERE EXISTS(SELECT 1 FROM deleted_rows);
+        ELSE
+            INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, patient_id);
+        END IF;
+    END;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION PatientAddedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
+  IF new.resourceType = 0 THEN
+    PERFORM PatientAddedOrUpdated(new.internalId, 0);
+  END IF;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS PatientAdded on Resources;
+CREATE TRIGGER PatientAdded
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE PatientAddedFunc();
+
+DROP FUNCTION IF EXISTS ProtectPatient(patient_id BIGINT);
+
+DROP FUNCTION IF EXISTS UnprotectPatient;
+
+-- repopulate the PatientRecyclingOrderTable
+WITH UnprotectedPatients AS (SELECT r.internalId
+  FROM Resources r
+  RIGHT JOIN Metadata m ON r.internalId = m.id AND m.type = 19  -- 19 = PatientRecyclingOrder
+  WHERE r.resourceType = 0
+    AND NOT EXISTS (SELECT 1 FROM Metadata m
+                    WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') -- 18 = IsProtected
+  ORDER BY CAST(m.value AS INTEGER) ASC)
+
+INSERT INTO PatientRecyclingOrder (patientId)
+SELECT internalId
+FROM UnprotectedPatients;
+
+DROP SEQUENCE IF EXISTS PatientRecyclingOrderSequence;
+
+-- remove the IsProtected and PatientRecyclingOrder metadata
+DELETE FROM Metadata WHERE type IN (18, 19);
+
+-- Re-installs the old CreateInstance method
+-----------
+
+CREATE OR REPLACE FUNCTION CreateInstance(
+  IN patient_public_id TEXT,
+  IN study_public_id TEXT,
+  IN series_public_id TEXT,
+  IN instance_public_id TEXT,
+  OUT is_new_patient BIGINT,
+  OUT is_new_study BIGINT,
+  OUT is_new_series BIGINT,
+  OUT is_new_instance BIGINT,
+  OUT patient_internal_id BIGINT,
+  OUT study_internal_id BIGINT,
+  OUT series_internal_id BIGINT,
+  OUT instance_internal_id BIGINT) AS $body$
+
+BEGIN
+	is_new_patient := 1;
+	is_new_study := 1;
+	is_new_series := 1;
+	is_new_instance := 1;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_patient := 0;
+            SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_study := 0;
+            SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+	BEGIN
+	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_series := 0;
+            SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+  	BEGIN
+		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_instance := 0;
+            SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+    IF is_new_instance > 0 THEN
+        -- Move the patient to the end of the recycling order.
+        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
+    END IF;  
+END;
+$body$ LANGUAGE plpgsql;
+
+----------
+
+-- set the global properties that actually documents the DB version, revision and some of the capabilities
+-- modify only the ones that have changed
+DELETE FROM GlobalProperties WHERE property IN (4, 11);
+INSERT INTO GlobalProperties VALUES (4, 4); -- GlobalProperty_DatabasePatchLevel

--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Apr 23 17:39:48 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Tue Jun 03 17:55:51 2025 +0200
@@ -76,12 +76,6 @@
        date VARCHAR(64)
        ); 
 
-CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       CONSTRAINT UniquePatientId UNIQUE (patientId)
-       );
-
 CREATE TABLE IF NOT EXISTS Labels(
         id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
         label TEXT, 
@@ -126,7 +120,6 @@
 
 CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
 CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
-CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
 
 CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
@@ -158,62 +151,24 @@
 END $body$;
 
 
-------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
-DROP TRIGGER IF EXISTS PatientAdded ON Resources;
-
+--------------------- PatientRecyclingOrder -------------------
+-- from rev 99, we always maintain a PatientRecyclingOrder metadata, no matter if the patient is protected or not
 CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
-    IN patient_id BIGINT,
-    IN is_update BIGINT
+    IN patient_id BIGINT
     )
 RETURNS VOID AS $body$
 BEGIN
     DECLARE
         newSeq BIGINT;
     BEGIN
-        IF is_update > 0 THEN
-            -- Note: Protected patients are not listed in this table !  So, they won't be updated
-            WITH deleted_rows AS (
-                DELETE FROM PatientRecyclingOrder
-                WHERE PatientRecyclingOrder.patientId = patient_id
-                RETURNING patientId
-            )
-            INSERT INTO PatientRecyclingOrder (patientId)
-            SELECT patientID FROM deleted_rows
-            WHERE EXISTS(SELECT 1 FROM deleted_rows);
-        ELSE
-            INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, patient_id);
-        END IF;
+        INSERT INTO Metadata (id, type, value, revision)
+        VALUES (patient_id, 19, nextval('PatientRecyclingOrderSequence')::TEXT, 0)
+        ON CONFLICT (id, type)
+        DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
     END;
 END;
 $body$ LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION PatientAddedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
-  IF new.resourceType = 0 THEN
-    PERFORM PatientAddedOrUpdated(new.internalId, 0);
-  END IF;
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-DROP TRIGGER IF EXISTS PatientAdded on Resources;
-CREATE TRIGGER PatientAdded
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE PatientAddedFunc();
-
--- initial population of 
-SELECT setval('patientrecyclingorder_seq_seq', MAX(seq)) FROM PatientRecyclingOrder;
-DELETE FROM GlobalIntegers WHERE key = 7;
-        -- UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq;
-
--- INSERT INTO GlobalIntegers
---     SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder
---     ON CONFLICT DO NOTHING;
-
-
 ------------------- ResourceDeleted trigger -------------------
 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
 
@@ -534,7 +489,8 @@
   OUT instance_internal_id BIGINT) AS $body$
 
 BEGIN
-	-- assume the parents already exists
+	-- Assume the parent series already exists to minimize exceptions.  
+    -- Most of the instances are not the first of their series - especially when we need high performances.
 
 	is_new_patient := 1;
 	is_new_study := 1;
@@ -601,10 +557,10 @@
 	END IF;
 
 
-	-- IF is_new_instance > 0 THEN
-	-- 	-- Move the patient to the end of the recycling order.
-	-- 	PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
-	-- END IF;
+	IF is_new_instance > 0 THEN
+		-- Move the patient to the end of the recycling order.
+		PERFORM PatientAddedOrUpdated(patient_internal_id);
+	END IF;
 END;
 $body$ LANGUAGE plpgsql;
 
@@ -748,11 +704,39 @@
 EXECUTE PROCEDURE UpdateChildCount();
 
 
+-- new in rev 99
+
+CREATE SEQUENCE IF NOT EXISTS PatientRecyclingOrderSequence INCREMENT 1 START 1;
+
+CREATE OR REPLACE FUNCTION ProtectPatient(patient_id BIGINT)
+RETURNS VOID AS $$
+BEGIN
+    INSERT INTO Metadata (id, type, value, revision) -- 18 = IsProtected
+    VALUES (patient_id, 18, 'true', 0)
+    ON CONFLICT (id, type)
+    DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
+END;
+$$ LANGUAGE plpgsql;
+
+-- remove IsProtected and update PatientRecyclingOrder
+CREATE OR REPLACE FUNCTION UnprotectPatient(patient_id BIGINT)
+RETURNS VOID AS $$
+BEGIN
+    DELETE FROM Metadata WHERE id = patient_id AND type = 18; -- 18 = IsProtected
+
+    INSERT INTO Metadata (id, type, value, revision)
+    VALUES (patient_id, 19, nextval('PatientRecyclingOrderSequence')::TEXT, 0)
+    ON CONFLICT (id, type)
+    DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
+END;
+$$ LANGUAGE plpgsql;
+
+
 
 -- set the global properties that actually documents the DB version, revision and some of the capabilities
 DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14);
 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
-INSERT INTO GlobalProperties VALUES (4, 5); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (4, 99); -- GlobalProperty_DatabasePatchLevel
 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
 INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance  -- this is actually the 3rd version of HasCreateInstance

--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev5.sql	Wed Apr 23 17:39:48 2025 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,2 +0,0 @@
--- everything is performed in PrepareIndex.sql
-SELECT 1;
\ No newline at end of file

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql	Tue Jun 03 17:55:51 2025 +0200
@@ -0,0 +1,47 @@
+CREATE SEQUENCE IF NOT EXISTS PatientRecyclingOrderSequence INCREMENT 1 START 1;
+
+-- the protection mechanisms changed in rev 99.  We now use a metadata (18: IsProtected)
+-- while, in the past, patients where protected by not appearing in the PatientRecyclingOrder
+
+-- Step 1: Identify all patients that are not in PatientRecyclingOrder (those are the protected patients)
+-- The "0" corresponds to "OrthancPluginResourceType_Patient"
+WITH ProtectedPatients AS (
+    SELECT r.internalId AS internalId
+    FROM Resources r
+    LEFT JOIN PatientRecyclingOrder pro ON r.internalId = pro.patientId
+    WHERE r.resourceType = 0
+    AND pro.patientId IS NULL
+) 
+, UnprotectedPatients AS (
+    SELECT patientId AS internalId
+    FROM PatientRecyclingOrder
+    ORDER BY seq ASC
+)
+
+-- Step 2: Prepare the data for both metadata types
+, MetadataToInsert AS (
+    -- mark protected patient in 18: IsProtected
+    SELECT internalId, 18 AS metadataType, 'true' AS metadataValue
+    FROM ProtectedPatients    
+
+    UNION ALL
+
+    -- copy previous recycling order in 19: RecyclingOrder
+    SELECT internalId, 19 AS metadataType, nextval('PatientRecyclingOrderSequence')::TEXT AS metadataValue
+    FROM UnprotectedPatients    
+)
+
+-- Step 3: Insert the Metadata (since the metadata are new, there should not be any conflicts)
+INSERT INTO Metadata (id, type, value, revision)
+SELECT internalId, metadataType, metadataValue, 0
+FROM MetadataToInsert
+ON CONFLICT (id, type)
+DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
+
+-- The PatientRecyclingOrder table can now be removed
+
+DROP TABLE PatientRecyclingOrder;
+
+DROP TRIGGER IF EXISTS PatientAdded on Resources;
+DROP FUNCTION IF EXISTS PatientAddedFunc;
+DROP FUNCTION IF EXISTS PatientAddedOrUpdated;
\ No newline at end of file