Skip to content

Fix FK violation by normalizing interviewer_id 0 to NULL#34

Merged
SuveenE merged 5 commits into
FoloUp:mainfrom
sys-dev-ac:fix/migration
May 25, 2026
Merged

Fix FK violation by normalizing interviewer_id 0 to NULL#34
SuveenE merged 5 commits into
FoloUp:mainfrom
sys-dev-ac:fix/migration

Conversation

@Pavan-Kumar-KN

@Pavan-Kumar-KN Pavan-Kumar-KN commented May 20, 2026 •
edited
Loading

Copy link
Copy Markdown
Contributor

This pull request introduces the initial database schema for the application, establishing the core tables and relationships needed for managing organizations, users, interviews, interviewers, responses, and feedback. The schema also defines a custom enum type for organization plans.

Database schema initialization

  • Created custom enum type plan with possible values: 'free', 'pro', and 'free_trial_over' to represent organization plans.
  • Added organization table with fields for organization details, allowed responses, and plan type.
  • Added user table linked to organization, storing user-specific information.
  • Added interviewer table for storing interviewer agent profiles and attributes.
  • Added interview, response, and feedback tables to manage interviews, their responses, and associated feedback, with appropriate foreign key relationships.

This PR fixes interview create/update failures caused by interviewer_id = 0 violating interview_interviewer_id_fkey.

Changes

Updated src/services/interviews.service.ts

  • Added payload normalization to convert interviewer_id === 0 to null
  • Applied in both createInterview and updateInterview

Added migration supabase/migrations/20260520101000_normalize_interviewer_id.sql

  • Adds trigger function to normalize NEW.interviewer_id = 0 to NULL
  • Applies on BEFORE INSERT OR UPDATE for interview

Fixes #35
Closes #35

@Pavan-Kumar-KN Pavan-Kumar-KN changed the title feat(migration): create the supbase migration files Fix FK violation by normalizing interviewer_id 0 to NULL May 20, 2026
@SuveenE

SuveenE commented May 24, 2026

Copy link
Copy Markdown
Member

@Pavan-Kumar-KN Thanks for fixing the FK violation. Could you trim this PR down to the actual migration/app fix?

  • Please remove package-lock.json and revert the yarn.lock changes. FoloUp repo uses Yarn, and package.json is unchanged, so the lockfile churn is unrelated.
  • Please remove supabase/migrations/20260520094459_init_schema.sql. That looks like a full baseline schema migration, and it will fail on existing Supabase databases because the type/tables already exist.
  • Please keep a forward migration for the interviewer_id = 0 -> NULL normalization.
  • Please also update the app-side normalization to handle the current create payload. The create flow converts interviewer_id to a string, so checking only payload.interviewer_id === 0 does not catch "0". It should normalize 0, "0", and possibly 0n to null, or the UI/API should send null directly.

@Pavan-Kumar-KN

Pavan-Kumar-KN commented May 25, 2026

Copy link
Copy Markdown
Contributor Author

Thanks @SuveenE — I trimmed this PR to only include the migration and the app fix. Please review the changes and let me know if anything else is needed.

Summary of what I changed

  • Removed: package-lock.json (repo uses Yarn).
  • Reverted: yarn.lock to match the base branch.
  • Removed: supabase/migrations/20260520094459_init_schema.sql (accidental baseline).
  • Kept: 20260520101000_normalize_interviewer_id.sql:1-20 (forward normalization migration).
  • App fix: updated normalizeInterviewPayload in interviews.service.ts:1-20 to normalize 0, "0", and 0n to null before insert/update.

@SuveenE

SuveenE commented May 25, 2026

Copy link
Copy Markdown
Member

Merging. Thanks for the fix @Pavan-Kumar-KN

@SuveenE SuveenE merged commit fa1c858 into FoloUp:main May 25, 2026
1 check passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Foreign key violation when interviewer_id defaults to 0 during interview create/update

2 participants

@Pavan-Kumar-KN @SuveenE