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.fieldworkschema). - 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 Table | Twenty Object | Direction | Notes |
|---|---|---|---|
locations | Custom: fieldworkLocation | → Twenty | Wards/streets/addresses |
households | Custom: fieldworkHousehold | → Twenty | Household groupings |
people | Twenty Person + Custom fields | → Twenty | Core person records |
organisations | Twenty Company | → Twenty | Local organisations |
doorstep_contacts | Custom: fieldworkEngagement | → Twenty | Doorstep interactions |
concerns | Custom: fieldworkNeed | → Twenty | Extracted needs/concerns |
resources | Custom: fieldworkOffer | → Twenty | Offers/resources from residents |
skills | Custom: fieldworkSkill | → Twenty | Skills from residents |
demographics | Custom: fieldworkDemographic (restricted) | → Twenty | Sortition layer, restricted access |
transcription_jobs | DO NOT SYNC | — | Internal pipeline only |
volunteer_voice_submissions | DO NOT SYNC | — | Internal source log only |
3. Field-Level Mapping
3.1 fieldwork.locations → Twenty fieldworkLocation
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | Internal UUID, not PII |
address_normalised | address | Direct | Cleaned address — safe |
postcode | postcode | Direct | |
ward | ward | Direct | |
area | area | Direct | |
created_at | sourceCreatedAt | Direct | |
updated_at | sourceUpdatedAt | Direct | For change detection |
address_raw | DO NOT SYNC | — | May contain PII |
geom | latitude, longitude | Extract from PostGIS if needed | Optional |
3.2 fieldwork.households → Twenty fieldworkHousehold
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
name | name | Direct | |
location_id | locationId | Lookup Twenty location ID by fieldworkExternalId | |
last_contact | lastContactAt | Direct | |
contact_count | contactCount | Direct | |
created_at | sourceCreatedAt | Direct | |
updated_at | sourceUpdatedAt | Direct | |
notes | notes | Sync only if reviewed flag present | May contain raw text |
3.3 fieldwork.people → Twenty Person + Custom Fields
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | Custom: fieldworkExternalId | Direct (text) | |
name_given | name.firstName | Direct | |
name_family | name.lastName | Direct | |
email | email | Direct | PII — ensure consent before sync |
phone | phone | Direct | PII — ensure consent before sync |
household_id | Custom: householdId | Lookup Twenty household ID | |
role | Custom: fieldworkRole | Normalise to picklist | Free-text needs mapping |
consent_status | Custom: consentStatus | Direct | Critical for GDPR |
consent_updated_at | Custom: consentUpdatedAt | Direct | |
created_at | sourceCreatedAt | Direct | |
updated_at | sourceUpdatedAt | Direct | |
telegram_id | DO NOT SYNC | — | External platform ID |
notes | Custom: fieldworkNotes | Sync only if reviewed | May 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 Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | Custom: fieldworkExternalId | Direct (text) | |
name | name | Direct | |
type | Custom: organisationType | Normalise to picklist | Free-text needs mapping |
location_id | Custom: locationId | Lookup Twenty location ID | |
contact_person_id | Custom: contactPersonId | Lookup Twenty person ID | |
created_at | sourceCreatedAt | Direct | |
updated_at | sourceUpdatedAt | Direct | |
notes | notes | Sync only if reviewed |
3.5 fieldwork.doorstep_contacts → Twenty fieldworkEngagement
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
household_id | householdId | Lookup Twenty household ID | |
person_id | personId | Lookup Twenty person ID | |
volunteer_id | volunteerId | Lookup Twenty person ID | |
outcome | outcome | Direct (enum match) | |
input_source | inputSource | Direct | |
llm_summary | summary | Direct | Already cleaned by LLM |
llm_extracted | extractedData | Direct (jsonb→json) | Structured extraction |
follow_up | requiresFollowUp | Direct | |
follow_up_notes | followUpNotes | Direct | |
captured_at | engagementDate | Direct | |
created_at | sourceCreatedAt | Direct | |
raw_transcript | DO NOT SYNC | — | Raw STT — privacy risk |
job_id | sourceJobId | Direct (reference only) | Audit trail |
3.6 fieldwork.concerns → Twenty fieldworkNeed
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
person_id | personId | Lookup Twenty person ID | |
concern | description | Direct | |
category | category | Normalise to picklist | Free-text needs mapping |
confidence | confidence | Direct | inferred/confirmed/reviewed |
source_job | sourceJobId | Direct (reference) | Audit trail |
created_at | sourceCreatedAt | Direct |
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 Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
person_id | personId | Lookup Twenty person ID | |
resource | description | Direct | |
confidence | confidence | Direct | inferred/confirmed/reviewed |
source_job | sourceJobId | Direct (reference) | |
created_at | sourceCreatedAt | Direct |
3.8 fieldwork.skills → Twenty fieldworkSkill
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
person_id | personId | Lookup Twenty person ID | |
skill | description | Direct | |
confidence | confidence | Direct | inferred/confirmed/reviewed |
source_job | sourceJobId | Direct (reference) | |
created_at | sourceCreatedAt | Direct |
3.9 fieldwork.demographics → Twenty fieldworkDemographic (RESTRICTED)
| Source Column | Target Field | Transform | Privacy Note |
|---|---|---|---|
id | fieldworkExternalId | Direct (text) | |
person_id | personId | Lookup Twenty person ID | |
age_band | ageBand | Direct | Special category — restricted |
gender | gender | Direct | Special category — restricted |
ethnicity | ethnicity | Direct | Special category — restricted |
housing_tenure | housingTenure | Direct | |
employment_status | employmentStatus | Direct | |
has_dependants | hasDependants | Direct | |
disability | disability | Direct | Health data — restricted |
area | area | Direct | |
created_at | sourceCreatedAt | Direct | |
updated_at | sourceUpdatedAt | Direct |
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-446655440000people:660e8400-e29b-41d4-a716-446655440001doorstep_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_atfrom Fieldwork assourceUpdatedAtin 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
| Data | Location | Reason |
|---|---|---|
Raw audio (audio_data) | CT604 only | Binary, never expose |
Raw transcripts (transcript, raw_transcript) | CT604 only | May contain sensitive/PII |
Telegram IDs (telegram_id, telegram_user_id, chat_id, message_id) | CT604 only | External platform identifiers |
Raw addresses (address_raw) | CT604 only | May contain PII not in normalised form |
Data That Syncs as Cleaned/Summary
| Data | Treatment |
|---|---|
llm_summary | Sync as-is (LLM-cleaned) |
llm_extracted | Sync structured jsonb fields |
notes fields | Sync only if reviewed/approved flag present |
address_normalised | Sync instead of raw |
Consent Gate
- 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
| Mode | Flag | Behaviour |
|---|---|---|
| Dry-run | DRY_RUN=true | Read Fieldwork, compute diffs, log what would change. No writes to Twenty. |
| Live sync | DRY_RUN=false | Read Fieldwork, compute diffs, write to Twenty. |
| Single table | --table people | Sync only one table (for testing). |
| Full sync | --full | Sync all tables in dependency order. |
| Since | --since 2026-05-01 | Only 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
| Table | Suggested Field | Type | Purpose |
|---|---|---|---|
people | reviewed_at | timestamptz | When notes were last reviewed for CRM sync |
people | reviewed_by | text | Who reviewed |
households | reviewed_at | timestamptz | Same as above |
organisations | reviewed_at | timestamptz | Same as above |
concerns | reviewed_at | timestamptz | When concern was human-reviewed |
concerns | reviewed_by | text | Who reviewed |
concerns | status | text | e.g. open, in_progress, resolved |
resources | reviewed_at | timestamptz | Same as above |
skills | reviewed_at | timestamptz | Same as above |
doorstep_contacts | reviewed_at | timestamptz | When 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 Object | Suggested Field | Type | Purpose |
|---|---|---|---|
| All custom objects | fieldworkExternalId | text (unique) | Idempotency key |
| All custom objects | sourceCreatedAt | datetime | Origin timestamp |
| All custom objects | sourceUpdatedAt | datetime | Change detection |
fieldworkEngagement | sourceJobId | text | Audit trail to transcription job |
fieldworkNeed | sourceJobId | text | Audit trail |
fieldworkOffer | sourceJobId | text | Audit trail |
fieldworkSkill | sourceJobId | text | Audit trail |
8.3 Normalisation Mappings Needed
| Fieldwork Column | Current Type | Needed Mapping |
|---|---|---|
people.role | free-text | → Controlled picklist: resident, volunteer, staff, trustee, councillor, other |
concerns.category | free-text | → Controlled picklist: housing, health, finance, isolation, food, employment, education, digital, transport, other |
organisations.type | free-text | → Controlled picklist: charity, council, business, faith_group, community_group, school, health, other |
resources.resource | free-text | → May benefit from category tagging |
skills.skill | free-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
-
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.
-
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.
-
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".
-
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.
-
Normalisation picklists — The free-text fields (
role,category,type) need agreed-upon controlled vocabularies before sync can populate select fields correctly. -
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)
- Create Twenty custom objects and fields as defined in Section 9.
- Verify Twenty API key has write permissions.
- Agree on normalisation picklists for
role,category,type. - 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
| Gate | Rule |
|---|---|
| Consent | Only sync people with consent_status = 'granted' |
| Raw transcripts | Never sync |
| Audio data | Never sync |
| Telegram IDs | Never sync |
| Demographics | Restricted access object only |
| Notes | Sync only if reviewed |
| Addresses | Sync normalised, not raw |