Database Schema

PostgreSQL 16.9 + Drizzle ORM. 60 tables, 53 enums, 78 migrations.

All timestamps are timestamptz (UTC). IDs are uuid with gen_random_uuid(). Multi-tenant by teacherId.

Service Architecture

Every service is classified by 3 orthogonal dimensions:

• deliveryMode: live | async | hybrid
• groupType: individual | group | open
• structure: single | package | course | subscription

Core model: Service → Enrollment → Session

Enums

Session & Status

Enum	Values
sessionStatusEnum	hold, pending_confirmation, scheduled, in_progress, completed, pending_review, cancelled_by_student, cancelled_by_teacher, cancelled_system, no_show_student, no_show_teacher, rescheduled, expired
enrollmentStatusEnum	pending_payment, active, completed, cancelled, expired, suspended
serviceStatusEnum	draft, active, paused, archived
participantStatusEnum	confirmed, cancelled, no_show, attended

Service Dimensions

Enum	Values
deliveryModeEnum	live, async, hybrid
groupTypeEnum	individual, group, open
serviceStructureEnum	single, package, course, subscription
serviceVisibilityEnum	public, unlisted, private
pricingModelEnum	credits, fixed_price, free
billingIntervalEnum	weekly, monthly, quarterly, yearly
scheduleItemTypeEnum	live_session, async_content, milestone
bookingModeEnum	student_picks, fixed_schedule, teacher_assigns, none

Credit System

Enum	Values
creditActionEnum	GRANT, RESERVE, CONSUME, RELEASE, ADJUST, EXPIRE
creditActorEnum	system, teacher, student, stripe

Integration & OAuth

Enum	Values
oauthProviderEnum	google, microsoft, zoom, apple
integrationTypeEnum	calendar_read, calendar_write, drive, meet
calendarSyncStatusEnum	synced, pending, failed

Reviews

Enum	Values
reviewSourceEnum	pinteach, preply, italki, verbling, google_business, trustpilot, other
reviewStatusEnum	pending, approved, hidden, flagged
reviewRequestStatusEnum	pending, completed, expired, dismissed
reviewRequestChannelEnum	in_app, email, link

Audit & GDPR

Enum	Values
auditActorTypeEnum	teacher, student, system, stripe, admin
erasureRequestedByEnum	student, teacher
erasureRequestStatusEnum	pending, processing, completed, rejected

Waitlist & Lifecycle

Enum	Values
waitlistStatusEnum	waiting, notified, enrolled, expired, cancelled
lifecycleEventTypeEnum	first_contact, trial_requested, trial_completed, first_purchase, session_completed, milestone_reached, streak_achieved, session_cancelled, no_show, gap_detected, enrollment_expiring, churned, reactivated, enrollment_renewed

Discount, Scheduling & Content

Enum	Values
discountTypeEnum	percentage, fixed_amount
scheduledMessageStatusEnum	pending, sent, cancelled
contentEntityTypeEnum	template, resource, category, tag

Core Tables

teachers

Column	Type	Constraints
id	uuid	PK
email	varchar(255)	UNIQUE, NOT NULL
name	varchar(255)	NOT NULL
slug	varchar(100)	UNIQUE, NOT NULL
headline	varchar(255)
bio	text
avatarUrl	text
timezone	varchar(100)	NOT NULL
stripeAccountId	varchar(255)
bufferMinutes	integer	default 10
minNoticeHours	integer	default 6
defaultCurrency	varchar(3)	default ‘EUR’
completionMode	enum	default ‘AUTO_COMPLETE’
profileLayout	jsonb
onboardingCompleted	boolean	default false

students

Column	Type	Constraints
id	uuid	PK
teacherId	uuid	FK→teachers CASCADE
email	varchar(255)	NOT NULL
name	varchar(255)	NOT NULL
phone	varchar(50)
timezone	varchar(100)	NOT NULL
level	varchar(50)
goals	text
status	enum	default ‘trial’
driveFolderId	varchar(255)
preferredStartTime	varchar(5)	HH:mm
preferredEndTime	varchar(5)	HH:mm
preferredDays	jsonb (number[])	ISO weekdays 1-7
referralSource	varchar(50)	‘direct’ / ‘directory’ / null
deletedAt	timestamptz	soft-delete

UNIQUE on (teacherId, email). Soft-delete via deletedAt. referralSource tracks attribution: 'direct' = teacher’s own link (?ref=direct), 'directory' = PinTeach lead, null = manually created.

services

3-dimension unified service catalog.

Column	Type	Constraints
id	uuid	PK
teacherId	uuid	FK→teachers
name	varchar(255)	NOT NULL
slug	varchar(150)	NOT NULL
deliveryMode	enum	NOT NULL
groupType	enum	NOT NULL
structure	enum	NOT NULL
sessionDurationMinutes	integer
sessionCount	integer
pricingModel	enum	NOT NULL
priceAmount	integer	cents
currency	varchar(3)	default ‘EUR’
isTrial	boolean	default false
status	enum	default ‘draft’
visibility	enum	default ‘public’
archivedAt	timestamptz	separate from deletedAt
deletedAt	timestamptz	soft-delete

UNIQUE on (teacherId, slug).

enrollments

Column	Type	Constraints
id	uuid	PK
serviceId	uuid	FK→services
studentId	uuid	FK→students
teacherId	uuid	FK→teachers
status	enum	default ‘pending_payment’
sessionsTotal	integer
sessionsScheduled	integer	default 0
sessionsCompleted	integer	default 0
sessionsCancelled	integer	default 0
sessionsForfeited	integer	default 0
validFrom	timestamptz	NOT NULL
validUntil	timestamptz
discountCodeId	uuid	FK→discount_codes (nullable)
discountAmount	integer	cents saved (nullable)

Remaining credits: sessionsTotal - sessionsScheduled - sessionsCompleted - sessionsCancelled - sessionsForfeited

Optional discount: discountCodeId FK→discount_codes, discountAmount (cents saved).

class_sessions

Core session table with 3 satellite tables.

Column	Type	Constraints
id	uuid	PK
enrollmentId	uuid	FK→enrollments
serviceId	uuid	FK→services
teacherId	uuid	FK→teachers
studentId	uuid	FK→students (nullable for groups)
startsAt / endsAt	timestamptz	NOT NULL
timezone	varchar(100)	NOT NULL
status	enum	default ‘scheduled’
isTrialSession	boolean	default false
rescheduleCount	integer	default 0

Satellite: session_content (1:1)

summary, teacherNotes, studentMood, rating, studentLevel, studentGoals, templateId

Satellite: session_payment (1:1)

stripeCheckoutSessionId, stripePaymentIntentId, amountPaid, currency

Satellite: session_calendar_sync (1:1)

googleCalendarEventId, googleMeetLink, meetingUrl, syncStatus (synced/pending/failed)

credit_ledger

Immutable append-only credit operations.

Column	Type	Constraints
id	uuid	PK
enrollmentId	uuid	FK→enrollments
sessionId	uuid	FK→class_sessions
studentId	uuid	FK→students
teacherId	uuid	FK→teachers
action	enum	NOT NULL
amount	integer	NOT NULL
idempotencyKey	varchar(255)	partial UNIQUE (WHERE NOT NULL)

Key format: {ACTION}:{enrollmentId}:{sessionId}:{studentId}

OAuth & Integrations

oauth_connections

Provider-agnostic OAuth storage. UNIQUE on (teacherId, provider, providerAccountId).

integration_settings

Per-teacher integration config. UNIQUE on (teacherId, integrationType). JSONB config typed per integrationType.

Resources & Materials

material_folders

Hierarchical folder system (self-referential parentId). Cascade soft-delete with deletionBatchId.

lesson_templates

Reusable lesson blueprints. Soft-delete via deletedAt.

teacher_resources

Resource library items. Provider: link/drive/youtube/vimeo. Kind: material/homework/reference.

Junction Tables

Table	Purpose
template_resources	template ↔ resource (sortOrder)
session_resources	session ↔ resource (sortOrder, source)
session_templates	session ↔ template (sortOrder)
template_tags	template ↔ tag (CASCADE)
template_categories	template ↔ category (CASCADE)
resource_tags	resource ↔ tag (CASCADE)
session_tags	session ↔ tag (CASCADE)
service_tags	service ↔ tag (CASCADE)
student_tags	student ↔ tag (CASCADE)

Other Tables

Reviews

• reviews — Student reviews + external imports (rating 1-5, source, status, isFeatured, teacherResponse)
• review_requests — Review solicitation (channels: in_app/email/link, token-based)
• review_settings — Per-teacher config (1:1)

Availability

• availability_schedules — Named weekly sets
• availability_rules — Recurring weekly time slots
• availability_overrides — One-off time off or extra availability
• schedule_activations — Date-range activations

Legal & Policies

• legal_documents — Versioned legal docs (contract/policy/terms/privacy)
• legal_document_acceptances — Student consent records
• cancellation_policies — Per-service JSONB rules (one default per teacher)

Waitlist

• waitlist_entries — Position-based queue with 48h offer window

Discount Codes

Table	Purpose
discount_codes	Teacher promotional codes. Fields: code VARCHAR(50), discountType (percentage/fixed_amount), discountValue, currency, applicableServiceIds JSONB, maxUses, usesCount, maxUsesPerStudent, validFrom/Until, isActive, deletedAt. Soft-delete. UNIQUE on (teacherId, code) WHERE deleted_at IS NULL
discount_code_uses	Usage tracking. FKs: discountCodeId CASCADE, studentId CASCADE, enrollmentId SET NULL. UNIQUE on (discountCodeId, studentId, enrollmentId)

Scheduled Messages

Table	Purpose
scheduled_messages	Teacher-scheduled contact reminders. Fields: teacherId, studentId, channel, body, scheduledFor TIMESTAMPTZ, status (pending/sent/cancelled), note, jobId (BullMQ), contactLogId FK→contact_log. PinTeach does NOT auto-send — creates contact_log + notification when due

Audit & GDPR

• audit_logs — Append-only audit trail (fire-and-forget)
• data_erasure_requests — GDPR Art. 17 (30-day due date)
• data_retention_settings — Per-teacher data retention config (1:1)

Lifecycle

• student_lifecycle_events — Event-sourced tracking (14 event types)
• retention_settings — Per-teacher thresholds (1:1)

Content Analytics

Table	Purpose
content_analytics_cache	Aggregation cache for content analytics

Soft-Delete Pattern

8 tables use deletedAt TIMESTAMPTZ (NULL = active, non-NULL = deleted):

students, services, lesson_templates, teacher_resources, material_folders, tags, class_categories, discount_codes

Use notDeleted(table) helper from @pinteach/db. For raw SQL: AND deleted_at IS NULL.

Exception: GDPR export intentionally includes soft-deleted records.

Connection

Driver: postgres.js via Drizzle ORM
URL: postgresql://pinteach:***@localhost:5433/pinteach
Schema: packages/db/src/schema/index.ts
Client: packages/db/src/index.ts (exports `db` instance)