2026-11-20T04:41:49+03:00
two-store consistency: injections on both sides, dry-run, safe repair, repeat, protection of fresh and used data

injections:
  S3 has an object that has no metadata row (fresh orphan)
  metadata exists but the object is gone: one attachment used by a message, one unused

$ scripts/s3tool.sh put chats/aaaaaaaa-0000-4000-8000-000000000000/bbbbbbbb-0000-4000-8000-000000000000 - < scripts/fixtures/sample.jpg
put chats/aaaaaaaa-0000-4000-8000-000000000000/bbbbbbbb-0000-4000-8000-000000000000 (653 bytes)
$ scripts/s3tool.sh delete chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1   # used
deleted chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1
$ scripts/s3tool.sh delete chats/d03b2c6b-67fd-4b5d-bc17-9e926440ff8d/c49ab2de-258a-4851-8ad2-f7f585fd3488   # unused
deleted chats/d03b2c6b-67fd-4b5d-bc17-9e926440ff8d/c49ab2de-258a-4851-8ad2-f7f585fd3488

$ scripts/reconcile.sh -grace 1h   # dry-run
{
  "prefix": "chats/",
  "grace_seconds": 3600,
  "dry_run": true,
  "scanned_objects": 19,
  "scanned_rows": 20,
  "truncated": false,
  "orphans": [],
  "missing": [
    {
      "id": "6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "object_key": "chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "chat_id": "47f62016-7525-4910-9823-0b21f2664b10",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": true
    },
    {
      "id": "c49ab2de-258a-4851-8ad2-f7f585fd3488",
      "object_key": "chats/d03b2c6b-67fd-4b5d-bc17-9e926440ff8d/c49ab2de-258a-4851-8ad2-f7f585fd3488",
      "chat_id": "d03b2c6b-67fd-4b5d-bc17-9e926440ff8d",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": false
    }
  ],
  "deleted_objects": 0,
  "deleted_metadata": 0,
  "retained_missing": 0,
  "delete_failures": 0,
  "s3_requests": 1,
  "db_queries": 2,
  "duration_ms": 8,
  "objects_per_second": 2375
}

$ scripts/reconcile.sh -grace 1h -dry-run=false   # apply: fresh object survives, safe metadata repair
{
  "prefix": "chats/",
  "grace_seconds": 3600,
  "dry_run": false,
  "scanned_objects": 19,
  "scanned_rows": 20,
  "truncated": false,
  "orphans": [],
  "missing": [
    {
      "id": "6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "object_key": "chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "chat_id": "47f62016-7525-4910-9823-0b21f2664b10",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": true
    },
    {
      "id": "c49ab2de-258a-4851-8ad2-f7f585fd3488",
      "object_key": "chats/d03b2c6b-67fd-4b5d-bc17-9e926440ff8d/c49ab2de-258a-4851-8ad2-f7f585fd3488",
      "chat_id": "d03b2c6b-67fd-4b5d-bc17-9e926440ff8d",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": false
    }
  ],
  "deleted_objects": 0,
  "deleted_metadata": 1,
  "retained_missing": 1,
  "delete_failures": 0,
  "s3_requests": 1,
  "db_queries": 3,
  "duration_ms": 12,
  "objects_per_second": 1583.3333333333333
}

$ scripts/s3tool.sh list chats/aaaaaaaa-0000-4000-8000-000000000000   # fresh orphan still present
chats/aaaaaaaa-0000-4000-8000-000000000000/bbbbbbbb-0000-4000-8000-000000000000	653	2026-11-20T01:41:49Z

$ scripts/reconcile.sh -grace 0s   # now eligible for deletion
{
  "prefix": "chats/",
  "grace_seconds": 0,
  "dry_run": true,
  "scanned_objects": 19,
  "scanned_rows": 19,
  "truncated": false,
  "orphans": [
    {
      "key": "chats/aaaaaaaa-0000-4000-8000-000000000000/bbbbbbbb-0000-4000-8000-000000000000",
      "size_bytes": 653,
      "last_modified": "2026-11-20T01:41:49Z",
      "age_seconds": 7
    }
  ],
  "missing": [
    {
      "id": "6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "object_key": "chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "chat_id": "47f62016-7525-4910-9823-0b21f2664b10",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": true
    }
  ],
  "deleted_objects": 0,
  "deleted_metadata": 0,
  "retained_missing": 0,
  "delete_failures": 0,
  "s3_requests": 1,
  "db_queries": 2,
  "duration_ms": 8,
  "objects_per_second": 2375
}

$ scripts/reconcile.sh -grace 0s -dry-run=false   # apply
{
  "prefix": "chats/",
  "grace_seconds": 0,
  "dry_run": false,
  "scanned_objects": 19,
  "scanned_rows": 19,
  "truncated": false,
  "orphans": [
    {
      "key": "chats/aaaaaaaa-0000-4000-8000-000000000000/bbbbbbbb-0000-4000-8000-000000000000",
      "size_bytes": 653,
      "last_modified": "2026-11-20T01:41:49Z",
      "age_seconds": 13
    }
  ],
  "missing": [
    {
      "id": "6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "object_key": "chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "chat_id": "47f62016-7525-4910-9823-0b21f2664b10",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": true
    }
  ],
  "deleted_objects": 1,
  "deleted_metadata": 0,
  "retained_missing": 1,
  "delete_failures": 0,
  "s3_requests": 2,
  "db_queries": 2,
  "duration_ms": 15,
  "objects_per_second": 1266.6666666666667
}

$ scripts/reconcile.sh -grace 0s -dry-run=false   # repeat: idempotent
{
  "prefix": "chats/",
  "grace_seconds": 0,
  "dry_run": false,
  "scanned_objects": 18,
  "scanned_rows": 19,
  "truncated": false,
  "orphans": [],
  "missing": [
    {
      "id": "6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "object_key": "chats/47f62016-7525-4910-9823-0b21f2664b10/6a9f0506-2126-497a-b8b4-89ac728fe5a1",
      "chat_id": "47f62016-7525-4910-9823-0b21f2664b10",
      "created_at": "2026-11-20T01:41:41Z",
      "referenced_by_message": true
    }
  ],
  "deleted_objects": 0,
  "deleted_metadata": 0,
  "retained_missing": 1,
  "delete_failures": 0,
  "s3_requests": 1,
  "db_queries": 2,
  "duration_ms": 9,
  "objects_per_second": 2000.0000000000002
}

used attachments remain untouched (rows and objects)
 used_attachments | ready 
------------------+-------
                3 |     3
(1 row)

retained referenced metadata row for the attachment whose object was deleted: 1
object of that referenced attachment is still absent: 0
