/
opt
/
canhelp
/
packages
/
db
/
src
/
/opt/canhelp/packages/db/src
mkdir
upload
Name
Size
Mode
Actions
client.js
1758
0644
edit
dl
rm
client.ts
296
0644
edit
dl
rm
index.js
845
0644
edit
dl
rm
index.ts
50
0644
edit
dl
rm
schema.js
55343
0644
edit
dl
rm
schema.ts
43956
0644
edit
dl
rm
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<Record<string, string>>(), }) 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<Record<string, string>>(), adminReplyTranslations: jsonb('admin_reply_translations').$type<Record<string, string>>(), 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(), })
Save
cmd:
run