Database - Done

55 min

Overview

This outlines a proposed database structure for the 360Pulse application. The schema described below is derived from the UI design and features (including Pulse Score calculation, multi-language support, plan tiers). It aims to provide a comprehensive foundation for storing the data needed to support the functionality.

Considerations

Outline Nature:

Please consider this schema an initial outline based on the information available. As development progresses, details may need refinement, optimization based on query patterns, or adjustments based on specific framework choices or performance testing.

Framework (Users, Roles, Billing):

This document includes tables for core concepts like Users, Roles, WorkspaceMembers, Plans, and WorkspaceSubscriptions. Our framework already provides some or all of that functionality. I included the tables here to illustrate how inter-table relationships would work, and help explain how the overall application should function. These should be viewed as a guidelines representing the types of data and relationships required by the application's features (e.g., linking users to workspaces, defining plan features, gating access).

Data Types & Constraints:

Specific data types (VARCHAR lengths, ENUM values, etc.) and constraints (indexes, precise foreign key relationships) provided are illustrative and should be finalized during detailed design and implementation according to the chosen database system and best practices.

Database Tables

Workspaces

Purpose: Represents a top-level customer account, typically a company or organization using 360Pulse. It acts as a container for users, PulseChecks, contacts, and billing information.
Usage: Used to isolate data between different customers. All major entities like PulseChecks, Contacts, Tags, etc., are linked back to a workspace_id.
Example Row:
- workspace_id: ws_abc123
- workspace_name: Example Corp
- owner_user_id: user_xyz789
- created_at: 2025-01-15 10:00:00
- updated_at: 2025-03-20 11:30:00

Table Structure

Column Name	Data Type	Notes
workspace_id	INT / UUID	PK
workspace_name	VARCHAR
owner_user_id	INT / UUID	FK to Users (Optional V1)
created_at	TIMESTAMP
updated_at	TIMESTAMP

Users

Purpose: Stores login credentials and basic profile information for individuals who can access the 360Pulse application.
Usage: Used for authenticating users into the admin interface. Linked to Workspaces via WorkspaceMembers.
Example Row:
- user_id: user_xyz789
- email: [email protected]
- password_hash: [hashed_password_string]
- first_name: Admin
- last_name: User
- is_active: TRUE
- last_login_at: 2025-04-07 09:30:00
- created_at: 2025-01-15 09:55:00
- updated_at: 2025-04-07 09:30:0

Table Structure

Column Name	Data Type	Notes
user_id	INT / UUID	PK
email	VARCHAR	UNIQUE
password_hash	VARCHAR
first_name	VARCHAR	Nullable
last_name	VARCHAR	Nullable
is_active	BOOLEAN	Default: TRUE
last_login_at	TIMESTAMP	Nullable
created_at	TIMESTAMP
updated_at	TIMESTAMP

Roles

3. Roles

Purpose: Defines the different permission levels available within the system. (For V1, this might only contain a single 'Admin' role).
Usage: Assigned to users within a specific workspace via the WorkspaceMembers table to control access in future versions.
Example Row:
- role_id: 1
- role_name: Admin
- description: Full access to all workspace features.

Table Structure

Column Name	Data Type	Notes
role_id	INT / SERIAL	PK
role_name	VARCHAR	UNIQUE, e.g., 'Admin'
description	TEXT	Nullable

WorkspaceMembers

Purpose: Links users from the Users table to specific Workspaces and assigns them a Role. This defines who can access which workspace and their permission level within it.
Usage: Checked upon login and during actions to verify user access to a workspace and potentially features (based on role in future).
Example Row:
- member_id: wsm_pqr456
- workspace_id: ws_abc123
- user_id: user_xyz789
- role_id: 1 (Admin)
- joined_at: 2025-01-15 10:05:00

Table Structure

Column Name	Data Type	Notes
member_id	INT / UUID	PK
workspace_id	INT / UUID	FK to Workspaces
user_id	INT / UUID	FK to Users
role_id	INT	FK to Roles
joined_at	TIMESTAMP
Constraint		UNIQUE(workspace_id, user_id)

Plans

Purpose: Defines the available subscription tiers (Free, Standard, Pro) and their associated features, limits, and pricing.
Usage: Referenced by WorkspaceSubscriptions and PulseChecks to determine feature availability (like multi-language, white-labeling) and enforce limits (contact counts).
Example Row (Pro Plan):
- plan_id: 3
- plan_name: Pro
- contact_limit: 500 (Base limit)
- allows_overages: TRUE
- overage_bundle_size: 100
- overage_bundle_cost: 10.00
- multi_language_enabled: TRUE
- allow_white_label: TRUE
- base_price_monthly: 99.00
- base_price_annually: 999.00

Table Structure

Column Name	Data Type	Notes
plan_id	INT / SERIAL	PK
plan_name	VARCHAR	e.g., 'Free', 'Standard', 'Pro'
contact_limit	INT	Nullable (for base Pro limit)
allows_overages	BOOLEAN
overage_bundle_size	INT	Nullable
overage_bundle_cost	DECIMAL	Nullable
multi_language_enabled	BOOLEAN
allow_white_label	BOOLEAN
base_price_monthly	DECIMAL
base_price_annually	DECIMAL

WorkspaceSubscriptions

Purpose: Tracks which plan a specific workspace is currently subscribed to, including the billing cycle and status.
Usage: Determines the active plan for a workspace, used for feature gating, limit checking, and triggering billing events. Tracks purchased overage bundles for the current period.
Example Row:
- subscription_id: sub_def456
- workspace_id: ws_abc123
- plan_id: 3 (Pro)
- status: Active
- billing_cycle: Monthly
- current_period_end: 2025-05-15
- purchased_overage_bundles: 1
- started_at: 2025-01-15 10:10:00
- cancelled_at: NULL

Table Structure

Column Name	Data Type	Notes
subscription_id	INT / UUID	PK
workspace_id	INT / UUID	FK to Workspaces
plan_id	INT	FK to Plans
status	ENUM / VARCHAR	e.g., 'Active', 'Cancelled'
billing_cycle	ENUM / VARCHAR	e.g., 'Monthly', 'Annually'
current_period_end	DATE
purchased_overage_bundles	INT	Default: 0
started_at	TIMESTAMP
cancelled_at	TIMESTAMP	Nullable

PulseChecks

Purpose: Defines a specific survey configuration, including its name, associated questions, schedule, email templates, branding, thank-you page logic, and other settings. Belongs to a Workspace.
Usage: The central definition for a survey campaign. Referenced when scheduling sends, rendering surveys, applying settings, and displaying dashboards. The active Plan features/limits apply here.
Example Row:
- pulsecheck_id: pc_jkl789
- workspace_id: ws_abc123
- name: Client Happiness
- introduction_text: Welcome! Please share your feedback.
- reference_group: Customers
- company_category: Primary Brand
- vertical_market: SaaS
- nps_question_wording: On a scale of 0-10, how likely are you to recommend Example Corp?
- frequency: Monthly
- schedule_start_date: 2025-02-01
- schedule_details: {"type": "Relative", "occurrence": "First", "day_type": "Monday"}
- use_comment_sentiment: TRUE
- collect_testimonials: TRUE
- require_testimonial_opt_in: TRUE
- typage_settings: {"high_heading": "Thanks!", "high_message": "We appreciate it...", ...}
- branding_settings: {"logo_url": "/logos/example.png", "color_accent": "#007bff", ...}
- remove_branding: TRUE (Assuming Pro Plan)
- is_active: TRUE
- created_at: 2025-01-20 14:00:00
- updated_at: 2025-04-04 17:10:00

Table Structure

Column Name	Data Type	Notes
pulsecheck_id	INT / UUID	PK
workspace_id	INT / UUID	FK to Workspaces
name	VARCHAR
introduction_text	TEXT	Nullable
reference_group	VARCHAR	Nullable
company_category	VARCHAR	Nullable
vertical_market	VARCHAR	Nullable
nps_question_wording	TEXT
frequency	ENUM / VARCHAR
schedule_start_date	DATE	Nullable
schedule_details	JSON / VARCHAR	Stores relative/specific rules
use_comment_sentiment	BOOLEAN	Default: TRUE
collect_testimonials	BOOLEAN	Default: TRUE
require_testimonial_opt_in	BOOLEAN	Default: TRUE
typage_settings	JSON	Nullable, Stores TY page content
branding_settings	JSON	Nullable, Stores design elements
remove_branding	BOOLEAN	Default: FALSE
is_active	BOOLEAN	Default: TRUE
created_at	TIMESTAMP
updated_at	TIMESTAMP

Questions

Purpose: Stores the definition of individual questions used within PulseChecks, including the default text, type (SmileyScale or NPS), sorting order, and area/category.
Usage: Linked to a PulseCheck. Displayed to users during survey completion. Translations are stored separately.
Example Row:
- question_id: q_101
- pulsecheck_id: pc_jkl789
- question_text_default: How satisfied are you with our support responsiveness?
- area: Support
- question_type: SmileyScale
- sort_order: 1
- is_active: TRUE
- created_at: 2025-01-20 14:05:00
- updated_at: 2025-01-20 14:05:00

Table Structure

Column Name	Data Type	Notes
question_id	INT / SERIAL	PK
pulsecheck_id	INT / UUID	FK to PulseChecks
question_text_default	TEXT	Source language text
area	VARCHAR	Short name / category
question_type	ENUM / VARCHAR	'SmileyScale', 'NPS'
sort_order	INT
is_active	BOOLEAN	Default: TRUE
created_at	TIMESTAMP
updated_at	TIMESTAMP

QuestionTranslations

Purpose: Stores the translated text for questions and their areas/short names in various languages.
Usage: Looked up when rendering a survey for a user whose browser language (and the PulseCheck's plan) supports multi-language. If no translation exists for the target language, the default from the Questions table is used.
Example Row:
- translation_id: qt_201
- question_id: q_101
- language_code: es
- translated_text: ¿Qué tan satisfecho está con la capacidad de respuesta de nuestro soporte?
- translated_area: Soporte
- last_updated_at: 2025-01-21 10:00:00

Table Structure

Column Name	Data Type	Notes
translation_id	INT / SERIAL	PK
question_id	INT	FK to Questions
language_code	VARCHAR(5)	e.g., 'en', 'es'
translated_text	TEXT
translated_area	VARCHAR	Translated short name
last_updated_at	TIMESTAMP
Constraint		UNIQUE(question_id, language_code)

EmailTemplates

Purpose: Defines a specific email template instance (Initial, Reminder1, or Reminder2) associated with a particular PulseCheck. It primarily serves as a linking record to find the relevant default system text (from SystemTextSnippets) and any user-provided customizations/overrides (from EmailTemplateContent).
Usage: Created automatically (three records per PulseCheck: Initial, Reminder1, Reminder2) when a new PulseCheck is defined. It's used during email sending to identify the correct set of content to assemble. The source_language_code field tracks the language the user last edited the template in, which is used as the source for triggering auto-translations. The last_updated_at field reflects when any customization related to this template was last saved. User-editable fields like from_name (if applicable) are stored here.
Schema:
Example Row:
- template_id: et_301
- pulsecheck_id: pc_jkl789
- email_type: Initial
- from_name: Example Corp Support (User customized this)
- source_language_code: en
- last_updated_at: 2025-04-07 10:40:00

Logic

This section details the logic for managing email templates, storing system defaults centrally and user customizations separately within the database.

Storage Strategy:
- System Defaults: All default text snippets (for both customizable fields like 'Subject' and fixed fields like 'Take the Survey' button label) are stored centrally in the SystemTextSnippets table, keyed by a unique text_key and language_code. This table is seeded during setup and serves as the master source for default content.
- Template Definition: The EmailTemplates table defines a template instance for a specific pulsecheck_id and email_type. It stores minimal configuration like the customizable from_name and tracks the source_language_code of the last user edit.
- User Customizations: Specific overrides provided by users for editable fields (Subject, Preview, Body Before/After) are stored separately in the EmailTemplateContent table. Each record links to an EmailTemplates instance (template_id) and language_code. A record only exists here if the user has customized at least one editable field for that specific template/language combination.
User Customization & Auto-Translation Workflow:
1. Editing: User modifies content (e.g., Subject in 'en').
2. Saving: An EmailTemplateContent record for the template_id and language_code ('en') is created or updated, storing only the modified field values. EmailTemplates.last_updated_at and EmailTemplates.source_language_code are updated.
3. Triggering Auto-Translation (Pro Plan): If applicable, translates only the modified fields from the source language into other supported languages.
4. Saving Translations: Creates/updates EmailTemplateContent records for other languages, populating only the corresponding auto-translated fields. EmailTemplateContent.last_edited_at is updated.
"Revert to Default" Action:
- When invoked for a template (template_id), all associated records in EmailTemplateContent for that template_id (across all languages) are deleted. This causes the sending logic to fully rely on SystemTextSnippets for this template.
Email Sending Logic:
1. Determine the target language_code.
2. Find the relevant template_id.
3. Fetch Customizations (Attempt): Try to fetch the EmailTemplateContent record matching template_id and language_code. Store any non-NULL values found (these are the overrides).
4. Fetch ALL Relevant Defaults: Fetch the necessary default text snippets from SystemTextSnippets for the target language_code using predefined text_keys (e.g., keys for default subject, default body1, button label, infobox titles, etc.).
5. Assemble Content: For each piece of content needed in the email:
  - Customizable Fields (Subject, Preview, Body1, Body2): Use the value from the fetched EmailTemplateContent record if it exists and the specific field is not NULL; otherwise, use the corresponding default value fetched from SystemTextSnippets.
  - Fixed Structural Fields ("Take the Survey", etc.): Use the value fetched directly from SystemTextSnippets using its specific text_key.
  - From Name: Use EmailTemplates.from_name if not NULL, otherwise fetch the default 'From Name' from SystemTextSnippets using its key.
6. Perform variable substitutions on the assembled customizable parts (primarily Subject and Body fields).
7. Construct and send the final email.

Table Structure

Column Name	Data Type	Notes
template_id	INT / SERIAL	PK
pulsecheck_id	INT / UUID	FK to PulseChecks
email_type	ENUM / VARCHAR	'Initial', 'Reminder1', 'Reminder2'
from_name	VARCHAR	Nullable
source_language_code	VARCHAR(5)	Default: 'en'
last_updated_at	TIMESTAMP
Constraint		UNIQUE(pulsecheck_id, email_type)

EmailTemplateContent

Purpose: Stores user-defined customizations (overrides) for the editable fields of an email template (Subject, Preview Text, Body Before/After Button) for a specific language. It also stores the auto-translated versions of these user customizations for other languages on Pro plans. System default text is stored separately in SystemTextSnippets.
Usage: This table is queried during email sending to check if a user has provided custom text for the recipient's language preference for a specific template. If a record exists for the template_id and language_code, the non-NULL values from this record are used instead of the system defaults (retrieved from SystemTextSnippets). A record only exists here if a user has customized at least one editable field for the specific template_id and language_code combination. Auto-translation processes also create/update records here. The "Revert to Default" action deletes records from this table for the relevant template_id.
Schema:
Example Row (User customized Subject and Body 1 in English):
- content_id: etc_401
- template_id: et_301
- language_code: en
- subject: A Custom Subject Line!
- preview_text: NULL (User didn't customize this part)
- body_before_button: Here is my custom intro text...
- body_after_button: NULL (User didn't customize this part)
- last_edited_at: 2025-04-07 10:40:00
Example Row (Auto-translation of the above into Spanish):
- content_id: etc_402
- template_id: et_301
- language_code: es
- subject: ¡Una línea de asunto personalizada!
- preview_text: NULL
- body_before_button: Aquí está mi texto de introducción personalizado...
- body_after_button: NULL
- last_edited_at: 2025-04-07 10:41:00 (Timestamp of auto-translation)

Table Structure

Column Name	Data Type	Notes
content_id	INT / SERIAL	PK
template_id	INT	FK to EmailTemplates
language_code	VARCHAR(5)
subject	VARCHAR
preview_text	VARCHAR	Nullable
body_before_button	TEXT
body_after_button	TEXT
is_custom	BOOLEAN	TRUE if user edited this lang directly
last_translated_at	TIMESTAMP	Nullable, Tracks auto-translation time
Constraint		UNIQUE(template_id, language_code)

Contacts

Purpose: Stores information about the individuals (audience) who can receive PulseCheck surveys. Belongs to a Workspace.
Usage: Central repository for audience data. Used for sending surveys, assigning tags, tracking status (Active, Unsubscribed, Bounced), and displaying contact lists/details. Email uniqueness within a workspace is important.
Example Row:
- contact_id: con_mno567
- workspace_id: ws_abc123
- first_name: Jane
- last_name: Doe
- email: [email protected]
- job_title: Customer Success Manager
- avatar_url: /avatars/jane.jpg
- status: Active
- is_bounced: FALSE
- language_preference: en
- created_at: 2025-02-10 11:00:00
- updated_at: 2025-03-15 12:00:00

Table Structure

Column Name	Data Type	Notes
contact_id	INT / UUID	PK
workspace_id	INT / UUID	FK to Workspaces
first_name	VARCHAR
last_name	VARCHAR	Nullable
email	VARCHAR
job_title	VARCHAR	Nullable
avatar_url	VARCHAR	Nullable
status	ENUM / VARCHAR	'Active', 'Unsubscribed', 'Bounced'
is_bounced	BOOLEAN	Default: FALSE
language_preference	VARCHAR(5)	Nullable, For emails
created_at	TIMESTAMP
updated_at	TIMESTAMP
Constraint		UNIQUE(workspace_id, email)

Column Name	Data Type	Notes
tag_id	INT / SERIAL	PK
workspace_id	INT / UUID	FK to Workspaces
tag_name	VARCHAR
description	TEXT	Nullable
tag_type	ENUM / VARCHAR	'Text', 'Dropdown'
dropdown_options	JSON / TEXT	Nullable
is_filterable	BOOLEAN	Default: FALSE
created_at	TIMESTAMP
updated_at	TIMESTAMP
Constraint		UNIQUE(workspace_id, tag_name)

ContactTags

Purpose: Creates the many-to-many relationship between Contacts and Tags, storing the specific value assigned for a tag to a contact.
Usage: Queried when displaying tags on a contact's profile and crucially when filtering lists/dashboards based on tag values.
Example Row:
- contact_tag_id: ct_601
- contact_id: con_mno567
- tag_id: tag_51 (Region)
- tag_value: West
- assigned_at: 2025-02-10 11:05:00

Table Structure

Column Name	Data Type	Notes
contact_tag_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
tag_id	INT	FK to Tags
tag_value	VARCHAR	Text or selected dropdown value
assigned_at	TIMESTAMP
Constraint		UNIQUE(contact_id, tag_id)

ContactPulseCheckSubscriptions

Purpose: Tracks which contacts are subscribed to receive which specific PulseChecks. Manages the opt-in/out status per PulseCheck.
Usage: Determines the recipient list when a PulseCheck instance is scheduled to be sent. Used to check plan contact limits.
Example Row:
- subscription_id: cps_701
- contact_id: con_mno567
- pulsecheck_id: pc_jkl789
- is_subscribed: TRUE
- subscribed_at: 2025-02-10 11:10:00
- unsubscribed_at: NULL

Table Structure

Column Name	Data Type	Notes
subscription_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
pulsecheck_id	INT / UUID	FK to PulseChecks
is_subscribed	BOOLEAN	Default: TRUE
subscribed_at	TIMESTAMP
unsubscribed_at	TIMESTAMP	Nullable
Constraint		UNIQUE(contact_id, pulsecheck_id)

PulseCheckInstances

Purpose: Represents a specific instance of a PulseCheck being sent to a contact for a given period (e.g., the April 2025 instance of the "Client Happiness" survey for Jane Doe). Tracks the sending status and links to the responses.
Usage: Created when a scheduled PulseCheck runs. Status is updated based on email tracking and survey completion. Holds the calculated Pulse Score for this specific interaction. Central record for tracking individual survey progress.
Example Row (Completed):
- instance_id: pci_801
- contact_id: con_mno567
- pulsecheck_id: pc_jkl789
- period_name: April 2025
- sent_date: 2025-04-07 10:00:00
- completed_date: 2025-04-07 15:30:00
- status: Completed
- calculated_pulse_score: 85
- expires_at: 2025-04-21 10:00:0

Table Structure

Column Name	Data Type	Notes
instance_id	INT / UUID	PK
contact_id	INT / UUID	FK to Contacts
pulsecheck_id	INT / UUID	FK to PulseChecks
period_name	VARCHAR	e.g., 'April 2025'
sent_date	TIMESTAMP
completed_date	TIMESTAMP	Nullable
status	ENUM / VARCHAR	'Sent', 'Opened', ... 'Bounced'
calculated_pulse_score	INT / DECIMAL	Nullable, 0-100 score for instance
expires_at	TIMESTAMP	Nullable

Responses

Purpose: Stores the individual answers provided by a contact for each question within a specific PulseCheck instance. Holds the raw response, derived NPS status, comment text, and comment sentiment/score.
Usage: Primary source for calculating Pulse Scores, NPS scores, sentiment analysis, and displaying detailed results. Linked to a PulseCheckInstance.
Example Row (Smiley + Comment):
- response_id: resp_901
- instance_id: pci_801
- question_id: q_101 (Support Question)
- response_value: Happy
- nps_status: NULL
- comment_text: Support was very quick this time!
- comment_sentiment: Positive
- comment_sentiment_score: 92.50
- is_comment_read: FALSE
- response_language_code: en
- responded_at: 2025-04-07 15:29:00
Example Row (NPS):
- response_id: resp_902
- instance_id: pci_801
- question_id: q_102 (NPS Question ID)
- response_value: 9
- nps_status: Promoter
- comment_text: NULL
- comment_sentiment: NULL
- comment_sentiment_score: NULL
- is_comment_read: FALSE
- response_language_code: en
- responded_at: 2025-04-07 15:29:30

Table Structure

Column Name	Data Type	Notes
response_id	INT / SERIAL	PK
instance_id	INT / UUID	FK to PulseCheckInstances
question_id	INT	FK to Questions
response_value	VARCHAR	NPS score, Smiley value, etc.
nps_status	ENUM / VARCHAR	'Promoter', 'Passive', 'Detractor', Nullable
comment_text	TEXT	Nullable
comment_sentiment	ENUM / VARCHAR	'Positive', 'Negative', 'Neutral', Nullable
comment_sentiment_score	DECIMAL(5,2)	Nullable, 0-100 score
is_comment_read	BOOLEAN	Default: FALSE
response_language_code	VARCHAR(5)	Language survey was taken in
responded_at	TIMESTAMP

EmailTracking

Purpose: Provides more granular tracking of email interactions (opens, clicks) beyond the basic status in PulseCheckInstances. Might be useful for detailed delivery analysis but adds overhead.
Usage: Populated via webhooks or tracking pixels from the email sending service. Used for reporting on email engagement rates.
Example Row:
- tracking_id: etk_1001
- instance_id: pci_801
- email_type: Initial
- opened_at: 2025-04-07 11:15:00
- clicked_at: 2025-04-07 15:25:00

Table Structure

Column Name	Data Type	Notes
tracking_id	INT / SERIAL	PK
instance_id	INT / UUID	FK to PulseCheckInstances
email_type	ENUM / VARCHAR	'Initial', 'Reminder1', 'Reminder2'
opened_at	TIMESTAMP	Nullable
clicked_at	TIMESTAMP	Nullable

Bounces

Purpose: Logs email bounce events reported by the email sending service. Helps identify invalid email addresses or temporary delivery issues.
Usage: Populated by callbacks/webhooks from the email service. Used to update Contacts.status and Contacts.is_bounced flags. Provides data for admins to manage list hygiene.
Example Row (Hard Bounce):
- bounce_id: b_1101
- contact_id: con_pqr890
- instance_id: pci_805
- email_address: [email protected]
- bounce_type: Hard
- reason: 550 User unknown
- bounced_at: 2025-04-07 10:00:05

Table Structure

Column Name	Data Type	Notes
bounce_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
instance_id	INT / UUID	FK to PulseCheckInstances, Nullable
email_address	VARCHAR	Email at time of bounce
bounce_type	ENUM / VARCHAR	'Hard', 'Soft'
reason	TEXT	Nullable, From email service
bounced_at	TIMESTAMP

GeneralFeedback

Purpose: Stores feedback submitted via the post-survey thank-you page, not tied to a specific survey question.
Usage: Captures additional context or general comments after the main survey. Analyzed for sentiment. Displayed on the contact's Feedback tab.
Example Row:
- general_feedback_id: gf_1201
- instance_id: pci_801
- contact_id: con_mno567
- feedback_text: Overall a good experience this month.
- sentiment: Positive
- sentiment_score: 75.00
- is_read: FALSE
- submitted_at: 2025-04-07 15:31:00

Table Structure

Column Name	Data Type	Notes
general_feedback_id	INT / SERIAL	PK
instance_id	INT / UUID	FK to PulseCheckInstances
contact_id	INT / UUID	FK to Contacts
feedback_text	TEXT
sentiment	ENUM / VARCHAR	Nullable
sentiment_score	DECIMAL(5,2)	Nullable
is_read	BOOLEAN	Default: FALSE
submitted_at	TIMESTAMP

Testimonials

Purpose: Stores feedback explicitly approved (if opt-in required) or designated as a testimonial, usually gathered from highly satisfied respondents via the thank-you page.
Usage: Displayed in the Testimonials tab for a contact and potentially aggregated in a workspace-level testimonials view. is_approved_for_marketing flag can control external use.
Example Row:
- testimonial_id: test_1301
- contact_id: con_mno567
- instance_id: pci_801
- pulsecheck_id: pc_jkl789
- testimonial_text: Example Corp's support is fantastic!
- opt_in_granted: TRUE
- opt_in_text: I allow use of this testiminial (this will be the full message from the page)
- is_approved_for_marketing: FALSE (Requires review)
- created_at: 2025-04-07 15:32:00
- is_read: FALSE
- read_at: NULL

Table Structure

Column Name	Data Type	Notes
testimonial_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
instance_id	INT / UUID	FK to PulseCheckInstances
pulsecheck_id	INT / UUID	FK to PulseChecks
testimonial_text	TEXT
opt_in_granted	BOOLEAN
opt_in_text	TEXT
is_approved_for_marketing	BOOLEAN	Default: FALSE
created_at	TIMESTAMP
is_read	BOOLEAN	Default: FALSE
read_at	TIMESTAMP	Nullable

ReviewPlatforms

Purpose: Stores the configuration for external review site links (like Google, G2, Yelp) defined at the workspace level.
Usage: Populates the options presented on the "Review Tab" of the high-satisfaction thank-you page. is_enabled controls visibility. Stores specific identifiers like Google Place ID where needed.
Example Row (Google):
- platform_id: rp_1401
- workspace_id: ws_abc123
- platform_name: Google
- platform_logo_url: /logos/google.svg
- platform_url: https://search.google.com/local/writereview?placeid=ChEXAMPLEPLACEID
- google_place_id: ChEXAMPLEPLACEID
- is_enabled: TRUE
- created_at: 2025-02-01 10:00:00
- updated_at: 2025-02-01 10:00:00

Table Structure

Column Name	Data Type	Notes
platform_id	INT / SERIAL	PK
workspace_id	INT / UUID	FK to Workspaces
platform_name	VARCHAR	e.g., 'Google', 'G2'
platform_logo_url	VARCHAR	Nullable
platform_url	VARCHAR	URL for reviews
google_place_id	VARCHAR	Nullable, Specific to Google
is_enabled	BOOLEAN	Default: TRUE
created_at	TIMESTAMP
updated_at	TIMESTAMP

AdvocacyActions

Purpose: Tracks specific advocacy-related actions taken by contacts, primarily clicking on external review links from the thank-you page.
Usage: Populates the "Review Link Clicks" metric on dashboards. Provides data on which platforms users are engaging with.
Example Row:
- action_id: aa_1501
- contact_id: con_mno567
- instance_id: pci_801
- action_type: ReviewLinkClick
- platform_id: rp_1401 (Google)
- action_timestamp: 2025-04-07 15:33:00

Table Structure

Column Name	Data Type	Notes
action_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
instance_id	INT / UUID	FK to PulseCheckInstances
action_type	ENUM / VARCHAR	'ReviewLinkClick'
platform_id	INT	FK to ReviewPlatforms, Nullable
action_timestamp	TIMESTAMP

MonthlyContactMetrics

Purpose: Stores pre-calculated, aggregated metrics per contact per month. Designed to speed up dashboard loading for contacts and potentially overall reporting.
Usage: Populated by an automated routine (e.g., nightly or triggered on instance completion). Provides data for the contact overview dashboard tiles (Pulse Score, NPS) and potentially trend charts.
Example Row:
- metric_id: mcm_1601
- contact_id: con_mno567
- month: 4
- year: 2025
- average_pulse_score: 85
- average_nps_score: 9 (Avg 0-10 scale score)
- nps_status: Promoter
- last_updated: 2025-04-07 16:00:00

Table Structure

Column Name	Data Type	Notes
metric_id	INT / SERIAL	PK
contact_id	INT / UUID	FK to Contacts
month	INT	1-12
year	INT
average_pulse_score	INT / DECIMAL	Nullable, Aggregated score 0-100
average_nps_score	INT / DECIMAL	Nullable, Aggregated 0-10 score
nps_status	ENUM / VARCHAR	Nullable, Latest P/P/D status
last_updated	TIMESTAMP
Constraint		UNIQUE(contact_id, month, year)

SystemTextSnippets

Purpose: Acts as the central repository for all system default text snippets used within the application, primarily for email templates but potentially usable elsewhere. It stores the default versions for fields users can customize (like Subject, Body) as well as the fixed text for elements users cannot customize (like the "Take the Survey" button label or standard informational text). This ensures defaults are stored only once and are managed centrally.
Usage: This table is populated (seeded) during application deployment or database migration with all required default text snippets for all supported languages. During email assembly, the system queries this table using predefined text_keys and the target language_code to retrieve the necessary default text. These defaults are used as fallbacks if a user hasn't provided a specific customization in EmailTemplateContent for the editable fields, and are used directly for the fixed structural elements of the email. This table is read-only from the perspective of a regular user interacting with PulseCheck settings. Updates would typically happen via migrations or a dedicated super-admin interface.
Schema:
Example Row (Default Subject):
- snippet_id: 1
- text_key: email.initial.subject
- language_code: en
- text_value: Your feedback is important!
Example Row (Default Subject - Spanish):
- snippet_id: 2
- text_key: email.initial.subject
- language_code: es
- text_value: ¡Su opinión es importante!
Example Row (Button Label):
- snippet_id: 53
- text_key: email.template.button.takeSurvey
- language_code: en
- text_value: Take the Survey
Example Row (Button Label - Spanish):
- snippet_id: 54
- text_key: email.template.button.takeSurvey
- language_code: es
- text_value: Realizar la Encuesta

Table Structure

Column Name	Data Type	Notes
snippet_id	INT / SERIAL	PK
text_key	VARCHAR	UNIQUE Key, e.g., 'email.initial.subject', 'email.template.button.takeSurvey'
language_code	VARCHAR(5)	e.g., 'en', 'es'
text_value	TEXT	The actual translated default text
Constraint		UNIQUE(text_key, language_code)

Prompt to Generate SystemTextSnippets for each Language

I did not generate the SystemTextSnippets translations yet because I did not know what syntax you want to use for placeholders (variables).

I create the prompt needed to generate the data for each language as a CSV. I used square brackets in this example, but you can update to your desired syntax.

You will need to run this prompt separately for each language. (or give me the desired syntax and I can run it).

The quality of these translations needs to be very good. I would recommend we use Claude 3.7 or Gemini 2.5.

Prompt for LLM:

Please act as a professional translator. Translate the following English text snippets into: TARGET_LANGUAGE_NAME (TARGET_LANGUAGE_CODE).

**Instructions:**

1.  Translate only the English text provided after the colon for each `text_key`.
2.  **Crucially, preserve any placeholders exactly as they appear** (e.g., `[ContactFirstName]`, `[Your Company Name]`, `[Frequency]`). Do not translate the text inside the square brackets.
3.  For the `email.template.footer.unsubscribe` key, translate the surrounding text but keep the link part structured as `[unsubscribe_link_start]TRANSLATED_TEXT_FOR_UNSUBSCRIBE_HERE[unsubscribe_link_end]`.
4.  Maintain the general tone and meaning of the original English text.
5.  Output the results strictly in CSV format with three columns: `text_key`, `language_code`, `text_value`.
6.  The `language_code` column should always contain: **TARGET_LANGUAGE_CODE**
7.  Include a header row in the CSV output.
8.  Ensure the `text_value` in the CSV is properly quoted if it contains commas or newlines (standard CSV practice). Use newline characters (`\n`) within the quoted `text_value` where appropriate based on the source text structure.

**English Source Text Snippets:**

email.initial.fromName.default: [Your Company Name]
email.initial.subject.default: Your feedback for [Your Company Name]!
email.initial.preview.default: Tell us about your recent experience.
email.initial.bodyBeforeButton.default: Hello [ContactFirstName],\n\nThank you for choosing [Your Company Name]. We strive to provide exceptional experiences and value your feedback.\nWould you take a moment to let us know how we're doing? Your input is valuable and will help us improve.
email.initial.bodyAfterButton.default: Your feedback helps us improve and better meet your needs.\nThank you for your time and continued support.\n\nBest regards,\nThe [Your Company Name] Team
email.reminder1.fromName.default: [Your Company Name]
email.reminder1.subject.default: Reminder: Your feedback for [Your Company Name]!
email.reminder1.preview.default: Just a quick reminder to share your thoughts.
email.reminder1.bodyBeforeButton.default: Hello [ContactFirstName],\n\nThis is a friendly reminder about our satisfaction pulse check we sent earlier this month.\nYour ongoing feedback is invaluable to us as we strive to improve your experience with our organization. This brief survey only takes a minute or two to complete.
email.reminder1.bodyAfterButton.default: Thank you for being a valued part of our community.\n\nBest regards,\nThe [Your Company Name] Team
email.reminder2.fromName.default: [Your Company Name]
email.reminder2.subject.default: Final Reminder: Your feedback for [Your Company Name]?
email.reminder2.preview.default: Last chance to tell us about your experience.
email.reminder2.bodyBeforeButton.default: Hello [ContactFirstName],\n\nOur [Frequency] PulseCheck survey will be closing soon, and we haven't heard from you yet.\nYour perspective is important to us and helps ensure we're meeting your needs consistently. This brief check-in takes just 1-2 minutes to complete.
email.reminder2.bodyAfterButton.default: Thank you for your continued support and for helping us improve.\n\nBest regards,\nThe [Your Company Name] Team
email.template.opinionMatters: Your opinion matters
email.template.completeInMinutes: Complete in 1-2 minutes
email.template.button.takeSurvey: Take the Survey
email.template.infobox.quick.title: Quick & Easy
email.template.infobox.quick.subtitle: Brief survey
email.template.infobox.improve.title: Help Us Improve
email.template.infobox.improve.subtitle: We value your input
email.template.infobox.comments.title: Share Comments
email.template.infobox.comments.subtitle: Tell us your thoughts
email.template.footer.signature: Best regards,
email.template.footer.senderName: The [Your Company Name] Team
email.template.footer.poweredBy: Powered by
email.template.footer.unsubscribe: If you’d rather not receive future surveys, you can [unsubscribe_link_start]unsubscribe here[unsubscribe_link_end].

**Example for TARGET_LANGUAGE_NAME = Spanish, TARGET_LANGUAGE_CODE = es:**

Output the CSV like this:
```csv
text_key,language_code,text_value
email.initial.fromName.default,es,"[Your Company Name]"
email.initial.subject.default,es,"¡Su opinión para [Your Company Name]!"
email.initial.preview.default,es,"Cuéntenos sobre su experiencia reciente."
email.initial.bodyBeforeButton.default,es,"Hola [ContactFirstName],\n\nGracias por elegir [Your Company Name]. Nos esforzamos por brindar experiencias excepcionales y valoramos sus comentarios.\n¿Podría tomarse un momento para decirnos cómo lo estamos haciendo? Su opinión es valiosa y nos ayudará a mejorar."
email.initial.bodyAfterButton.default,es,"Sus comentarios nos ayudan a mejorar y satisfacer mejor sus necesidades.\nGracias por su tiempo y apoyo continuo.\n\nSaludos cordiales,\nEl Equipo de [Your Company Name]"
email.reminder1.fromName.default,es,"[Your Company Name]"
email.reminder1.subject.default,es,"Recordatorio: ¡Su opinión para [Your Company Name]!"
email.reminder1.preview.default,es,"Solo un breve recordatorio para compartir sus pensamientos."
email.reminder1.bodyBeforeButton.default,es,"Hola [ContactFirstName],\n\nEste es un recordatorio amistoso sobre nuestra encuesta rápida de satisfacción que enviamos a principios de este mes.\nSus comentarios continuos son invaluables para nosotros mientras nos esforzamos por mejorar su experiencia con nuestra organización. Esta breve encuesta solo toma uno o dos minutos para completar."
email.reminder1.bodyAfterButton.default,es,"Gracias por ser una parte valiosa de nuestra comunidad.\n\nSaludos cordiales,\nEl Equipo de [Your Company Name]"
email.reminder2.fromName.default,es,"[Your Company Name]"
email.reminder2.subject.default,es,"Último Recordatorio: ¿Su opinión para [Your Company Name]?"
email.reminder2.preview.default,es,"Última oportunidad para contarnos sobre su experiencia."
email.reminder2.bodyBeforeButton.default,es,"Hola [ContactFirstName],\n\nNuestra encuesta [Frequency] PulseCheck se cerrará pronto y aún no hemos tenido noticias suyas.\nSu perspectiva es importante para nosotros y nos ayuda a garantizar que satisfacemos sus necesidades de manera constante. Este breve registro toma solo 1-2 minutos para completar."
email.reminder2.bodyAfterButton.default,es,"Gracias por su continuo apoyo y por ayudarnos a mejorar.\n\nSaludos cordiales,\nEl Equipo de [Your Company Name]"
email.template.opinionMatters,es,"Su opinión importa"
email.template.completeInMinutes,es,"Complete en 1-2 minutos"
email.template.button.takeSurvey,es,"Realizar la Encuesta"
email.template.infobox.quick.title,es,"Rápido y Fácil"
email.template.infobox.quick.subtitle,es,"Encuesta breve"
email.template.infobox.improve.title,es,"Ayúdanos a Mejorar"
email.template.infobox.improve.subtitle,es,"Valoramos su opinión"
email.template.infobox.comments.title,es,"Compartir Comentarios"
email.template.infobox.comments.subtitle,es,"Cuéntanos lo que piensas"
email.template.footer.signature,es,"Saludos cordiales,"
email.template.footer.senderName,es,"El Equipo de [Your Company Name]"
email.template.footer.poweredBy,es,"Desarrollado por "
email.template.footer.unsubscribe,es,"Si prefiere no recibir encuestas futuras, puede [unsubscribe_link_start]cancelar la suscripción aquí[unsubscribe_link_end]."