Data Model

Core entities and their relationships. PostgreSQL via Drizzle ORM.

Entity Relationship

User (1) ──── (many) Collection
                       │
              ┌────────┼────────┐
              │        │        │
        Invitation  Response  Synthesis
         (many)     (many)    (many versions)
              │        │
              └────┬───┘
                   │
            Response links to
            Invitation (nullable)

Entities

User

The authenticated creator. Links to Clerk via auth_provider_id.

Column	Type	Notes
`id`	UUID	Primary key
`auth_provider_id`	string	Clerk user ID
`status`	enum	`active` · `unverified` · `suspended` · `deleted`
`email`	string	Unique
`display_name`	string
`avatar_url`	string?	From social login or custom upload
`company_name`	string?	For report branding
`company_logo_url`	string?	GCS URL
`timezone`	string	IANA timezone, auto-detected at signup
`notification_preferences`	JSON	Email notification toggles
`plan`	enum	`free` · `starter` · `professional` · `enterprise`
`stripe_customer_id`	string?
`created_at`	timestamp
`updated_at`	timestamp
`deleted_at`	timestamp?	Soft delete, 30-day recovery window

Collection

Top-level entity for one knowledge extraction exercise.

Column	Type	Notes
`id`	UUID	Primary key
`creator_id`	UUID	FK → User
`status`	enum	`draft` · `collecting` · `synthesising` · `complete` · `closed`
`title`	string
`purpose`	text	"What are you trying to find out?"
`motivation`	text	"Why does this matter?"
`audience_description`	text	"Who are you asking?"
`report_audience`	text	"Who will read this report?"
`distribution_mode`	enum	`open_link` · `invite` · `both`
`deadline`	timestamp?
`min_responses`	int	Default: 3
`auto_synthesise`	boolean
`respondent_thank_you`	text?	Custom completion message
`anonymous`	boolean	For open link mode
`response_cap`	int?	Max open link responses
`password_hash`	string?	Optional questionnaire password
`created_at`	timestamp
`updated_at`	timestamp
`closed_at`	timestamp?

Invitation

Individual email invitations with delivery and completion tracking.

Column	Type	Notes
`id`	UUID	Primary key
`collection_id`	UUID	FK → Collection
`email`	string
`name`	string?
`role`	string?	e.g. "peer", "manager", "direct report"
`unique_token`	string	URL-safe, used in invite link
`status`	enum	`pending` · `sent` · `opened` · `started` · `completed`
`sent_at`	timestamp?
`opened_at`	timestamp?
`reminded_at`	timestamp?
`completed_at`	timestamp?

Response

A completed questionnaire submission.

Column	Type	Notes
`id`	UUID	Primary key
`collection_id`	UUID	FK → Collection
`invitation_id`	UUID?	FK → Invitation (null for open link)
`respondent_name`	string?
`respondent_email`	string?
`source`	enum	`open_link` · `invite`
`role`	string?
`form_data`	JSON	The actual answers
`status`	enum	`in_progress` · `completed`
`started_at`	timestamp
`completed_at`	timestamp?

Synthesis

AI-generated report. Versioned — a collection may have multiple.

Column	Type	Notes
`id`	UUID	Primary key
`collection_id`	UUID	FK → Collection
`version`	int	Incremented on regeneration
`content`	JSON	Structured AI output
`rendered_html`	text
`rendered_pdf_url`	string?	GCS URL
`prompt_snapshot`	text	Full prompt sent to Claude (for debugging)
`focus_instructions`	text?	Creator guidance for regeneration
`response_count`	int	Responses included in this synthesis
`generated_at`	timestamp

Design Notes

Organisation (Post-MVP)

The User table should include a nullable organisation_id FK from day one, even though the Organisation entity won't be built until post-MVP. This avoids a migration headache later.

Respondent Identity Linking

If a respondent later signs up as a creator with the same email, their previous identified responses are associated with the new account. Anonymous responses are never retroactively linked. Anonymity guarantees are sacrosanct.

Soft Deletes

User deletion is a soft delete with a 30-day recovery window. After 30 days, cascading hard delete removes all collections, responses, invitations, and syntheses. Financial records required by law are retained separately.

Tech Stack Index