Below you may find the script to clean up all the instances with more than one non-deleted quote. This script defaults to soft-delete all but the latest quote created for an instance.

BEGIN;

CREATE TEMP TABLE quotes_to_soft_delete ON COMMIT DROP AS (
    WITH duplicate_quotes_instances AS (
        SELECT "instanceId", count(*) FROM quotes
        WHERE "deletedAt" IS NULL
        GROUP BY "instanceId"
        HAVING count(*) > 1
    ), duplicate_quotes AS (
        SELECT * FROM quotes
        WHERE "deletedAt" IS NULL AND "instanceId" IN (SELECT "instanceId" FROM duplicate_quotes_instances)
    ), max_id_duplicate_quotes AS (
        SELECT max(id) as max_id, "instanceId" FROM quotes
        WHERE "id" IN (SELECT id FROM duplicate_quotes) AND "deletedAt" IS NULL
        GROUP BY "instanceId"
    ), quotes_to_soft_delete AS (
        SELECT * FROM duplicate_quotes
        WHERE id NOT IN (SELECT max_id FROM max_id_duplicate_quotes)
    )
    SELECT * FROM quotes_to_soft_delete -- c.name,
    -- JOIN instances i ON i.id = quotes_to_soft_delete."instanceId"
    -- JOIN assistants a ON a.id = i."assistantId"
    -- JOIN companies c ON c.id = a."companyId"
    -- WHERE c.name <> 'Invisible'
    ORDER BY "instanceId" DESC
);

UPDATE quotes
SET "deletedAt" = NOW(), "updatedAt" = NOW()
WHERE id IN (SELECT id FROM quotes_to_soft_delete);

UPDATE "lineItems"
SET "deletedAt" = NOW(), "updatedAt" = NOW()
WHERE "quoteId" IN (SELECT id FROM quotes_to_soft_delete);

UPDATE "billingEvents"
SET "deletedAt" = NOW(), "updatedAt" = NOW()
WHERE "quoteId" IN (SELECT id FROM quotes_to_soft_delete);

-- TODO: soft-delete billingCases from "lineItems"

COMMIT;