Database (Import & Survey Metadata Cache)

Two tables covering candidate import history and the survey metadata cache.

Answer data is not stored in Postgres

Survey answers are read straight from Databricks Unity Catalog (cs.cs_dm.dm_answers_<survey_id>). The only thing Postgres holds is databricks_surveys, which backs the survey list. See Databricks integration.

The metadata cache is not wired to application tables

databricks_surveys caches external data and holds no foreign keys to projects or anything else. The only link is the logical one through the string stored in projects.survey_id.

---

import_logs

Overview

Execution history of TSV / survey imports, including a breakdown of counts and per-row errors.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Import log	import_logs	id	integer	✓				auto-increment
		project_id	integer		projects:id (cascade)
		import_type	import_type						manual_tsv / manual_survey / auto_survey
		survey_id	varchar(50)				✓		Target survey for survey imports
		status	import_status					'pending'	pending / processing / completed / partial / failed
		imported_count	integer					0	Rows imported successfully
		skipped_count	integer					0	Rows skipped (e.g. by filter)
		error_count	integer					0	Rows that errored
		duplicate_count	integer					0	Rows skipped as duplicates
		error_details	json				✓		ImportError[] type
		payload	json				✓		The request that ran the import (mapping, filter)
		source_file_timestamp	timestamp				✓		Left over from the S3 CSV era; no longer written
		started_at	timestamp				✓		Processing start
		completed_at	timestamp				✓		Processing end
		created_at	timestamp					now()	Row creation

Relations

• project_id → projects.id (onDelete: cascade)

Indexes

• Primary key (id)
• INDEX: idx_il_project_id (project_id)

Notes

• Imports are asynchronous. The API creates the row as pending / processing and returns immediately; progress is polled through GET /projects/{id}/candidates/import/{importId}/status
• The work itself runs as an ECS Fargate task (src/batch/import.ts)
• A partially successful run ends as partial
• payload keeps the mapping and filter verbatim, so the same conditions can be reproduced later
• The import_type values were manual_s3 / auto_s3 during the S3 integration era. Existing rows were migrated with ALTER TYPE ... RENAME VALUE

---

databricks_surveys

Overview

Cache of survey metadata. src/batch/databricks-meta-sync.ts aggregates cs.cs_dwh in Databricks and refreshes this table daily.

The aggregation scans roughly 68M answer rows and takes about 15 seconds, which is far too slow to run per request, so it is materialised here. The survey list and detail screens read only this table.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Survey metadata	databricks_surveys	id	integer	✓				auto-increment
		survey_id	varchar(50)			✓			External survey ID
		survey_name	varchar(500)						Survey name
		collector_name	varchar(500)				✓		Collector name
		total_respondents	integer					0	Total respondents
		completed_respondents	integer					0	Respondents who completed
		question_count	integer					0	Number of questions
		earliest_response_at	timestamp				✓		First response
		latest_response_at	timestamp				✓		Last response
		synced_at	timestamp						Last refresh

Indexes

• Primary key (id)
• UNIQUE: survey_id
• INDEX: idx_dbs_synced_at (synced_at)

Notes

• projects.survey_id points at this survey_id, but there is no foreign key
• The statistics (total_respondents and friends) are a snapshot from the last refresh
• The refresh is an upsert: rows for surveys that disappear from Databricks are kept
• The per-survey endpoints (/api/v1/surveys/{surveyId} and below) use the presence of a row here to decide on a 404, so that an unknown ID does not turn into a Databricks "table does not exist" error (502)
• This table is the renamed s3_surveys from the S3 integration era; its columns are unchanged

---

Removed Tables

The three tables that cached S3 CSVs in Postgres have been dropped.

Former table	Why it went away
s3_answers	Cached the answers themselves. Superseded by reading dm_answers_<survey_id> in Databricks directly
s3_panels	Cached respondent information. Databricks already joins it onto the answers
s3_sync_logs	Execution history of the sync batch, which no longer exists

The related enums s3_sync_status / s3_sync_type were dropped as well.