Skip to main content

Next Checks — Critical Verifications Before Sync Worker Build

Purpose: Verify infrastructure readiness before implementing the Fieldwork → Twenty sync worker.

Timeline: Should be completed before starting sync_worker.py implementation.

1. Fieldwork App Route Confirmation​

Status: ❓ Unknown

Check:

  • Confirm whether Fieldwork has a public-facing web application
  • If yes: document the URL and routing (CT101 Caddy route, if applicable)
  • If no: confirm it is intentionally backend-only (Telegram bot + API only)

Command (if applicable):

# Check CT101 Caddyfile for fieldwork routes
pct exec 101 -- grep -i fieldwork /etc/caddy/Caddyfile

Expected outcome: Either a confirmed public URL or explicit documentation that Fieldwork is backend-only.

2. Fieldwork Pilot Data Count​

Status: ❓ Unknown

Check:

  • Verify real pilot data exists in CT604 Postgres
  • Count records in key tables: people, doorstep_contacts, concerns, resources, skills
  • Identify any data quality issues (null values, malformed addresses, etc.)

Command:

# SSH to CT604 and query Fieldwork schema
ssh [email protected]

psql -U fieldwork_api -d lifehouse << EOF
SELECT 'people' as table_name, COUNT(*) as count FROM fieldwork.people
UNION ALL
SELECT 'doorstep_contacts', COUNT(*) FROM fieldwork.doorstep_contacts
UNION ALL
SELECT 'concerns', COUNT(*) FROM fieldwork.concerns
UNION ALL
SELECT 'resources', COUNT(*) FROM fieldwork.resources
UNION ALL
SELECT 'skills', COUNT(*) FROM fieldwork.skills
UNION ALL
SELECT 'households', COUNT(*) FROM fieldwork.households
UNION ALL
SELECT 'organisations', COUNT(*) FROM fieldwork.organisations
UNION ALL
SELECT 'locations', COUNT(*) FROM fieldwork.locations;
EOF

Expected outcome: Confirmed row counts for each table, or explicit confirmation that pilot data is empty/placeholder.

Status: ❓ Unknown

Check:

  • Verify consent status distribution in fieldwork.people
  • Identify how many records have consent_status = 'granted' (syncable)
  • Identify how many have 'not_asked', 'refused', or 'withdrawn' (not syncable or flagged)

Command:

psql -U fieldwork_api -d lifehouse -c "SELECT consent_status, COUNT(*) FROM fieldwork.people GROUP BY consent_status;"

Expected outcome: Clear breakdown of consent statuses, informing sync filtering strategy.

4. Twenty API Write Permissions​

Status: ❓ Unknown

Check:

  • Verify the existing Twenty API key has write permissions
  • Test creating a custom object record via REST API
  • Confirm API key can update existing records

Command:

# Test API write permission with a test Person record
curl -X POST http://192.168.100.220:3000/rest/people \
  -H "Authorization: Bearer <TWENTY_API_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "name": {"firstName": "Test", "lastName": "Write"},
    "email": "[email protected]"
  }'

Expected outcome: HTTP 201 (created) or confirmation of write access. If 403 (forbidden), API key needs upgrade.

5. Sync State Table in Postgres​

Status: ❓ Unknown

Check:

  • Verify fieldwork.sync_state table exists in CT604 Postgres
  • If not, it will need to be created before sync worker runs
  • This table tracks which Fieldwork records have been synced to Twenty

Command:

psql -U fieldwork_api -d lifehouse -c "SELECT * FROM fieldwork.sync_state;"

Expected outcome: Either table exists with schema, or explicit confirmation it needs to be created.

6. Reviewed_at and Reviewed_by Fields​

Status: ❓ Unknown

Check:

  • Verify concerns, resources, skills tables have reviewed_at and reviewed_by fields
  • If not, these will need to be added before syncing (to track data review status)
  • These fields enable filtering of unreviewed/inferred data before sync

Command:

psql -U fieldwork_api -d lifehouse -c "\d fieldwork.concerns;"
psql -U fieldwork_api -d lifehouse -c "\d fieldwork.resources;"
psql -U fieldwork_api -d lifehouse -c "\d fieldwork.skills;"

Expected outcome: Either fields exist, or explicit plan to add them before sync.

7. Demographic Access Control in Twenty​

Status: ❓ Unknown

Check:

  • Verify Twenty has role-based access control (RBAC) for the fieldworkDemographic custom object
  • Confirm only users with "Demographic Data" permission can view demographic records
  • Test that standard CRM users cannot access demographic fields

Command:

# Check Twenty settings for custom object permissions
# (Requires manual UI inspection or GraphQL query)

Expected outcome: Confirmed RBAC implementation or explicit plan to add it before syncing demographics.

Verification Checklist​

  • Fieldwork app route confirmed (public or backend-only)
  • Pilot data count verified in CT604
  • Consent status distribution documented
  • Twenty API write permissions tested
  • sync_state table exists (or creation plan documented)
  • reviewed_at / reviewed_by fields exist (or addition plan documented)
  • Demographic access control implemented (or plan documented)

Blockers Requiring Resolution​

If any of the above checks fail, the following blockers must be resolved before sync worker implementation:

  1. Normalisation picklists — Agreed-upon controlled vocabularies for:

    • people.role → picklist (resident, volunteer, staff, trustee, councillor, other)
    • concerns.category → picklist (housing, health, finance, isolation, food, employment, education, digital, transport, other)
    • organisations.type → picklist (charity, council, business, faith_group, community_group, school, health, other)

  2. Consent withdrawal policy — Clear decision on what happens when consent is withdrawn for a person already synced to Twenty:

    • Delete the Twenty record?
    • Anonymise it?
    • Flag it as "consent withdrawn — do not contact"?

  3. Demographic data access control — Twenty must support role-based permissions on custom objects before syncing sensitive demographic data.

  4. Review workflow — Decision on whether extracted data (concerns, resources, skills) requires explicit human review before sync, or if confidence levels are sufficient.

Next Prompt Recommendation​

Once all checks are complete and blockers resolved:

Build the Fieldwork → Twenty sync worker at /root/lifehouse-twenty-sync/sync_worker.py

Requirements:
- Python 3
- Read from CT604 Postgres (lifehouse.fieldwork)
- Write to Twenty API at http://192.168.100.220:3000
- Dry-run mode first (DRY_RUN=true)
- Follow the sync contract in FIELDWORK_TO_TWENTY_SYNC_CONTRACT.md
- Log to /var/log/fieldwork-twenty-sync.log
- Do not sync raw transcripts, audio, or telegram IDs
- Respect consent_status gate
- Use fieldworkExternalId for idempotency
- Sync in dependency order: locations → households → people → organisations → doorstep_contacts → concerns/resources/skills → demographics

All checks must be completed before proceeding to sync worker implementation.