Promise Tracker | Airtable | Visibility Control and Deletion Synchronization| Managing Country record or Political record deleted or made inactive on Airtable

[RalphOwah]

User Story: As a Promise Tracker Airtable admin, I want the website to only display "Active" records while ensuring that hard deletions in Airtable are mirrored in Payload CMS and the Ptracker website to prevent duplicate and test data on the live site.

Requirements

1. Airtable Field: Add a Single Select field named Publishing Status with options Active and Inactive.
2. API Gatekeeper: The website's API must be updated to only pull records where Publishing Status is exactly "Active"
3. Payload Toggle Sync: When the status changes to "Inactive" in Airtable, Payload CMS must auto update the record status to Inactive or Draft but keep the data intact in payload. however the record "Country" or "Political" entity is not shown on the public website.
4. Hard Delete Action from Airtable: This trigger must send a DELETE request to the Payload CMS API using the Airtable Record ID as the unique key to permanently wipe the record from both Payload and the webiste.
5. Any Country or Political entity record without Airtable status "Active" should be removed from the public website and saved as a drafted in Payload.

Acceptance Criteria (AC)

1. Status Toggle Logic: Changing a record to Inactive in Airtable makes it disappear from the website immediately, but the record remains visible and "Inactive" inside Payload CMS.
2. Live Visibility: Only records explicitly set to Active in Airtable appear on the public Promise Tracker website.
3. Permanent Removal: If a record is physically deleted from Airtable, it must be automatically deleted from the Payload CMS database and the website.
4. Data Integrity: Reverting a record from Inactive back to Active restores it to the website without data loss, as long as it wasn't hard-deleted.

Definition of Done (DoD)

1. The Publishing Status field is live in Airtable.
2. The website frontend successfully filters out any record not marked "Active".
3. The delete record from Airtable is tested and confirmed to remove records from Payload CMS and the Website.
4. The team has been briefed that "Inactive" is for hiding content and "Delete" is for permanent removal.

Airtable url
https://airtable.com/appIuU48pYIXtqDQg/tbl1UynrhPUg2TUwM/viweBdBjIjkUDnJhP?blocks=hide

[RalphOwah]

Hi @kilemensi, please can I run this proposed solution through with you to see if it is the most optimised approach, easily feasible, before looping in Kipruto?

Reference convo 1: https://code4africa.slack.com/archives/C0DNN0CN5/p1770728118064999