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

Enum	Values
fieldwork.consent_status	not_asked, granted, refused, withdrawn
fieldwork.job_status	queued, processing, completed, failed
fieldwork.input_source	telegram_voice, web_upload, ocr_image
fieldwork.contact_outcome	unknown, 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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
household_id	uuid	YES	—	FK → fieldwork.households(id)
name_given	text	YES	—	First name
name_family	text	YES	—	Last name
telegram_id	text	YES	—	External ID from Telegram
email	text	YES	—
phone	text	YES	—
role	text	YES	—	e.g. resident, volunteer, staff
notes	text	YES	—	Free-text notes
consent_status	fieldwork.consent_status	NOT NULL	'not_asked'	GDPR consent tracking
consent_updated_at	timestamptz	YES	—	When consent last changed
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
location_id	uuid	YES	—	FK → fieldwork.locations(id)
name	text	YES	—	Household label
notes	text	YES	—	Free-text notes
last_contact	timestamptz	YES	—	Most recent doorstep contact
contact_count	integer	NOT NULL	0	Aggregate count of contacts
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
job_id	uuid	YES	—	FK → fieldwork.transcription_jobs(job_id)
household_id	uuid	YES	—	FK → fieldwork.households(id)
person_id	uuid	YES	—	FK → fieldwork.people(id) — who was spoken to
volunteer_id	uuid	YES	—	FK → fieldwork.people(id) — who did the contact
outcome	fieldwork.contact_outcome	NOT NULL	'unknown'	engaged/declined/not_home/follow_up
input_source	fieldwork.input_source	NOT NULL	'telegram_voice'	How the contact was captured
raw_transcript	text	YES	—	DO NOT SYNC — raw STT output
llm_summary	text	YES	—	Cleaned summary — sync this
llm_extracted	jsonb	YES	'{}'	Structured extraction — sync this
follow_up	boolean	YES	false	Flag for follow-up needed
follow_up_notes	text	YES	—	Notes on follow-up required
captured_at	timestamptz	NOT NULL	now()	When the contact happened
created_at	timestamptz	NOT NULL	now()	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.

Column	Type	Nullable	Default	Notes
job_id	uuid	NOT NULL	uuid_generate_v4()	PK
status	fieldwork.job_status	NOT NULL	'queued'	Pipeline status
source_project	text	NOT NULL	'fieldwork'	Project identifier
reference_id	text	YES	—	External reference
input_source	fieldwork.input_source	NOT NULL	'telegram_voice'
filename	text	YES	—	Original filename
language	text	YES	—	Detected language
language_prob	numeric(5,4)	YES	—	Detection confidence
duration_secs	numeric(8,3)	YES	—	Audio duration
transcript	text	YES	—	Raw transcript — DO NOT SYNC
segments	jsonb	YES	—	Timestamped segments
error	text	YES	—	Error message if failed
doorstep_contact_id	uuid	YES	—	FK → fieldwork.doorstep_contacts(id)
created_at	timestamptz	NOT NULL	now()
completed_at	timestamptz	YES	—
audio_data	bytea	YES	—	NEVER sync — raw audio binary
v2_contact_id	uuid	YES	—	FK → fieldwork.doorstep_contacts(id)
v2_error	text	YES	—	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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
job_id	text	YES	—	Links to transcription job
telegram_user_id	bigint	NOT NULL	—	Telegram user ID
username	text	YES	—	Telegram @username
first_name	text	YES	—	Telegram first name
last_name	text	YES	—	Telegram last name
chat_id	bigint	NOT NULL	—	Telegram chat ID
message_id	bigint	NOT NULL	—	Telegram message ID
audio_duration	integer	YES	—	Duration in seconds
created_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
person_id	uuid	YES	—	FK → fieldwork.people(id) ON DELETE CASCADE
concern	text	NOT NULL	—	The concern text
category	text	YES	—	e.g. housing, health, finance, isolation
confidence	text	YES	'inferred'	inferred / confirmed / reviewed
source_job	uuid	YES	—	FK → fieldwork.transcription_jobs(job_id)
created_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
person_id	uuid	YES	—	FK → fieldwork.people(id) ON DELETE CASCADE, UNIQUE
age_band	text	YES	—	e.g. 18-24, 25-34, 65+
gender	text	YES	—
ethnicity	text	YES	—
housing_tenure	text	YES	—	e.g. owner, social rent, private rent
employment_status	text	YES	—
has_dependants	boolean	YES	—
disability	boolean	YES	—
area	text	YES	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
address_raw	text	YES	—	Original address text
address_normalised	text	YES	—	Cleaned/normalised address
postcode	text	YES	—
ward	text	YES	—	Electoral ward
area	text	YES	—	Neighbourhood/area
geom	geometry(Point,4326)	YES	—	PostGIS point (lat/lon)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
name	text	NOT NULL	—	Organisation name
type	text	YES	—	e.g. charity, council, business, faith group
location_id	uuid	YES	—	FK → fieldwork.locations(id)
contact_person_id	uuid	YES	—	FK → fieldwork.people(id)
notes	text	YES	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
person_id	uuid	YES	—	FK → fieldwork.people(id) ON DELETE CASCADE
resource	text	NOT NULL	—	The resource/offer text
confidence	text	YES	'inferred'	inferred / confirmed / reviewed
source_job	uuid	YES	—	FK → fieldwork.transcription_jobs(job_id)
created_at	timestamptz	NOT NULL	now()

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.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	uuid_generate_v4()	PK
person_id	uuid	YES	—	FK → fieldwork.people(id) ON DELETE CASCADE
skill	text	NOT NULL	—	The skill text
confidence	text	YES	'inferred'	inferred / confirmed / reviewed
source_job	uuid	YES	—	FK → fieldwork.transcription_jobs(job_id)
created_at	timestamptz	NOT NULL	now()

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

Table	Column	Reason
doorstep_contacts	raw_transcript	Raw STT output, may contain sensitive/personal data
transcription_jobs	audio_data	Binary audio, never expose
transcription_jobs	transcript	Raw transcript
volunteer_voice_submissions	telegram_user_id	External platform ID
volunteer_voice_submissions	chat_id	External platform ID
volunteer_voice_submissions	message_id	External platform ID
demographics	age_band, gender, ethnicity, disability	Special category GDPR data — restricted access only
people	telegram_id	External platform ID

Fields That Should Sync Only as Summary/Cleaned Text

Table	Column	Treatment
doorstep_contacts	llm_summary	Sync as-is (already cleaned by LLM)
doorstep_contacts	llm_extracted	Parse jsonb, sync structured fields
people	notes	Review before sync or sync only if marked reviewed
households	notes	Review before sync or sync only if marked reviewed
organisations	notes	Review before sync or sync only if marked reviewed
locations	address_raw	Sync 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.