Competing edits silently revert each other, and approving one leaves its siblings approvable

Every resource_edits row is a full snapshot of the resource at submission time, and approving one does nothing to the others queued against the same resource.

Case A — approving an old edit reverts a newer one

Alice fixes the name on Monday. Bob fixes the guidelines on Tuesday. Different fields, unrelated edits.

-- live: name='Grace Cafe Philly', guidelines='Bring your own container.'
insert into public.resource_edits (mapped_resource, submitted_by, name, guidelines) values
  (1, 'alice', 'Grace Cafe (Broad St)', 'Bring your own container.'),         -- id 1, Mon
  (1, 'bob',   'Grace Cafe Philly',     'NOW CUPS PROVIDED - no container');  -- id 2, Tue

select public.approve_edit(2, 'volunteer');   -- Bob, Tuesday

 name              | guidelines
-------------------+------------------------------------
 Grace Cafe Philly | NOW CUPS PROVIDED - no container     ← correct

select public.approve_edit(1, 'volunteer');   -- Alice, Wednesday. Still PENDING, looks unrelated.

 name                  | guidelines
-----------------------+---------------------------
 Grace Cafe (Broad St) | Bring your own container.   ← Bob's fix REVERTED

Alice never touched guidelines. Her snapshot predates Bob's approval, so approving it rolled him back. No warning, no record.

Case B — two contradictory reports, neither knows about the other

The cafe stops serving water. One neighbour reports it closed, another reports it moved.

insert into public.resource_edits (mapped_resource, submitted_by, name) values
  (1, 'alice', 'Grace Cafe - CLOSED PERMANENTLY'),
  (1, 'bob',   'Grace Cafe - moved to 12th St');

The queue — two ordinary rows, two separate pages, no conflict signal anywhere:

 id | submitted_by |          proposed name
----+--------------+---------------------------------
  1 | alice        | Grace Cafe - CLOSED PERMANENTLY
  2 | bob          | Grace Cafe - moved to 12th St

select public.approve_edit(1, 'volunteer');       -- => 'Grace Cafe - CLOSED PERMANENTLY'

select id, review_status from public.resource_edits where id = 2;
--  2 | PENDING                                    ← still live, still has an Approve button

select public.approve_edit(2, 'volunteer2');      -- => 'Grace Cafe - moved to 12th St'
                                                  --   first volunteer's decision, overturned

Suggested Fix

1. approve_edit should put sibling PENDING rows for the same mapped_resource into a terminal state (REJECTED with a system note, or a new SUPERSEDED status) in the same transaction.
2. Add a version / last_modified guard so a stale snapshot can't silently revert newer data.
3. Show the reviewer that competing proposals exist — Use Supabase views for review queues and split reviews page into new vs. existing edits #45's "Pending edits" count column is the first step.

#45's dedupe migration does not fix this

20260818000000_resource_edits_dedupe_pending.sql allows only one PENDING edit per resource by overwriting the existing row on each new submission. Three people report three different things:

insert ... (1, 'alice', 'Grace Cafe - CLOSED PERMANENTLY', 'Bring your own container.');
insert ... (1, 'bob',   'Grace Cafe Philly',               'NOW CUPS PROVIDED');
insert ... (1, 'carol', 'Grace Cafe - moved to 12th St',   'Bring your own container.');

select id, submitted_by, name from public.resource_edits_queue where mapped_resource = 1;

 id | submitted_by |         proposed name
----+--------------+-------------------------------
  1 | carol        | Grace Cafe - moved to 12th St

Alice's closure report and Bob's guidelines update are gone before anyone reviews them, unrecoverable. Case A still happens across the approve → re-submit cycle, so the lost-update bug survives the migration anyway.