/opt/canhelp/packages/db/src
Edit: /opt/canhelp/packages/db/src/schema.ts (43956B)
import {
pgTable,
text,
boolean,
timestamp,
numeric,
varchar,
smallint,
integer,
real,
jsonb,
index,
uniqueIndex,
type AnyPgColumn,
} from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'
// ─── Auth tables (Better Auth compatible) ────────────────────────────────────
export const users = pgTable('users', {
id: text('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull().unique(),
emailVerified: boolean('email_verified').notNull().default(false),
image: text('image'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
// Marketplace-specific fields
firstName: text('first_name').notNull().default(''),
lastName: text('last_name').notNull().default(''),
role: text('role', { enum: ['user', 'admin', 'customer', 'specialist'] })
.notNull()
.default('user'),
phone: text('phone'),
bio: text('bio'),
bioEl: text('bio_el'),
bioEn: text('bio_en'),
bioRu: text('bio_ru'),
bioUk: text('bio_uk'),
skills: text('skills').array(),
specialistCategories: text('specialist_categories').array(),
specialistLocations: text('specialist_locations').array(),
planId: text('plan_id'),
locale: varchar('locale', { length: 5 }).notNull().default('el'),
languages: text('languages').array().default([]),
isActive: boolean('is_active').notNull().default(true),
lastSeenAt: timestamp('last_seen_at'),
notifyNewTasks: boolean('notify_new_tasks').notNull().default(false),
notifMessages: boolean('notif_messages').notNull().default(true),
balance: numeric('balance', { precision: 12, scale: 2 }).notNull().default('0'),
planExpiresAt: timestamp('plan_expires_at'),
pendingPlanId: text('pending_plan_id'),
renewalReminderSentAt: timestamp('renewal_reminder_sent_at'),
referralCode: varchar('referral_code', { length: 16 }).unique(),
referredBy: text('referred_by'),
showContactInfo: boolean('show_contact_info').notNull().default(false),
phoneVerified: boolean('phone_verified').notNull().default(false),
personalSiteSlug: varchar('personal_site_slug', { length: 50 }).unique(),
siteSettings: jsonb('site_settings').$type<{
showBio?: boolean
showRating?: boolean
showServices?: boolean
showPortfolio?: boolean
showReviews?: boolean
showPhone?: boolean
showStatus?: boolean
showFullLastName?: boolean
siteTheme?: 'dark' | 'light'
profileBackgroundImage?: string
seoTitleEl?: string
seoTitleEn?: string
seoTitleRu?: string
seoTitleUk?: string
seoDescEl?: string
seoDescEn?: string
seoDescRu?: string
seoDescUk?: string
}>(),
})
export const sessions = pgTable('sessions', {
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
token: text('token').notNull().unique(),
expiresAt: timestamp('expires_at').notNull(),
ipAddress: text('ip_address'),
userAgent: text('user_agent'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const accounts = pgTable(
'accounts',
{
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
accountId: text('account_id').notNull(),
providerId: text('provider_id').notNull(),
accessToken: text('access_token'),
refreshToken: text('refresh_token'),
accessTokenExpiresAt: timestamp('access_token_expires_at'),
refreshTokenExpiresAt: timestamp('refresh_token_expires_at'),
scope: text('scope'),
idToken: text('id_token'),
password: text('password'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
},
(t) => ({
userProviderIdx: index('accounts_user_provider_idx').on(t.userId, t.providerId),
userIdx: index('accounts_user_idx').on(t.userId),
}),
)
export const verifications = pgTable('verifications', {
id: text('id').primaryKey(),
identifier: text('identifier').notNull(),
value: text('value').notNull(),
expiresAt: timestamp('expires_at').notNull(),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Categories ────────────────────────────────────────────────────────────
export const categories = pgTable('categories', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
slug: varchar('slug', { length: 100 }).notNull().unique(),
icon: text('icon').notNull().default('📦'),
namesEl: text('names_el').notNull(),
namesEn: text('names_en').notNull(),
namesRu: text('names_ru').notNull(),
namesUk: text('names_uk'),
parentId: text('parent_id').references((): AnyPgColumn => categories.id, { onDelete: 'cascade' }),
order: integer('order').notNull().default(0),
isActive: boolean('is_active').notNull().default(true),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const categoriesRelations = relations(categories, ({ one, many }) => ({
parent: one(categories, { fields: [categories.parentId], references: [categories.id], relationName: 'children' }),
children: many(categories, { relationName: 'children' }),
}))
// ─── Locations ─────────────────────────────────────────────────────────────
export const locations = pgTable('locations', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
slug: varchar('slug', { length: 100 }).notNull().unique(),
nameEl: text('name_el').notNull(),
nameEn: text('name_en').notNull(),
nameRu: text('name_ru').notNull(),
nameUk: text('name_uk'),
parentId: text('parent_id').references((): AnyPgColumn => locations.id, { onDelete: 'cascade' }),
order: integer('order').notNull().default(0),
isActive: boolean('is_active').notNull().default(true),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const locationsRelations = relations(locations, ({ one, many }) => ({
parent: one(locations, { fields: [locations.parentId], references: [locations.id], relationName: 'districts' }),
children: many(locations, { relationName: 'districts' }),
}))
// ─── Marketplace tables ────────────────────────────────────────────────────
export const tasks = pgTable('tasks', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
title: text('title').notNull(),
description: text('description').notNull(),
originalLocale: varchar('original_locale', { length: 5 }).notNull().default('el'),
titleEl: text('title_el'),
titleEn: text('title_en'),
titleRu: text('title_ru'),
titleUk: text('title_uk'),
descriptionEl: text('description_el'),
descriptionEn: text('description_en'),
descriptionRu: text('description_ru'),
descriptionUk: text('description_uk'),
status: text('status', {
enum: ['draft', 'open', 'in_progress', 'completed', 'cancelled'],
})
.notNull()
.default('open'),
budget: numeric('budget', { precision: 10, scale: 2 }),
budgetNegotiable: boolean('budget_negotiable').notNull().default(false),
currency: varchar('currency', { length: 3 }).notNull().default('EUR'),
category: text('category'),
location: text('location'),
district: text('district'),
street: text('street'),
houseNumber: text('house_number'),
timeSlot: text('time_slot'),
confidentialNote: text('confidential_note'),
images: text('images').array(),
deadline: timestamp('deadline'),
expiresAt: timestamp('expires_at'),
customerId: text('customer_id')
.notNull()
.references(() => users.id),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const offers = pgTable('offers', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: text('task_id')
.notNull()
.references(() => tasks.id, { onDelete: 'cascade' }),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id),
price: numeric('price', { precision: 10, scale: 2 }).notNull(),
currency: varchar('currency', { length: 3 }).notNull().default('EUR'),
message: text('message'),
counterPrice: numeric('counter_price', { precision: 10, scale: 2 }),
status: text('status', {
enum: ['pending', 'accepted', 'declined', 'withdrawn', 'other_accepted'],
})
.notNull()
.default('pending'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const chatRooms = pgTable('chat_rooms', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: text('task_id')
.references(() => tasks.id, { onDelete: 'cascade' }),
customerId: text('customer_id')
.notNull()
.references(() => users.id),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
export const messages = pgTable('messages', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
roomId: text('room_id')
.notNull()
.references(() => chatRooms.id, { onDelete: 'cascade' }),
senderId: text('sender_id')
.notNull()
.references(() => users.id),
body: text('body').notNull(),
imageUrl: text('image_url'),
isRead: boolean('is_read').notNull().default(false),
createdAt: timestamp('created_at').notNull().defaultNow(),
translations: jsonb('translations').$type
>(),
})
export const reviews = pgTable('reviews', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: text('task_id')
.notNull()
.references(() => tasks.id),
authorId: text('author_id')
.notNull()
.references(() => users.id),
targetId: text('target_id')
.notNull()
.references(() => users.id),
rating: smallint('rating').notNull(),
comment: text('comment'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
export const notifications = pgTable('notifications', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
recipientId: text('recipient_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
type: text('type', {
enum: [
'new_offer',
'offer_accepted',
'offer_declined',
'offer_other_accepted',
'task_completed',
'task_cancelled',
'task_updated',
'new_message',
'new_review',
'task_invite',
'task_deadline_reminder',
'task_archived',
'referral_reward',
'support_ticket_reply',
'specialist_card_submitted',
'specialist_card_approved',
'specialist_card_rejected',
],
}).notNull(),
title: text('title').notNull(),
body: text('body'),
referenceId: text('reference_id'),
isRead: boolean('is_read').notNull().default(false),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
export const pushTokens = pgTable(
'push_tokens',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
token: text('token').notNull(),
platform: text('platform', { enum: ['android', 'ios', 'web', 'unknown'] })
.notNull()
.default('unknown'),
locale: varchar('locale', { length: 5 }),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
},
(t) => ({
tokenUnique: uniqueIndex('push_tokens_token_uq').on(t.token),
}),
)
export const favorites = pgTable(
'favorites',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
taskId: text('task_id')
.notNull()
.references(() => tasks.id, { onDelete: 'cascade' }),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('favorites_user_task_idx').on(t.userId, t.taskId)],
)
export const reports = pgTable('reports', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
reporterId: text('reporter_id')
.notNull()
.references(() => users.id),
targetType: text('target_type', { enum: ['task', 'user'] }).notNull(),
targetId: text('target_id').notNull(),
reason: text('reason', {
enum: ['spam', 'inappropriate', 'fraud', 'duplicate', 'other'],
}).notNull(),
description: text('description'),
status: text('status', { enum: ['pending', 'reviewed', 'dismissed'] })
.notNull()
.default('pending'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Plans (tariff packages) ───────────────────────────────────────────────
export const plans = pgTable('plans', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
name: text('name').notNull(),
description: text('description'),
role: text('role', { enum: ['requester', 'helper', 'customer', 'specialist'] }).notNull().default('customer'),
tier: text('tier', { enum: ['free', 'pro', 'ultimate'] }).notNull().default('free'),
price: numeric('price', { precision: 10, scale: 2 }).notNull().default('0'),
oldPrice: numeric('old_price', { precision: 10, scale: 2 }),
currency: varchar('currency', { length: 3 }).notNull().default('EUR'),
maxTasks: integer('max_tasks'),
maxOffers: integer('max_offers'),
maxCards: integer('max_cards'),
maxMessagesPerDay: integer('max_messages_per_day'),
maxOrdersPerDay: integer('max_orders_per_day'),
maxSkills: integer('max_skills'),
maxProfiles: integer('max_profiles'),
maxPortfolioItems: integer('max_portfolio_items'),
notifyNewTasks: boolean('notify_new_tasks').notNull().default(false),
canContactFreePlan: boolean('can_contact_free_plan').notNull().default(false),
canContactProPlan: boolean('can_contact_pro_plan').notNull().default(true),
canContactAll: boolean('can_contact_all').notNull().default(false),
canShowContactInfo: boolean('can_show_contact_info').notNull().default(false),
canViewPhone: boolean('can_view_phone').notNull().default(false),
canUploadVideo: boolean('can_upload_video').notNull().default(false),
hasFavorites: boolean('has_favorites').notNull().default(false),
hasGoogleCalendar: boolean('has_google_calendar').notNull().default(false),
hasVerifiedBadge: boolean('has_verified_badge').notNull().default(false),
highlightedReviews: boolean('highlighted_reviews').notNull().default(false),
searchBoost: integer('search_boost').notNull().default(0),
hasPersonalSite: boolean('has_personal_site').notNull().default(false),
hasCanHelpNowStatus: boolean('has_canhelp_now_status').notNull().default(false),
hasNeedHelpStatus: boolean('has_need_help_status').notNull().default(false),
hasAutoResponse: boolean('has_auto_response').notNull().default(false),
hasAutoMatch: boolean('has_auto_match').notNull().default(false),
hasPriceList: boolean('has_price_list').notNull().default(false),
offersMultiplier: numeric('offers_multiplier', { precision: 4, scale: 2 }).notNull().default('1.00'),
receiveInstantDispatchFree: boolean('receive_instant_dispatch_free').notNull().default(false),
receiveInstantDispatchPro: boolean('receive_instant_dispatch_pro').notNull().default(false),
features: text('features').array(),
durationDays: integer('duration_days'),
isDefault: boolean('is_default').notNull().default(false),
isActive: boolean('is_active').notNull().default(true),
order: integer('order').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Specialist Availability ────────────────────────────────────────────────
export const specialistAvailability = pgTable('specialist_availability', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
// ISO date string 'YYYY-MM-DD'
date: varchar('date', { length: 10 }).notNull(),
// 'available' = free, 'busy' = occupied
status: text('status', { enum: ['available', 'busy'] }).notNull(),
note: text('note'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Activity Logs ─────────────────────────────────────────────────────────
export const activityLogs = pgTable('activity_logs', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id').references(() => users.id, { onDelete: 'set null' }),
userEmail: text('user_email'),
userName: text('user_name'),
// e.g. 'user.login', 'user.register', 'admin.user.deactivate', 'task.created'
event: varchar('event', { length: 100 }).notNull(),
details: text('details'), // JSON string
ipAddress: text('ip_address'),
userAgent: text('user_agent'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Specialist Cards ──────────────────────────────────────────────────────
export const specialistCards = pgTable('specialist_cards', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
title: varchar('title', { length: 200 }).notNull(),
description: text('description'),
descriptionEl: text('description_el'),
descriptionEn: text('description_en'),
descriptionRu: text('description_ru'),
descriptionUk: text('description_uk'),
originalLocale: varchar('original_locale', { length: 5 }).notNull().default('el'),
skills: text('skills').array(),
categories: text('categories').array(),
locations: text('locations').array(),
publicationStatus: text('publication_status', { enum: ['pending', 'active', 'inactive'] })
.notNull()
.default('pending'),
isActive: boolean('is_active').notNull().default(true),
order: integer('order').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const categorySuggestions = pgTable('category_suggestions', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
cardId: text('card_id')
.notNull()
.references(() => specialistCards.id, { onDelete: 'cascade' }),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
name: varchar('name', { length: 100 }).notNull(),
locale: varchar('locale', { length: 5 }).notNull().default('el'),
status: text('status', { enum: ['pending', 'mapped', 'created', 'rejected'] })
.notNull()
.default('pending'),
mappedCategoryId: text('mapped_category_id').references(() => categories.id, { onDelete: 'set null' }),
reviewedByAdminId: text('reviewed_by_admin_id').references(() => users.id, { onDelete: 'set null' }),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
}, (t) => ({
cardIdx: index('category_suggestions_card_idx').on(t.cardId),
statusIdx: index('category_suggestions_status_idx').on(t.status),
}))
// ─── Portfolio Items ───────────────────────────────────────────────────────
export const portfolioItems = pgTable('portfolio_items', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
cardId: text('card_id')
.references((): AnyPgColumn => specialistCards.id, { onDelete: 'cascade' }),
imageUrl: text('image_url').notNull(),
title: varchar('title', { length: 200 }),
description: text('description'),
order: integer('order').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Settings (admin key-value store) ─────────────────────────────────────
export const settings = pgTable('settings', {
key: varchar('key', { length: 100 }).primaryKey(),
value: text('value').notNull(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Email Templates ──────────────────────────────────────────────────────
export const emailTemplates = pgTable('email_templates', {
key: varchar('key', { length: 100 }).primaryKey(),
subject: text('subject').notNull().default(''),
body: text('body').notNull().default(''),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Skill Suggestions ────────────────────────────────────────────────────
export const skillSuggestions = pgTable('skill_suggestions', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
categorySlug: varchar('category_slug', { length: 100 }).notNull(),
nameEl: text('name_el').notNull(),
nameEn: text('name_en').notNull(),
nameRu: text('name_ru').notNull(),
nameUk: text('name_uk'),
order: integer('order').notNull().default(0),
})
// ─── AI Logs ───────────────────────────────────────────────────────────────
// ─── Payments ───────────────────────────────────────────────────────────────
export const payments = pgTable('payments', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id').notNull(),
orderRef: text('order_ref').notNull().unique(),
amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
currency: varchar('currency', { length: 3 }).notNull().default('EUR'),
// type: 'balance' = just top up wallet; 'plan' = purchase plan
type: text('type', { enum: ['balance', 'plan'] }).notNull().default('balance'),
status: text('status', { enum: ['pending', 'success', 'failed', 'cancelled'] }).notNull().default('pending'),
description: text('description'),
transactionId: text('transaction_id'),
ePayResponse: jsonb('epay_response'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const balanceTransactions = pgTable('balance_transactions', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
adminUserId: text('admin_user_id').references(() => users.id, { onDelete: 'set null' }),
paymentId: text('payment_id').references(() => payments.id, { onDelete: 'set null' }),
kind: text('kind', {
enum: ['topup', 'plan_purchase', 'admin_topup', 'admin_adjustment'],
}).notNull(),
direction: text('direction', { enum: ['credit', 'debit'] }).notNull(),
amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
balanceBefore: numeric('balance_before', { precision: 12, scale: 2 }).notNull(),
balanceAfter: numeric('balance_after', { precision: 12, scale: 2 }).notNull(),
currency: varchar('currency', { length: 3 }).notNull().default('EUR'),
description: text('description'),
metadata: jsonb('metadata'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── AI Logs ─────────────────────────────────────────────────────────────────
export const aiLogs = pgTable('ai_logs', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
provider: varchar('provider', { length: 20 }).notNull(),
model: varchar('model', { length: 100 }).notNull(),
action: varchar('action', { length: 50 }).notNull(),
fromLocale: varchar('from_locale', { length: 5 }),
toLocale: varchar('to_locale', { length: 5 }),
inputTokens: integer('input_tokens'),
outputTokens: integer('output_tokens'),
totalTokens: integer('total_tokens'),
durationMs: integer('duration_ms'),
costUsd: real('cost_usd'),
success: boolean('success').notNull().default(true),
error: text('error'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Referral Rewards ─────────────────────────────────────────────────────
export const referralRewards = pgTable('referral_rewards', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
referrerId: text('referrer_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
refereeId: text('referee_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
planId: text('plan_id').notNull(),
daysAdded: integer('days_added').notNull(),
expiresAt: timestamp('expires_at').notNull(),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Relations ─────────────────────────────────────────────────────────────
export const usersRelations = relations(users, ({ many }) => ({
tasks: many(tasks),
offers: many(offers),
sentMessages: many(messages),
reviewsGiven: many(reviews, { relationName: 'author' }),
reviewsReceived: many(reviews, { relationName: 'target' }),
notifications: many(notifications),
favorites: many(favorites),
balanceTransactions: many(balanceTransactions, { relationName: 'userBalanceTransactions' }),
adminBalanceTransactions: many(balanceTransactions, { relationName: 'adminBalanceTransactions' }),
}))
export const tasksRelations = relations(tasks, ({ one, many }) => ({
customer: one(users, { fields: [tasks.customerId], references: [users.id] }),
offers: many(offers),
chatRooms: many(chatRooms),
reviews: many(reviews),
favorites: many(favorites),
}))
export const offersRelations = relations(offers, ({ one }) => ({
task: one(tasks, { fields: [offers.taskId], references: [tasks.id] }),
specialist: one(users, { fields: [offers.specialistId], references: [users.id] }),
}))
export const chatRoomsRelations = relations(chatRooms, ({ one, many }) => ({
task: one(tasks, { fields: [chatRooms.taskId], references: [tasks.id] }),
customer: one(users, { fields: [chatRooms.customerId], references: [users.id] }),
specialist: one(users, { fields: [chatRooms.specialistId], references: [users.id] }),
messages: many(messages),
}))
export const messagesRelations = relations(messages, ({ one }) => ({
room: one(chatRooms, { fields: [messages.roomId], references: [chatRooms.id] }),
sender: one(users, { fields: [messages.senderId], references: [users.id] }),
}))
export const reviewsRelations = relations(reviews, ({ one }) => ({
task: one(tasks, { fields: [reviews.taskId], references: [tasks.id] }),
author: one(users, { fields: [reviews.authorId], references: [users.id], relationName: 'author' }),
target: one(users, { fields: [reviews.targetId], references: [users.id], relationName: 'target' }),
}))
export const notificationsRelations = relations(notifications, ({ one }) => ({
recipient: one(users, { fields: [notifications.recipientId], references: [users.id] }),
}))
export const favoritesRelations = relations(favorites, ({ one }) => ({
user: one(users, { fields: [favorites.userId], references: [users.id] }),
task: one(tasks, { fields: [favorites.taskId], references: [tasks.id] }),
}))
export const balanceTransactionsRelations = relations(balanceTransactions, ({ one }) => ({
user: one(users, {
fields: [balanceTransactions.userId],
references: [users.id],
relationName: 'userBalanceTransactions',
}),
adminUser: one(users, {
fields: [balanceTransactions.adminUserId],
references: [users.id],
relationName: 'adminBalanceTransactions',
}),
payment: one(payments, {
fields: [balanceTransactions.paymentId],
references: [payments.id],
}),
}))
export const specialistFavorites = pgTable(
'specialist_favorites',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('specialist_favorites_user_specialist_idx').on(t.userId, t.specialistId)],
)
export const specialistFavoritesRelations = relations(specialistFavorites, ({ one }) => ({
user: one(users, { fields: [specialistFavorites.userId], references: [users.id] }),
specialist: one(users, { fields: [specialistFavorites.specialistId], references: [users.id] }),
}))
// ─── Daily Usage (rate limiting per plan) ──────────────────────────────────
export const dailyUsage = pgTable(
'daily_usage',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
date: varchar('date', { length: 10 }).notNull(),
messagesSent: integer('messages_sent').notNull().default(0),
ordersMade: integer('orders_made').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('daily_usage_user_date_idx').on(t.userId, t.date)],
)
// ─── Google OAuth Tokens ───────────────────────────────────────────────────
// Used for both Google Calendar access and future Google Sign-In linking.
// TODO: reuse for Google Sign-In — link via googleUserId (sub claim)
export const googleCalendarTokens = pgTable('google_calendar_tokens', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' })
.unique(),
// Google OAuth sub claim — reuse for Sign-In account linking in future
googleUserId: text('google_user_id').unique(),
accessToken: text('access_token').notNull(),
refreshToken: text('refresh_token'),
expiresAt: timestamp('expires_at'),
calendarEmail: text('calendar_email'),
// Comma-separated scopes granted by the user
scopes: text('scopes'),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
export const googleCalendarTokensRelations = relations(googleCalendarTokens, ({ one }) => ({
user: one(users, { fields: [googleCalendarTokens.userId], references: [users.id] }),
}))
// ─── User Statuses (CanHelp Now / Need Help) ──────────────────────────────
export const userStatuses = pgTable(
'user_statuses',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
statusType: text('status_type', { enum: ['canhelp_now', 'need_help'] }).notNull(),
isActive: boolean('is_active').notNull().default(false),
activatedAt: timestamp('activated_at'),
expiresAt: timestamp('expires_at'),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('user_statuses_user_type_idx').on(t.userId, t.statusType)],
)
export const userStatusesRelations = relations(userStatuses, ({ one }) => ({
user: one(users, { fields: [userStatuses.userId], references: [users.id] }),
}))
// ─── FOMO: Task Views ──────────────────────────────────────────────────────
export const taskViews = pgTable(
'task_views',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: text('task_id')
.notNull()
.references(() => tasks.id, { onDelete: 'cascade' }),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
userPlanTier: text('user_plan_tier'),
viewedAt: timestamp('viewed_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('task_views_task_user_idx').on(t.taskId, t.userId)],
)
// ─── FOMO: Task Stats (aggregated per task) ────────────────────────────────
export const taskStats = pgTable(
'task_stats',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: text('task_id')
.notNull()
.references(() => tasks.id, { onDelete: 'cascade' }),
totalViews: integer('total_views').notNull().default(0),
proViews: integer('pro_views').notNull().default(0),
totalOffers: integer('total_offers').notNull().default(0),
avgResponseMin: integer('avg_response_min'),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('task_stats_task_idx').on(t.taskId)],
)
// ─── FOMO: Missed Orders (per specialist per task) ─────────────────────────
export const missedOrders = pgTable('missed_orders', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
taskId: text('task_id')
.notNull()
.references(() => tasks.id, { onDelete: 'cascade' }),
reason: text('reason'),
estimatedValue: numeric('estimated_value', { precision: 10, scale: 2 }),
takenByPro: boolean('taken_by_pro').notNull().default(false),
date: varchar('date', { length: 10 }).notNull(),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── FOMO: Daily Specialist Stats (aggregated per day) ─────────────────────
export const dailySpecialistStats = pgTable(
'daily_specialist_stats',
{
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
date: varchar('date', { length: 10 }).notNull(),
missedTotal: integer('missed_total').notNull().default(0),
missedByPro: integer('missed_by_pro').notNull().default(0),
missedRevenue: numeric('missed_revenue', { precision: 10, scale: 2 }).notNull().default('0'),
receivedOrders: integer('received_orders').notNull().default(0),
totalAvailable: integer('total_available').notNull().default(0),
hotOrdersMissed: integer('hot_orders_missed').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(t) => [uniqueIndex('daily_spec_stats_idx').on(t.specialistId, t.date)],
)
// ─── Auto-Response Settings (Ultimate specialists) ─────────────────────────
export const autoResponseSettings = pgTable('auto_response_settings', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' })
.unique(),
isEnabled: boolean('is_enabled').notNull().default(false),
categories: text('categories').array(), // filter: only respond to these categories (null = all from cards)
locations: text('locations').array(), // filter: only respond to these locations (null = all from cards)
minBudget: numeric('min_budget', { precision: 10, scale: 2 }), // minimum task budget to respond
maxBudget: numeric('max_budget', { precision: 10, scale: 2 }), // maximum task budget to respond
defaultPrice: numeric('default_price', { precision: 10, scale: 2 }), // price in auto-generated offer
defaultMessage: text('default_message'), // message in auto-generated offer
maxAutoOffersPerDay: integer('max_auto_offers_per_day').notNull().default(10),
todayAutoOffers: integer('today_auto_offers').notNull().default(0),
todayDate: varchar('today_date', { length: 10 }), // for resetting daily counter
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Personal Site: Page Views ─────────────────────────────────────────────
export const sitePageViews = pgTable('site_page_views', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
siteOwnerId: text('site_owner_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
visitorFingerprint: text('visitor_fingerprint').notNull(), // privacy-safe hash(IP+UA)[0..16]
date: varchar('date', { length: 10 }).notNull(), // YYYY-MM-DD
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Support Tickets ────────────────────────────────────────────────────────
export const supportTickets = pgTable('support_tickets', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
type: varchar('type', { length: 20 }).notNull().default('bug'), // 'bug' | 'suggestion' | 'other'
subject: text('subject').notNull(),
body: text('body').notNull(),
status: varchar('status', { length: 20 }).notNull().default('open'), // 'open' | 'in_progress' | 'resolved' | 'closed'
adminReply: text('admin_reply'),
adminId: text('admin_id').references(() => users.id, { onDelete: 'set null' }),
repliedAt: timestamp('replied_at'),
bodyTranslations: jsonb('body_translations').$type>(),
adminReplyTranslations: jsonb('admin_reply_translations').$type>(),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
})
// ─── Personal Site: Contact Message Logs ───────────────────────────────────
export const siteContactLogs = pgTable('site_contact_logs', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
siteOwnerId: text('site_owner_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
date: varchar('date', { length: 10 }).notNull(), // YYYY-MM-DD
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Price List Groups (groups within a specialist card) ────────────────────
export const priceListGroups = pgTable('price_list_groups', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
cardId: text('card_id')
.notNull()
.references((): AnyPgColumn => specialistCards.id, { onDelete: 'cascade' }),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
title: varchar('title', { length: 200 }).notNull(),
titleEl: text('title_el'),
titleEn: text('title_en'),
titleRu: text('title_ru'),
titleUk: text('title_uk'),
originalLocale: varchar('original_locale', { length: 5 }).notNull().default('el'),
order: integer('order').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Price List Items (rows within a price list group) ─────────────────────
export const priceListItems = pgTable('price_list_items', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
groupId: text('group_id')
.notNull()
.references((): AnyPgColumn => priceListGroups.id, { onDelete: 'cascade' }),
cardId: text('card_id')
.notNull()
.references((): AnyPgColumn => specialistCards.id, { onDelete: 'cascade' }),
specialistId: text('specialist_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
name: varchar('name', { length: 200 }).notNull(),
nameEl: text('name_el'),
nameEn: text('name_en'),
nameRu: text('name_ru'),
nameUk: text('name_uk'),
description: text('description'),
descriptionEl: text('description_el'),
descriptionEn: text('description_en'),
descriptionRu: text('description_ru'),
descriptionUk: text('description_uk'),
price: numeric('price', { precision: 10, scale: 2 }),
unit: varchar('unit', { length: 100 }),
unitEl: text('unit_el'),
unitEn: text('unit_en'),
unitRu: text('unit_ru'),
unitUk: text('unit_uk'),
originalLocale: varchar('original_locale', { length: 5 }).notNull().default('el'),
order: integer('order').notNull().default(0),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
// ─── Plan History ──────────────────────────────────────────────────────────
export const planHistory = pgTable('plan_history', {
id: text('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
event: varchar('event', { length: 30 }).notNull(),
// 'activated' | 'renewed' | 'downgraded' | 'referral_reward' | 'admin_change' | 'expired'
planId: text('plan_id').notNull(),
planName: varchar('plan_name', { length: 100 }).notNull(),
previousPlanId: text('previous_plan_id'),
previousPlanName: varchar('previous_plan_name', { length: 100 }),
expiresAt: timestamp('expires_at'),
daysAdded: integer('days_added'),
referralRewardId: text('referral_reward_id'),
note: text('note'),
createdAt: timestamp('created_at').notNull().defaultNow(),
})