Task 4.2: Database Schema Design & Setup

[scrollinondubs]

Description

Design PostgreSQL schema for appointments, staff, services, and users. Set up Neon/Supabase database and DrizzleORM.

Epic

Part of Epic 4: Technical Foundation (#21)

Technical Details

• Database: PostgreSQL (Neon or Supabase managed hosting)
• ORM: DrizzleORM with TypeScript types
• Migration Tool: DrizzleORM migrations
• Schema: users, staff, services, appointments, availability tables

Core Tables

1. users - Customer and admin authentication
2. staff - Spa staff members
3. services - Available spa services/treatments
4. appointments - Booked appointments
5. availability - Staff availability schedules
6. time_slots - Available booking time slots

Acceptance Criteria

• PostgreSQL database provisioned on Neon/Supabase
• DrizzleORM configured with TypeScript types
• Core tables created with proper relationships
• Database migrations setup and documented
• ACID transaction patterns documented
• Indexes created for performance (date/time queries)
• Connection pooling configured

Dependencies

None - Foundational database task

Required By

• Task 1.1: Implement Booking Request API (Task 1.1: Implement Booking Request API #13)
• Task 2.2: Real-Time Availability Updates (Task 2.2: Real-Time Availability Updates #17)
• Task 3.2: Notification System for Staff (Task 3.2: Notification System for Staff #20)

Estimated Effort

15 hours

Priority

P0

Git Worktree

Can be developed in main branch or separate database-setup worktree.