/** * Drizzle ORM schema - Multideal (מולטידיל) database * * All 13 FDS data models + supporting tables. * snake_case column names (enforced by drizzle.config.ts casing: 'snake_case'). * Every timestamp uses { withTimezone: true }. * Every enum uses pgEnum. * * PII columns (phone, email, address, token) are stored as encrypted text via pgcrypto. * Blind-index columns provide deterministic lookup without decryption. */ import { bytesToHex } from '@/lib/encoding.js'; import { pgTable, uuid, text, integer, numeric, jsonb, timestamp, index, uniqueIndex, } from 'drizzle-orm/pg-core'; import { sql } from 'drizzle-orm'; import { llmJobTypeEnum, llmJobStatusEnum, llmDecisionEnum, llmTargetTypeEnum, imageApprovalStatusEnum, uploadPurposeEnum, imageEntityTypeEnum, aiDecisionEnum, notificationSeverityEnum, scanStatusEnum, personalDealStatusEnum, reportTargetTypeEnum, reportReasonEnum, reportStatusEnum, reviewRemovalQueueStatusEnum, emailVerificationPurposeEnum, } from './enums.js'; import { users, vendors } from './core.js'; import { deals, reviews } from './deal-variants.js'; import { orderLine } from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js'; import { e2eFactoryRuns } from './e2e-factory.js'; // ─── Supporting Tables ──────────────────────────────────────────────────────── /** Auth: server-side session records. */ export const sessions = pgTable( 'sessions', { id: text('id').primaryKey(), userId: uuid('user_id') .notNull() .references(() => users.id, { onDelete: 'cascade' }), csrfToken: text('csrf_token').notNull(), userAgent: text('user_agent').notNull().default(''), /** Encrypted IP address. */ ipEncrypted: text('ip_encrypted'), expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(), revokedAt: timestamp('revoked_at', { withTimezone: true }), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), /** SHA-256 hex of the opaque refresh token. Unique per session. Added in M1. * $defaultFn provides a random placeholder so existing inserts (M1→M2 gap) stay valid. * M2 will generate and pass the real hash explicitly. */ refreshTokenHash: text('refresh_token_hash') .notNull() .unique() .$defaultFn(() => { const b = new Uint8Array(32); crypto.getRandomValues(b); return bytesToHex(b); }), /** Timestamp of last token refresh. Null until first refresh. */ lastRefreshedAt: timestamp('last_refreshed_at', { withTimezone: true }), /** SHA-256 hex of the refresh token rotated away on last rotation. * Used for the 60-second grace window: concurrent rotation losers carry the * old RT value, which matches here instead of refresh_token_hash. */ previousRefreshTokenHash: text('previous_refresh_token_hash'), }, (t) => [index('sessions_expires_at_idx').on(t.expiresAt)], ); /** Auth: email magic-links for guest→user conversion. */ export const magicLinks = pgTable( 'magic_links', { id: text('id').primaryKey(), tokenHash: text('token_hash').notNull().unique(), /** Encrypted email. */ emailEncrypted: text('email_encrypted').notNull(), preFill: jsonb('pre_fill'), usedAt: timestamp('used_at', { withTimezone: true }), expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [index('magic_links_expires_at_idx').on(t.expiresAt)], ); /** Auth: email verification tokens for signup + email-change flows. */ export const emailVerifications = pgTable( 'email_verifications', { id: uuid('id').primaryKey().defaultRandom(), userId: uuid('user_id') .notNull() .references(() => users.id, { onDelete: 'cascade' }), /** SHA-256 hex of the raw token. Raw token only sent in email URL, never stored. */ tokenHash: text('token_hash').notNull().unique(), /** Encrypted (pgcrypto) email being verified. Supports change-flow where users.email not yet updated. */ emailEncrypted: text('email_encrypted').notNull(), purpose: emailVerificationPurposeEnum('purpose').notNull(), expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(), consumedAt: timestamp('consumed_at', { withTimezone: true }), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('email_verifications_user_id_idx').on(t.userId), index('email_verifications_expires_at_idx').on(t.expiresAt), ], ); /** PWA push subscriptions. */ export const pushSubscriptions = pgTable('push_subscriptions', { id: uuid('id').primaryKey().defaultRandom(), userId: uuid('user_id') .notNull() .references(() => users.id, { onDelete: 'cascade' }), endpoint: text('endpoint').notNull().unique(), p256dh: text('p256dh').notNull(), auth: text('auth').notNull(), userAgent: text('user_agent').notNull().default(''), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), revokedAt: timestamp('revoked_at', { withTimezone: true }), }); /** Personal deal requests. */ export const personalDealRequests = pgTable('personal_deal_requests', { id: uuid('id').primaryKey().defaultRandom(), userId: uuid('user_id') .notNull() .references(() => users.id), vendorId: uuid('vendor_id') .notNull() .references(() => vendors.id), sourceDealId: uuid('source_deal_id') .notNull() .references(() => deals.id), status: personalDealStatusEnum('status').notNull().default('PENDING'), responseDeadline: timestamp('response_deadline', { withTimezone: true, }).notNull(), respondedAt: timestamp('responded_at', { withTimezone: true }), createdDealId: uuid('created_deal_id') .unique() .references(() => deals.id), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }); /** Vendor-uploaded gallery images. */ export const vendorGalleryImages = pgTable('vendor_gallery_images', { id: uuid('id').primaryKey().defaultRandom(), vendorId: uuid('vendor_id') .notNull() .references(() => vendors.id, { onDelete: 'cascade' }), url: text('url').notNull(), caption: text('caption'), sortOrder: integer('sort_order').notNull().default(0), approvalStatus: imageApprovalStatusEnum('approval_status').notNull().default('APPROVED'), rejectReasonCode: text('reject_reason_code'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }); /** User-uploaded gallery images attached to vendor pages (from purchases). */ export const userGalleryImages = pgTable('user_gallery_images', { id: uuid('id').primaryKey().defaultRandom(), userId: uuid('user_id') .notNull() .references(() => users.id, { onDelete: 'cascade' }), vendorId: uuid('vendor_id') .notNull() .references(() => vendors.id), orderLineId: uuid('order_line_id').references(() => orderLine.id), url: text('url').notNull(), caption: text('caption'), approvalStatus: imageApprovalStatusEnum('approval_status').notNull().default('APPROVED'), rejectReasonCode: text('reject_reason_code'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }); /** Tracks every R2 upload for virus scanning and moderation. */ export const imageUploads = pgTable( 'image_uploads', { id: uuid('id').primaryKey().defaultRandom(), uploaderUserId: uuid('uploader_user_id').references(() => users.id), uploaderVendorId: uuid('uploader_vendor_id').references(() => vendors.id), r2Key: text('r2_key').notNull(), mime: text('mime').notNull(), sizeBytes: integer('size_bytes').notNull(), scanStatus: scanStatusEnum('scan_status').notNull().default('PENDING'), approvalStatus: imageApprovalStatusEnum('approval_status').notNull().default('PENDING'), rejectReasonCode: text('reject_reason_code'), /** Upload purpose — nullable until Wave 5 backfill; NOT NULL constraint applied in 0058. */ purpose: uploadPurposeEnum('purpose').notNull(), entityType: imageEntityTypeEnum('entity_type'), entityId: uuid('entity_id'), aiDecision: aiDecisionEnum('ai_decision'), aiReason: text('ai_reason'), aiScore: numeric('ai_score', { precision: 3, scale: 2 }), aiModel: text('ai_model'), aiCheckedAt: timestamp('ai_checked_at', { withTimezone: true }), aiRawPayload: jsonb('ai_raw_payload'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('image_uploads_entity_idx').on(t.entityType, t.entityId), index('image_uploads_purpose_idx').on(t.purpose), ], ); /** User-submitted reports about vendors/deals/reviews/users. */ export const reportTickets = pgTable('report_tickets', { id: uuid('id').primaryKey().defaultRandom(), reporterUserId: uuid('reporter_user_id').references(() => users.id), targetType: reportTargetTypeEnum('target_type').notNull(), targetId: uuid('target_id').notNull(), reason: reportReasonEnum('reason').notNull(), body: text('body').notNull().default(''), status: reportStatusEnum('status').notNull().default('OPEN'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), resolvedAt: timestamp('resolved_at', { withTimezone: true }), }); /** Token-bucket rate-limit state (Workers-side enforcement). */ export const rateLimitBuckets = pgTable('rate_limit_buckets', { key: text('key').primaryKey(), count: integer('count').notNull().default(0), windowStartAt: timestamp('window_start_at', { withTimezone: true }).notNull(), refilledAt: timestamp('refilled_at', { withTimezone: true }).notNull(), }); /** Vendor-submitted review removal requests with optional AI scoring. */ export const reviewRemovalQueue = pgTable('review_removal_queue', { id: uuid('id').primaryKey().defaultRandom(), reviewId: uuid('review_id') .notNull() .unique() .references(() => reviews.id, { onDelete: 'cascade' }), submittedBy: uuid('submitted_by') .notNull() .references(() => vendors.id), reason: text('reason').notNull(), aiScore: numeric('ai_score', { precision: 5, scale: 4 }), status: reviewRemovalQueueStatusEnum('status').notNull().default('PENDING'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), decidedAt: timestamp('decided_at', { withTimezone: true }), }); /** Transactional outbox for reliable event publishing. */ export const outbox = pgTable( 'outbox', { id: uuid('id').primaryKey().defaultRandom(), aggregateType: text('aggregate_type').notNull(), aggregateId: uuid('aggregate_id').notNull(), eventType: text('event_type').notNull(), dedupeKey: text('dedupe_key'), payload: jsonb('payload').notNull(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), processedAt: timestamp('processed_at', { withTimezone: true }), /** Set on failure; cleared to null on success (retryable until retry_count >= 3). */ failedAt: timestamp('failed_at', { withTimezone: true }), /** Number of failed processing attempts. */ retryCount: integer('retry_count').notNull().default(0), /** Finite attempt budget; explicitly retryable failures may raise it. */ retryLimit: integer('retry_limit').notNull().default(3), /** Last error message from a failed processing attempt. */ lastError: text('last_error'), // H2: explicit terminal state. Set when retry cap is crossed; row is then // excluded from all drain paths and surfaced by the outbox_abandoned check. deadAt: timestamp('dead_at', { withTimezone: true }), }, (t) => [ index('outbox_processed_at_idx').on(t.processedAt), index('outbox_aggregate_feed_idx').on(t.aggregateType, t.aggregateId, t.createdAt), index('outbox_dead_at_idx').on(t.deadAt), uniqueIndex('outbox_aggregate_event_uniq').on(t.aggregateType, t.aggregateId, t.eventType), uniqueIndex('outbox_dedupe_key_uq').on(t.dedupeKey), ], ); export const paymentFinalizeCheckpoints = pgTable( 'payment_finalize_checkpoints', { id: uuid('id').primaryKey().defaultRandom(), purchaseId: uuid('purchase_id').notNull(), orderId: uuid('order_id').notNull(), providerPaymentId: text('provider_payment_id').notNull(), effectKey: text('effect_key').notNull(), status: text('status').notNull().default('pending'), attempt: integer('attempt').notNull().default(0), result: jsonb('result'), leaseOwner: text('lease_owner'), leaseExpiresAt: timestamp('lease_expires_at', { withTimezone: true }), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), completedAt: timestamp('completed_at', { withTimezone: true }), }, (t) => [ uniqueIndex('payment_finalize_checkpoint_effect_uniq').on(t.purchaseId, t.effectKey), uniqueIndex('payment_finalize_checkpoint_provider_uniq') .on(t.providerPaymentId) .where(sql`${t.effectKey} = 'payment-claim'`), index('payment_finalize_checkpoint_pending_idx').on(t.status, t.updatedAt), ], ); export const paymentExpectedContracts = pgTable( 'payment_expected_contracts', { id: uuid('id').primaryKey().defaultRandom(), purchaseId: uuid('purchase_id').notNull(), orderId: uuid('order_id').notNull(), amountAgorot: integer('amount_agorot').notNull(), currency: text('currency').notNull(), customerId: text('customer_id'), destinationAccountId: text('destination_account_id').notNull(), applicationFeeAgorot: integer('application_fee_agorot').notNull(), effectKey: text('effect_key').notNull(), bindingSource: text('binding_source').notNull().default('checkout'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ uniqueIndex('payment_expected_contract_purchase_uniq').on(t.purchaseId), uniqueIndex('payment_expected_contract_effect_uniq').on(t.effectKey), ], ); export const cartCheckoutCaptures = pgTable( 'cart_checkout_captures', { id: uuid('id').primaryKey().defaultRandom(), checkoutKey: text('checkout_key').notNull(), orderId: uuid('order_id').notNull(), orderLineId: uuid('order_line_id').notNull(), providerPaymentId: text('provider_payment_id'), providerHoldId: text('provider_hold_id').notNull(), amountAgorot: integer('amount_agorot').notNull(), checkoutOrderIds: uuid('checkout_order_ids').array().notNull(), status: text('status').notNull().default('pending'), captureIdempotencyKey: text('capture_idempotency_key').notNull(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ uniqueIndex('cart_checkout_captures_checkout_order_uniq').on(t.checkoutKey, t.orderId), uniqueIndex('cart_checkout_captures_payment_uniq').on(t.providerPaymentId), uniqueIndex('cart_checkout_captures_hold_uniq').on(t.providerHoldId), index('cart_checkout_captures_status_idx').on(t.status, t.createdAt), ], ); export const cartCheckoutCompensations = pgTable( 'cart_checkout_compensations', { id: uuid('id').primaryKey().defaultRandom(), checkoutKey: text('checkout_key').notNull(), orderId: uuid('order_id').notNull(), orderLineId: uuid('order_line_id').notNull(), providerPaymentId: text('provider_payment_id').notNull(), amountAgorot: integer('amount_agorot').notNull(), checkoutOrderIds: uuid('checkout_order_ids').array().notNull(), refundIdempotencyKey: text('refund_idempotency_key').notNull(), status: text('status').notNull().default('pending'), providerRefundId: text('provider_refund_id'), lastError: text('last_error'), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ uniqueIndex('cart_checkout_compensations_payment_uniq').on(t.providerPaymentId), uniqueIndex('cart_checkout_compensations_checkout_order_uniq').on(t.checkoutKey, t.orderId), index('cart_checkout_compensations_status_idx').on(t.status, t.updatedAt), ], ); /** * Global key-value store for runtime admin configuration. * Editable from the admin panel without a redeploy. * * Known keys: * llm_model - Gemini model ID used by all AI agents (e.g. "gemini-2.0-flash-preview") */ export const systemConfig = pgTable('system_config', { key: text('key').primaryKey(), value: text('value').notNull(), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), updatedBy: text('updated_by'), }); /** Dashboard notifications for vendors and users (image rejections, approval status). */ export const notifications = pgTable( 'notifications', { id: uuid('id').primaryKey().defaultRandom(), recipientType: text('recipient_type').notNull(), recipientId: uuid('recipient_id').notNull(), eventType: text('event_type').notNull(), severity: notificationSeverityEnum('severity').notNull().default('warning'), payload: jsonb('payload').$type>().notNull(), readAt: timestamp('read_at', { withTimezone: true }), resolvedAt: timestamp('resolved_at', { withTimezone: true }), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('notifications_unresolved_idx') .on(t.recipientType, t.recipientId) .where(sql`${t.resolvedAt} IS NULL`), ], ); /** LLM job queue - tracks every AI moderation/scoring task with full audit trail. */ export const llmJobs = pgTable( 'llm_jobs', { id: uuid('id').primaryKey().defaultRandom(), jobType: llmJobTypeEnum('job_type').notNull(), targetId: uuid('target_id').notNull(), targetType: llmTargetTypeEnum('target_type').notNull(), status: llmJobStatusEnum('status').notNull().default('PENDING'), /** Full input sent to the LLM (deal content, images, etc.). */ inputPayload: jsonb('input_payload').notNull(), /** Raw LLM response stored verbatim. */ outputPayload: jsonb('output_payload'), /** Snapshot of the prompt used at execution time - immutable audit trail. */ promptSnapshot: text('prompt_snapshot'), decision: llmDecisionEnum('decision'), flagReason: text('flag_reason'), startedAt: timestamp('started_at', { withTimezone: true }), completedAt: timestamp('completed_at', { withTimezone: true }), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), retryCount: integer('retry_count').notNull().default(0), lastError: text('last_error'), /** Token counts and cost for LLM cost tracking. */ promptTokens: integer('prompt_tokens'), completionTokens: integer('completion_tokens'), totalTokens: integer('total_tokens'), costUsd: numeric('cost_usd', { precision: 10, scale: 6 }), /** Actual model name used (may differ from modelUsed when fallback fires). */ modelName: text('model_name'), targetLocale: text('target_locale'), jobClass: text('job_class').notNull().default('BATCH'), notBefore: timestamp('not_before', { withTimezone: true }), modelUsed: text('model_used'), e2eRunId: uuid('e2e_run_id').references(() => e2eFactoryRuns.runId, { onDelete: 'cascade', }), queueChainOverride: jsonb('queue_chain_override').$type>(), }, (t) => [ index('llm_jobs_status_created_idx').on(t.status, t.createdAt), index('llm_jobs_target_idx').on(t.targetId, t.jobType), index('llm_jobs_e2e_run_idx').on(t.e2eRunId, t.status), ], );