Skip to main content

Fieldwork → Twenty CRM Sync Contract

Date: 2026-05-11 Version: 1.0 Status: Draft — pending implementation


1. Source of Truth Principle

Fieldwork/Postgres is canonical. Twenty CRM stores CRM-facing synced copies.

  • All data originates in Fieldwork (CT604 PostgreSQL, lifehouse.fieldwork schema).
  • Twenty CRM (VM700) receives read-only synced copies for organiser-facing views.
  • No data flows from Twenty back to Fieldwork in the initial sync direction.
  • Fieldwork remains the authoritative source for all doorstep, consent, demographic, and engagement data.
  • If a conflict arises, Fieldwork wins. Twenty records are overwritten on next sync.

2. Table-to-Object Mapping

Fieldwork TableTwenty ObjectDirectionNotes
locationsCustom: fieldworkLocation→ TwentyWards/streets/addresses
householdsCustom: fieldworkHousehold→ TwentyHousehold groupings
peopleTwenty Person + Custom fields→ TwentyCore person records
organisationsTwenty Company→ TwentyLocal organisations
doorstep_contactsCustom: fieldworkEngagement→ TwentyDoorstep interactions
concernsCustom: fieldworkNeed→ TwentyExtracted needs/concerns
resourcesCustom: fieldworkOffer→ TwentyOffers/resources from residents
skillsCustom: fieldworkSkill→ TwentySkills from residents
demographicsCustom: fieldworkDemographic (restricted)→ TwentySortition layer, restricted access
transcription_jobsDO NOT SYNCInternal pipeline only
volunteer_voice_submissionsDO NOT SYNCInternal source log only

3. Field-Level Mapping

3.1 fieldwork.locations → Twenty fieldworkLocation

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)Internal UUID, not PII
address_normalisedaddressDirectCleaned address — safe
postcodepostcodeDirect
wardwardDirect
areaareaDirect
created_atsourceCreatedAtDirect
updated_atsourceUpdatedAtDirectFor change detection
address_rawDO NOT SYNCMay contain PII
geomlatitude, longitudeExtract from PostGIS if neededOptional

3.2 fieldwork.households → Twenty fieldworkHousehold

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
namenameDirect
location_idlocationIdLookup Twenty location ID by fieldworkExternalId
last_contactlastContactAtDirect
contact_countcontactCountDirect
created_atsourceCreatedAtDirect
updated_atsourceUpdatedAtDirect
notesnotesSync only if reviewed flag presentMay contain raw text

3.3 fieldwork.people → Twenty Person + Custom Fields

Source ColumnTarget FieldTransformPrivacy Note
idCustom: fieldworkExternalIdDirect (text)
name_givenname.firstNameDirect
name_familyname.lastNameDirect
emailemailDirectPII — ensure consent before sync
phonephoneDirectPII — ensure consent before sync
household_idCustom: householdIdLookup Twenty household ID
roleCustom: fieldworkRoleNormalise to picklistFree-text needs mapping
consent_statusCustom: consentStatusDirectCritical for GDPR
consent_updated_atCustom: consentUpdatedAtDirect
created_atsourceCreatedAtDirect
updated_atsourceUpdatedAtDirect
telegram_idDO NOT SYNCExternal platform ID
notesCustom: fieldworkNotesSync only if reviewedMay contain raw/sensitive text

GDPR Gate: Only sync people with consent_status IN ('granted') OR where there is a legitimate interest assessment documented. People with consent_status = 'refused' or 'withdrawn' must not be synced. People with consent_status = 'not_asked' should be flagged for review.

3.4 fieldwork.organisations → Twenty Company

Source ColumnTarget FieldTransformPrivacy Note
idCustom: fieldworkExternalIdDirect (text)
namenameDirect
typeCustom: organisationTypeNormalise to picklistFree-text needs mapping
location_idCustom: locationIdLookup Twenty location ID
contact_person_idCustom: contactPersonIdLookup Twenty person ID
created_atsourceCreatedAtDirect
updated_atsourceUpdatedAtDirect
notesnotesSync only if reviewed

3.5 fieldwork.doorstep_contacts → Twenty fieldworkEngagement

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
household_idhouseholdIdLookup Twenty household ID
person_idpersonIdLookup Twenty person ID
volunteer_idvolunteerIdLookup Twenty person ID
outcomeoutcomeDirect (enum match)
input_sourceinputSourceDirect
llm_summarysummaryDirectAlready cleaned by LLM
llm_extractedextractedDataDirect (jsonb→json)Structured extraction
follow_uprequiresFollowUpDirect
follow_up_notesfollowUpNotesDirect
captured_atengagementDateDirect
created_atsourceCreatedAtDirect
raw_transcriptDO NOT SYNCRaw STT — privacy risk
job_idsourceJobIdDirect (reference only)Audit trail

3.6 fieldwork.concerns → Twenty fieldworkNeed

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
person_idpersonIdLookup Twenty person ID
concerndescriptionDirect
categorycategoryNormalise to picklistFree-text needs mapping
confidenceconfidenceDirectinferred/confirmed/reviewed
source_jobsourceJobIdDirect (reference)Audit trail
created_atsourceCreatedAtDirect

Confidence Gate: Concerns with confidence = 'inferred' should be synced but flagged as "Unverified" in Twenty. Only confidence = 'reviewed' or 'confirmed' should display as active needs.

3.7 fieldwork.resources → Twenty fieldworkOffer

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
person_idpersonIdLookup Twenty person ID
resourcedescriptionDirect
confidenceconfidenceDirectinferred/confirmed/reviewed
source_jobsourceJobIdDirect (reference)
created_atsourceCreatedAtDirect

3.8 fieldwork.skills → Twenty fieldworkSkill

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
person_idpersonIdLookup Twenty person ID
skilldescriptionDirect
confidenceconfidenceDirectinferred/confirmed/reviewed
source_jobsourceJobIdDirect (reference)
created_atsourceCreatedAtDirect

3.9 fieldwork.demographics → Twenty fieldworkDemographic (RESTRICTED)

Source ColumnTarget FieldTransformPrivacy Note
idfieldworkExternalIdDirect (text)
person_idpersonIdLookup Twenty person ID
age_bandageBandDirectSpecial category — restricted
gendergenderDirectSpecial category — restricted
ethnicityethnicityDirectSpecial category — restricted
housing_tenurehousingTenureDirect
employment_statusemploymentStatusDirect
has_dependantshasDependantsDirect
disabilitydisabilityDirectHealth data — restricted
areaareaDirect
created_atsourceCreatedAtDirect
updated_atsourceUpdatedAtDirect

Access Control: This object must have role-based access control in Twenty. Only users with "Demographic Data" permission can view. Standard CRM users should not see these fields.


4. Idempotency

External ID Strategy

Every Twenty custom object must include a fieldworkExternalId field (type: text, unique).

fieldworkExternalId = fieldwork_table_name + ":" + id

Examples:

  • locations:550e8400-e29b-41d4-a716-446655440000
  • people:660e8400-e29b-41d4-a716-446655440001
  • doorstep_contacts:770e8400-e29b-41d4-a716-446655440002

Upsert Logic

1. Query Twenty: find record where fieldworkExternalId = <external_id>
2. If found:
a. Compare sourceUpdatedAt with Twenty's sourceUpdatedAt
b. If source is newer: UPDATE the Twenty record
c. If same age: SKIP (no change)
3. If not found:
a. CREATE new Twenty record with fieldworkExternalId

Change Detection

  • Use updated_at from Fieldwork as sourceUpdatedAt in Twenty.
  • Before updating, compare: only write if source.updated_at > target.sourceUpdatedAt.
  • This prevents unnecessary API calls and preserves any manual enrichments in Twenty (if those fields are not overwritten).

Sync State Table

Create fieldwork.sync_state in Postgres to track sync progress:

CREATE TABLE fieldwork.sync_state (
table_name text PRIMARY KEY,
last_synced_at timestamptz,
last_row_count integer,
last_error text,
updated_at timestamptz NOT NULL DEFAULT now()
);

5. Sync Order

Dependencies require this exact order:

1. locations (no dependencies)
2. households (depends on locations)
3. people (depends on households)
4. organisations (depends on locations, people)
5. doorstep_contacts (depends on households, people)
6. concerns (depends on people)
7. resources (depends on people)
8. skills (depends on people)
9. demographics (depends on people, RESTRICTED)

Each step completes fully before the next begins. Within each step, process in order of updated_at ascending (oldest first).

Follow-Up Task Creation (Post-Sync)

After syncing doorstep_contacts, create Twenty Tasks for records where:

  • follow_up = true
  • No existing open Task linked to this engagement

6. Privacy Rules

Data That Stays Outside Twenty

DataLocationReason
Raw audio (audio_data)CT604 onlyBinary, never expose
Raw transcripts (transcript, raw_transcript)CT604 onlyMay contain sensitive/PII
Telegram IDs (telegram_id, telegram_user_id, chat_id, message_id)CT604 onlyExternal platform identifiers
Raw addresses (address_raw)CT604 onlyMay contain PII not in normalised form

Data That Syncs as Cleaned/Summary

DataTreatment
llm_summarySync as-is (LLM-cleaned)
llm_extractedSync structured jsonb fields
notes fieldsSync only if reviewed/approved flag present
address_normalisedSync instead of raw
  • People with consent_status = 'granted': Sync to Twenty.
  • People with consent_status = 'not_asked': Do not sync. Flag for consent collection.
  • People with consent_status = 'refused' or 'withdrawn': Do not sync. If previously synced, delete or anonymise the Twenty record.
  • Legitimate interest: If documented, may override for specific limited processing. Requires case-by-case assessment.

Demographic Data

  • Sync to a separate restricted-access object in Twenty.
  • Not visible in standard Person views.
  • Role-based access control required.
  • Primary use: sortition and aggregate analysis outside CRM.

Sortition Logic

  • Sortition (random selection for citizens' assemblies etc.) remains entirely outside Twenty.
  • Twenty stores only the demographic data needed for sortition eligibility filtering.
  • Actual random selection algorithms run against Fieldwork/Postgres directly.

7. First Implementation Plan

7.1 Python Sync Worker

Location: /root/lifehouse-twenty-sync/sync_worker.py

Dependencies:

  • psycopg2 (read from CT604 Postgres)
  • requests (write to Twenty API)
  • python-dotenv (configuration)

Configuration (.env):

FIELDWORK_DB_HOST=127.0.0.1
FIELDWORK_DB_PORT=5432
FIELDWORK_DB_NAME=lifehouse
FIELDWORK_DB_USER=postgres
FIELDWORK_DB_PASSWORD=<from CT604>

TWENTY_BASE_URL=http://192.168.100.220:3000
TWENTY_API_KEY=<twenty_api_key>

DRY_RUN=true
SYNC_DEMOGRAPHICS=false
LOG_LEVEL=INFO

7.2 Modes

ModeFlagBehaviour
Dry-runDRY_RUN=trueRead Fieldwork, compute diffs, log what would change. No writes to Twenty.
Live syncDRY_RUN=falseRead Fieldwork, compute diffs, write to Twenty.
Single table--table peopleSync only one table (for testing).
Full sync--fullSync all tables in dependency order.
Since--since 2026-05-01Only sync records updated after date.

7.3 Sync Worker Pseudocode

def sync_table(table_name, twenty_object_type, field_mapping, dry_run):
# 1. Read from Postgres
rows = query(f"SELECT * FROM fieldwork.{table_name} WHERE updated_at > %s", (last_synced_at,))

# 2. For each row
for row in rows:
external_id = f"{table_name}:{row['id']}"

# 3. Check if exists in Twenty
existing = twenty_find_by_external_id(twenty_object_type, external_id)

if existing:
if row['updated_at'] <= existing['sourceUpdatedAt']:
continue # No change
if dry_run:
log(f"[DRY-RUN] Would UPDATE {twenty_object_type} {external_id}")
else:
twenty_update(twenty_object_type, existing['id'], map_fields(row, field_mapping))
else:
if dry_run:
log(f"[DRY-RUN] Would CREATE {twenty_object_type} {external_id}")
else:
twenty_create(twenty_object_type, map_fields(row, field_mapping))

# 4. Update sync state
update_sync_state(table_name)

def main():
sync_order = ['locations', 'households', 'people', 'organisations',
'doorstep_contacts', 'concerns', 'resources', 'skills']

if not DRY_RUN:
verify_twenty_connectivity()
verify_twenty_auth()

for table in sync_order:
log(f"Syncing {table}...")
sync_table(table, TABLE_TO_OBJECT[table], FIELD_MAPPINGS[table], DRY_RUN)
log(f"Done: {table}")

if not DRY_RUN:
create_follow_up_tasks()

log("Sync complete.")

7.4 Sync Logs

Log to /var/log/fieldwork-twenty-sync.log with format:

2026-05-11T19:00:00Z [INFO] Starting sync: locations
2026-05-11T19:00:01Z [INFO] locations: 0 creates, 0 updates, 0 skipped
2026-05-11T19:00:01Z [INFO] Starting sync: households
2026-05-11T19:00:02Z [INFO] households: 3 creates, 1 update, 0 skipped
...
2026-05-11T19:00:15Z [INFO] Sync complete. 15 creates, 3 updates, 42 skipped.

7.5 Optional Zulip Notification

After successful sync, post to Zulip stream #fieldwork-sync:

Fieldwork → Twenty sync complete
- Locations: 0 created, 0 updated
- Households: 3 created, 1 updated
- People: 5 created, 2 updated
- Engagements: 7 created, 0 updated
- Needs: 4 created, 0 updated
- Offers: 3 created, 0 updated
- Skills: 2 created, 0 updated
Total: 24 creates, 3 updates

8. Missing Fields / Schema Gaps

8.1 Fields to Consider Adding to Fieldwork/Postgres

TableSuggested FieldTypePurpose
peoplereviewed_attimestamptzWhen notes were last reviewed for CRM sync
peoplereviewed_bytextWho reviewed
householdsreviewed_attimestamptzSame as above
organisationsreviewed_attimestamptzSame as above
concernsreviewed_attimestamptzWhen concern was human-reviewed
concernsreviewed_bytextWho reviewed
concernsstatustexte.g. open, in_progress, resolved
resourcesreviewed_attimestamptzSame as above
skillsreviewed_attimestamptzSame as above
doorstep_contactsreviewed_attimestamptzWhen contact was reviewed
consent_records (new table)Audit log of consent changes
sync_state (new table)Track what's been synced to Twenty

8.2 Fields to Consider Adding to Twenty

Twenty ObjectSuggested FieldTypePurpose
All custom objectsfieldworkExternalIdtext (unique)Idempotency key
All custom objectssourceCreatedAtdatetimeOrigin timestamp
All custom objectssourceUpdatedAtdatetimeChange detection
fieldworkEngagementsourceJobIdtextAudit trail to transcription job
fieldworkNeedsourceJobIdtextAudit trail
fieldworkOffersourceJobIdtextAudit trail
fieldworkSkillsourceJobIdtextAudit trail

8.3 Normalisation Mappings Needed

Fieldwork ColumnCurrent TypeNeeded Mapping
people.rolefree-text→ Controlled picklist: resident, volunteer, staff, trustee, councillor, other
concerns.categoryfree-text→ Controlled picklist: housing, health, finance, isolation, food, employment, education, digital, transport, other
organisations.typefree-text→ Controlled picklist: charity, council, business, faith_group, community_group, school, health, other
resources.resourcefree-text→ May benefit from category tagging
skills.skillfree-text→ May benefit from category tagging

9. Twenty Custom Objects Definition

9.1 fieldworkLocation

Fields:
- fieldworkExternalId (text, unique)
- address (text)
- postcode (text)
- ward (text)
- area (text)
- latitude (number, optional)
- longitude (number, optional)
- sourceCreatedAt (datetime)
- sourceUpdatedAt (datetime)

9.2 fieldworkHousehold

Fields:
- fieldworkExternalId (text, unique)
- name (text)
- locationId (relation → fieldworkLocation)
- lastContactAt (datetime)
- contactCount (number)
- notes (text)
- sourceCreatedAt (datetime)
- sourceUpdatedAt (datetime)

9.3 fieldworkEngagement

Fields:
- fieldworkExternalId (text, unique)
- householdId (relation → fieldworkHousehold)
- personId (relation → Person)
- volunteerId (relation → Person)
- outcome (select: unknown, engaged, declined, not_home, follow_up)
- inputSource (select: telegram_voice, web_upload, ocr_image)
- summary (text)
- extractedData (json)
- requiresFollowUp (boolean)
- followUpNotes (text)
- engagementDate (datetime)
- sourceJobId (text)
- sourceCreatedAt (datetime)

9.4 fieldworkNeed

Fields:
- fieldworkExternalId (text, unique)
- personId (relation → Person)
- description (text)
- category (select)
- confidence (select: inferred, confirmed, reviewed)
- sourceJobId (text)
- sourceCreatedAt (datetime)

9.5 fieldworkOffer

Fields:
- fieldworkExternalId (text, unique)
- personId (relation → Person)
- description (text)
- confidence (select: inferred, confirmed, reviewed)
- sourceJobId (text)
- sourceCreatedAt (datetime)

9.6 fieldworkSkill

Fields:
- fieldworkExternalId (text, unique)
- personId (relation → Person)
- description (text)
- confidence (select: inferred, confirmed, reviewed)
- sourceJobId (text)
- sourceCreatedAt (datetime)

9.7 fieldworkDemographic (RESTRICTED ACCESS)

Fields:
- fieldworkExternalId (text, unique)
- personId (relation → Person)
- ageBand (text)
- gender (text)
- ethnicity (text)
- housingTenure (text)
- employmentStatus (text)
- hasDependants (boolean)
- disability (boolean)
- area (text)
- sourceCreatedAt (datetime)
- sourceUpdatedAt (datetime)

Access: Only users with "Demographic Data" permission.

9.8 Person Custom Fields (added to standard Twenty Person)

Custom Fields on Person:
- fieldworkExternalId (text, unique)
- fieldworkRole (select)
- householdId (relation → fieldworkHousehold)
- consentStatus (select: not_asked, granted, refused, withdrawn)
- consentUpdatedAt (datetime)
- fieldworkNotes (text)
- sourceCreatedAt (datetime)
- sourceUpdatedAt (datetime)

9.9 Company Custom Fields (added to standard Twenty Company)

Custom Fields on Company:
- fieldworkExternalId (text, unique)
- organisationType (select)
- locationId (relation → fieldworkLocation)
- contactPersonId (relation → Person)
- sourceCreatedAt (datetime)
- sourceUpdatedAt (datetime)

10. Blockers

  1. Twenty custom objects must be created first — The sync worker cannot run until all custom objects and fields are defined in Twenty. This requires Twenty admin access.

  2. Twenty API key with write permissions — The existing API key may be read-only. Verify it has permission to create/update custom objects and standard objects.

  3. Consent status handling — Need a clear policy on what happens when consent is withdrawn for a person already synced to Twenty. Options: delete the Twenty record, anonymise it, or flag it as "consent withdrawn — do not contact".

  4. Demographic data access control — Twenty must support role-based permissions on custom objects. If not, demographic data should not be synced until access control is available.

  5. Normalisation picklists — The free-text fields (role, category, type) need agreed-upon controlled vocabularies before sync can populate select fields correctly.

  6. Review workflow — Currently no explicit review step before data reaches Twenty. Consider adding a simple review flag or status field to key tables.


11. Next Steps

Immediate (Before Coding)

  1. Create Twenty custom objects and fields as defined in Section 9.
  2. Verify Twenty API key has write permissions.
  3. Agree on normalisation picklists for role, category, type.
  4. Decide consent withdrawal policy.

Implementation (Next Prompt)

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
- 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

Appendix A: Quick Reference — Sync Order & Dependencies

1. locations ← no deps
2. households ← locations
3. people ← households
4. organisations ← locations, people
5. doorstep_contacts ← households, people
6. concerns ← people
7. resources ← people
8. skills ← people
9. demographics ← people (RESTRICTED)

Appendix B: Quick Reference — Privacy Gates

GateRule
ConsentOnly sync people with consent_status = 'granted'
Raw transcriptsNever sync
Audio dataNever sync
Telegram IDsNever sync
DemographicsRestricted access object only
NotesSync only if reviewed
AddressesSync normalised, not raw