Skip to main content

Fieldwork PostgreSQL Schema Inspection

Date: 2026-05-11 Host: CT604 (lifehouse-db), 192.168.100.204 Database: lifehouse Schema: fieldwork PostGIS: Yes (geometry on locations)


Custom Enums​

EnumValues
fieldwork.consent_statusnot_asked, granted, refused, withdrawn
fieldwork.job_statusqueued, processing, completed, failed
fieldwork.input_sourcetelegram_voice, web_upload, ocr_image
fieldwork.contact_outcomeunknown, engaged, declined, not_home, follow_up

Table: fieldwork.people​

Purpose: Core person records. Central entity linking households, concerns, demographics, doorstep contacts, resources, skills, and organisations.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
household_iduuidYES—FK → fieldwork.households(id)
name_giventextYES—First name
name_familytextYES—Last name
telegram_idtextYES—External ID from Telegram
emailtextYES—
phonetextYES—
roletextYES—e.g. resident, volunteer, staff
notestextYES—Free-text notes
consent_statusfieldwork.consent_statusNOT NULL'not_asked'GDPR consent tracking
consent_updated_attimestamptzYES—When consent last changed
created_attimestamptzNOT NULLnow()
updated_attimestamptzNOT NULLnow()

Indexes:

  • people_pkey — PRIMARY KEY btree (id)
  • idx_people_household — btree (household_id)
  • idx_people_telegram — btree (telegram_id)

Referenced by:

  • concerns.person_id → people(id) ON DELETE CASCADE
  • demographics.person_id → people(id) ON DELETE CASCADE
  • doorstep_contacts.person_id → people(id)
  • doorstep_contacts.volunteer_id → people(id)
  • organisations.contact_person_id → people(id)
  • resources.person_id → people(id) ON DELETE CASCADE
  • skills.person_id → people(id) ON DELETE CASCADE

Sync notes:

  • telegram_id is an external identifier — useful for dedup but should not be exposed in CRM
  • consent_status is critical for GDPR — must sync to Twenty
  • notes may contain raw/unreviewed text — consider summary-only sync
  • role is free-text — may need normalisation for CRM views

Table: fieldwork.households​

Purpose: Household groupings. Links to locations and contains aggregated contact tracking.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
location_iduuidYES—FK → fieldwork.locations(id)
nametextYES—Household label
notestextYES—Free-text notes
last_contacttimestamptzYES—Most recent doorstep contact
contact_countintegerNOT NULL0Aggregate count of contacts
created_attimestamptzNOT NULLnow()
updated_attimestamptzNOT NULLnow()

Indexes:

  • households_pkey — PRIMARY KEY btree (id)

Referenced by:

  • doorstep_contacts.household_id → households(id)
  • people.household_id → households(id)

Sync notes:

  • contact_count is derived/aggregate — sync as-is or recalculate
  • last_contact is useful for CRM prioritisation
  • notes may contain raw text — consider summary-only

Table: fieldwork.doorstep_contacts​

Purpose: Captured doorstep interactions. Core engagement record linking households, people, volunteers, transcription jobs, and LLM extraction results.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
job_iduuidYES—FK → fieldwork.transcription_jobs(job_id)
household_iduuidYES—FK → fieldwork.households(id)
person_iduuidYES—FK → fieldwork.people(id) — who was spoken to
volunteer_iduuidYES—FK → fieldwork.people(id) — who did the contact
outcomefieldwork.contact_outcomeNOT NULL'unknown'engaged/declined/not_home/follow_up
input_sourcefieldwork.input_sourceNOT NULL'telegram_voice'How the contact was captured
raw_transcripttextYES—DO NOT SYNC — raw STT output
llm_summarytextYES—Cleaned summary — sync this
llm_extractedjsonbYES'{}'Structured extraction — sync this
follow_upbooleanYESfalseFlag for follow-up needed
follow_up_notestextYES—Notes on follow-up required
captured_attimestamptzNOT NULLnow()When the contact happened
created_attimestamptzNOT NULLnow()When record was created

Indexes:

  • doorstep_contacts_pkey — PRIMARY KEY btree (id)
  • idx_doorstep_captured — btree (captured_at)
  • idx_doorstep_household — btree (household_id)
  • idx_doorstep_llm_extracted — gin (llm_extracted)
  • idx_doorstep_outcome — btree (outcome)

Referenced by:

  • transcription_jobs.doorstep_contact_id → doorstep_contacts(id)
  • transcription_jobs.v2_contact_id → doorstep_contacts(id)

Sync notes:

  • raw_transcript — NEVER sync to Twenty (privacy, raw audio derivative)
  • llm_summary — sync as engagement note/summary
  • llm_extracted — parse jsonb for structured fields (concerns, resources, skills already extracted into separate tables)
  • outcome — maps to Twenty engagement status
  • follow_up + follow_up_notes — maps to Twenty tasks

Table: fieldwork.transcription_jobs​

Purpose: STT job tracking. Contains audio data and transcription results. Primarily internal pipeline — do not sync directly to Twenty.

ColumnTypeNullableDefaultNotes
job_iduuidNOT NULLuuid_generate_v4()PK
statusfieldwork.job_statusNOT NULL'queued'Pipeline status
source_projecttextNOT NULL'fieldwork'Project identifier
reference_idtextYES—External reference
input_sourcefieldwork.input_sourceNOT NULL'telegram_voice'
filenametextYES—Original filename
languagetextYES—Detected language
language_probnumeric(5,4)YES—Detection confidence
duration_secsnumeric(8,3)YES—Audio duration
transcripttextYES—Raw transcript — DO NOT SYNC
segmentsjsonbYES—Timestamped segments
errortextYES—Error message if failed
doorstep_contact_iduuidYES—FK → fieldwork.doorstep_contacts(id)
created_attimestamptzNOT NULLnow()
completed_attimestamptzYES—
audio_databyteaYES—NEVER sync — raw audio binary
v2_contact_iduuidYES—FK → fieldwork.doorstep_contacts(id)
v2_errortextYES—V2 pipeline error

Indexes:

  • transcription_jobs_pkey — PRIMARY KEY btree (job_id)
  • idx_jobs_created — btree (created_at)
  • idx_jobs_reference — btree (reference_id)
  • idx_jobs_status — btree (status)
  • idx_jobs_v2_contact — btree (v2_contact_id)

Referenced by:

  • concerns.source_job → transcription_jobs(job_id)
  • doorstep_contacts.job_id → transcription_jobs(job_id)
  • resources.source_job → transcription_jobs(job_id)
  • skills.source_job → transcription_jobs(job_id)

Sync notes:

  • DO NOT sync this table directly — internal pipeline only
  • audio_data — binary, never sync
  • transcript — raw, never sync
  • May sync status + completed_at as audit reference on doorstep_contacts if needed
  • error + v2_error — useful for pipeline monitoring, not CRM

Table: fieldwork.volunteer_voice_submissions​

Purpose: Raw Telegram voice note submission log. Source record for the STT pipeline.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLgen_random_uuid()PK
job_idtextYES—Links to transcription job
telegram_user_idbigintNOT NULL—Telegram user ID
usernametextYES—Telegram @username
first_nametextYES—Telegram first name
last_nametextYES—Telegram last name
chat_idbigintNOT NULL—Telegram chat ID
message_idbigintNOT NULL—Telegram message ID
audio_durationintegerYES—Duration in seconds
created_attimestamptzNOT NULLnow()

Indexes:

  • volunteer_voice_submissions_pkey — PRIMARY KEY btree (id)

Sync notes:

  • DO NOT sync this table directly — internal source log
  • telegram_user_id, username, first_name, last_name — link to volunteer identity but should not be exposed in CRM
  • May sync as source reference on doorstep_contacts (e.g. "source: telegram_voice, ref: sub_xxx")
  • audio_duration — metadata only, not CRM-relevant

Table: fieldwork.concerns​

Purpose: Extracted concerns/needs linked to people. Generated by LLM extraction pipeline from doorstep contacts.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
person_iduuidYES—FK → fieldwork.people(id) ON DELETE CASCADE
concerntextNOT NULL—The concern text
categorytextYES—e.g. housing, health, finance, isolation
confidencetextYES'inferred'inferred / confirmed / reviewed
source_jobuuidYES—FK → fieldwork.transcription_jobs(job_id)
created_attimestamptzNOT NULLnow()

Indexes:

  • concerns_pkey — PRIMARY KEY btree (id)

Sync notes:

  • Maps to Twenty Needs / Agenda Inputs
  • confidence = 'inferred' — should be flagged as unverified in CRM
  • confidence = 'reviewed' — safe to display as confirmed need
  • category — may need normalisation to match Twenty picklist
  • source_job — audit trail only, not CRM-visible

Table: fieldwork.demographics​

Purpose: Demographic data per person. Restricted visibility — not for ordinary CRM views.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
person_iduuidYES—FK → fieldwork.people(id) ON DELETE CASCADE, UNIQUE
age_bandtextYES—e.g. 18-24, 25-34, 65+
gendertextYES—
ethnicitytextYES—
housing_tenuretextYES—e.g. owner, social rent, private rent
employment_statustextYES—
has_dependantsbooleanYES—
disabilitybooleanYES—
areatextYES—
created_attimestamptzNOT NULLnow()
updated_attimestamptzNOT NULLnow()

Indexes:

  • demographics_pkey — PRIMARY KEY btree (id)
  • demographics_person_id_key — UNIQUE btree (person_id)

Sync notes:

  • RESTRICTED — do not sync to standard Twenty views
  • If synced, must go to a restricted-access custom object with role-based visibility
  • Primary use: sortition/analysis outside CRM
  • age_band, gender, ethnicity — special category data under GDPR
  • disability — health data under GDPR
  • Consider syncing only aggregate/anonymised demographic summaries to Twenty

Table: fieldwork.locations​

Purpose: Geocoded addresses with PostGIS geometry. Links to households and organisations.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
address_rawtextYES—Original address text
address_normalisedtextYES—Cleaned/normalised address
postcodetextYES—
wardtextYES—Electoral ward
areatextYES—Neighbourhood/area
geomgeometry(Point,4326)YES—PostGIS point (lat/lon)
created_attimestamptzNOT NULLnow()
updated_attimestamptzNOT NULLnow()

Indexes:

  • locations_pkey — PRIMARY KEY btree (id)
  • idx_locations_address_trgm — gin (address_normalised gin_trgm_ops) — fuzzy text search
  • idx_locations_geom — gist (geom) — spatial index
  • idx_locations_postcode — btree (postcode)

Referenced by:

  • households.location_id → locations(id)
  • organisations.location_id → locations(id)

Sync notes:

  • address_raw — may contain identifiable data, sync normalised only
  • address_normalised — sync to Twenty location/address fields
  • postcode — sync (useful for CRM filtering)
  • ward — sync (useful for CRM filtering/territory management)
  • area — sync
  • geom — Twenty may not support geometry natively; sync lat/lon as separate fields or skip

Table: fieldwork.organisations​

Purpose: Organisations linked to locations and contact people.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
nametextNOT NULL—Organisation name
typetextYES—e.g. charity, council, business, faith group
location_iduuidYES—FK → fieldwork.locations(id)
contact_person_iduuidYES—FK → fieldwork.people(id)
notestextYES—
created_attimestamptzNOT NULLnow()
updated_attimestamptzNOT NULLnow()

Indexes:

  • organisations_pkey — PRIMARY KEY btree (id)

Sync notes:

  • Maps to Twenty Companies
  • type — may need normalisation to Twenty company type picklist
  • contact_person_id — link to Twenty Person as point of contact
  • notes — may contain raw text

Table: fieldwork.resources​

Purpose: Extracted resources/offers linked to people. Generated by LLM extraction pipeline.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
person_iduuidYES—FK → fieldwork.people(id) ON DELETE CASCADE
resourcetextNOT NULL—The resource/offer text
confidencetextYES'inferred'inferred / confirmed / reviewed
source_jobuuidYES—FK → fieldwork.transcription_jobs(job_id)
created_attimestamptzNOT NULLnow()

Indexes:

  • resources_pkey — PRIMARY KEY btree (id)

Sync notes:

  • Maps to Twenty Offers / Resources
  • confidence = 'inferred' — flag as unverified
  • confidence = 'reviewed' — safe to display
  • source_job — audit only

Table: fieldwork.skills​

Purpose: Extracted skills linked to people. Generated by LLM extraction pipeline.

ColumnTypeNullableDefaultNotes
iduuidNOT NULLuuid_generate_v4()PK
person_iduuidYES—FK → fieldwork.people(id) ON DELETE CASCADE
skilltextNOT NULL—The skill text
confidencetextYES'inferred'inferred / confirmed / reviewed
source_jobuuidYES—FK → fieldwork.transcription_jobs(job_id)
created_attimestamptzNOT NULLnow()

Indexes:

  • skills_pkey — PRIMARY KEY btree (id)

Sync notes:

  • Maps to Twenty Offers / Skills
  • Same confidence handling as resources
  • source_job — audit only

Entity Relationship Summary​

locations ──┬── households ──┬── people ──┬── concerns
│ │ ├── demographics (1:1)
│ │ ├── resources
│ │ ├── skills
│ │ └── organisations (as contact_person)
│ │
│ └── doorstep_contacts ─── transcription_jobs
│ (person_id, volunteer_id → people)
│
└── organisations

Fields That Must NOT Sync to Twenty​

TableColumnReason
doorstep_contactsraw_transcriptRaw STT output, may contain sensitive/personal data
transcription_jobsaudio_dataBinary audio, never expose
transcription_jobstranscriptRaw transcript
volunteer_voice_submissionstelegram_user_idExternal platform ID
volunteer_voice_submissionschat_idExternal platform ID
volunteer_voice_submissionsmessage_idExternal platform ID
demographicsage_band, gender, ethnicity, disabilitySpecial category GDPR data — restricted access only
peopletelegram_idExternal platform ID

Fields That Should Sync Only as Summary/Cleaned Text​

TableColumnTreatment
doorstep_contactsllm_summarySync as-is (already cleaned by LLM)
doorstep_contactsllm_extractedParse jsonb, sync structured fields
peoplenotesReview before sync or sync only if marked reviewed
householdsnotesReview before sync or sync only if marked reviewed
organisationsnotesReview before sync or sync only if marked reviewed
locationsaddress_rawSync address_normalised instead

Mapping Gaps​

  1. No review/approval flag on extracted data — concerns, resources, skills have confidence field but no explicit "reviewed_by" or "approved_at" timestamp. Consider adding before syncing to CRM.

  2. No explicit consent record table — consent is tracked as a status on people but there is no audit log of consent changes (who, when, what version of consent text). May need a consent_records table for full GDPR compliance.

  3. No task/follow-up table — doorstep_contacts has follow_up boolean and follow_up_notes but no dedicated tasks table with assignee, due date, status. Twenty tasks would need to be created from these flags.

  4. Free-text role on people — no controlled vocabulary. May need normalisation mapping (e.g. "resident", "volunteer", "staff", "trustee").

  5. Free-text category on concerns — no controlled vocabulary. May need normalisation mapping.

  6. Free-text type on organisations — no controlled vocabulary. May need normalisation mapping.

  7. No external_id column on Fieldwork tables — the sync worker will need to track which Fieldwork records have been synced to Twenty. Options: add twenty_id column to Fieldwork tables, or maintain a separate sync_state table.

  8. PostGIS geometry — Twenty does not natively support geometry. Lat/lon would need to be extracted from geom if spatial data is needed in CRM.

  9. doorstep_contacts has two FK paths to transcription_jobs — job_id (legacy) and v2_contact_id (v2). Sync worker needs to handle both.

  10. No soft-delete or archival — all tables use hard deletes (ON DELETE CASCADE). Sync worker needs to handle missing records gracefully.