/** * tenants + tenant_settings — foundation-auth-rbac. * * `tenants.settings JSONB` is free-form business-profile / locale / org-prefs * (owned by settings-module). `tenant_settings` is the per-tenant TYPED * module-config singleton: its BASE (id + UNIQUE tenant_id FK + timestamps) * is created HERE exactly once. Foundation is in every tenant's transitive * closure, so every downstream module can safely * ALTER TABLE tenant_settings ADD COLUMN IF NOT EXISTS * without a create-collision. Modules MUST NOT re-create this table. */ import { pgTable, uuid, text, boolean, jsonb, integer, numeric, timestamp, index, check } from 'drizzle-orm/pg-core' import { sql } from 'drizzle-orm' export const tenants = pgTable( 'tenants', { id: uuid('id').primaryKey().defaultRandom(), slug: text('slug').notNull().unique(), name: text('name').notNull(), // home-dashboard (P053): onboarding checklist state checklistDismissedAt: timestamp('checklist_dismissed_at', { withTimezone: true }), onboardingCompleted: boolean('onboarding_completed').notNull().default(false), // onboarding wizard (spec 29): 0=not started, 1=business info, 2=modules, 3=email, 4=invites, 5=complete onboardingStep: integer('onboarding_step').notNull().default(0), // tier: 'freelancer' | 'business' | 'enterprise' | 'white_label' tier: text('tier').notNull().default('freelancer'), requireApproval: boolean('require_approval').notNull().default(false), // auth-2fa: tenant-level 2FA enforcement settings enforce2fa: boolean('enforce_2fa').notNull().default(false), disable2faRememberDevice: boolean('disable_2fa_remember_device').notNull().default(false), // system-i18n: country/locale/currency settings countryCode: text('country_code').notNull().default('IL'), defaultCurrency: text('default_currency').notNull().default('ILS'), defaultTimezone: text('default_timezone').notNull().default('Asia/Jerusalem'), // app-shell: tenant logo for workspace switcher logoUrl: text('logo_url'), // Free-form business-profile, locale & org-prefs (owned by settings-module); // typed FK/CHECK module config lives in tenant_settings below. settings: jsonb('settings').notNull().default(sql`'{}'::jsonb`), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => ({ slugIdx: index('tenants_slug_idx').on(t.slug), tierCheck: check( 'tenants_tier_check', sql`${t.tier} IN ('freelancer', 'business', 'enterprise', 'white_label')`, ), countryCodeCheck: check( 'tenants_country_code_check', sql`${t.countryCode} IN ('IL')`, ), }), ) export const tenantSettings = pgTable( 'tenant_settings', { id: uuid('id').primaryKey().defaultRandom(), tenantId: uuid('tenant_id') .notNull() .unique() .references(() => tenants.id, { onDelete: 'cascade' }), forceShell: text('force_shell'), // ── expenses-module columns (ALTER TABLE … ADD COLUMN IF NOT EXISTS in migration) ── // VAT filing period filingCadence: text('filing_cadence').notNull().default('monthly'), // Accounting basis taxBasis: text('tax_basis').notNull().default('cash'), // Industry for AI deductibility context businessCategory: text('business_category'), // Per-diem daily rates in ILS perDiemRates: jsonb('per_diem_rates') .notNull() .default( sql`'{"domestic_full_day":100,"domestic_half_day":50,"international_full_day":250,"international_half_day":125}'::jsonb`, ) .$type<{ domestic_full_day: number domestic_half_day: number international_full_day: number international_half_day: number }>(), expenseDefaultCategory: text('expense_default_category').notNull().default('exceptional'), expenseAutoApproveThresholdIls: integer('expense_auto_approve_threshold_ils') .notNull() .default(0), expenseReceiptReminderEnabled: boolean('expense_receipt_reminder_enabled') .notNull() .default(false), expenseReceiptReminderDays: integer('expense_receipt_reminder_days').notNull().default(7), // Approval-workflow columns (Business+ gate; spec 61): expenseApprovalThresholdIls: integer('expense_approval_threshold_ils').notNull().default(0), expenseApproverRole: text('expense_approver_role').notNull().default('any_admin'), // Time-management columns (time-management spec) — CHECK added via raw_ddl ALTER TABLE: idleTimerThresholdMinutes: integer('idle_timer_threshold_minutes').notNull().default(10), timeRounding: text('time_rounding').notNull().default('none'), contractorRequireTimeApproval: boolean('contractor_require_time_approval').notNull().default(true), timeMinBillableMinutes: integer('time_min_billable_minutes').notNull().default(0), timeIdleThresholdMinutes: integer('time_idle_threshold_minutes').notNull().default(10), timeAutoPauseOnIdle: boolean('time_auto_pause_on_idle').notNull().default(true), timeStandardHoursPerDay: numeric('time_standard_hours_per_day', { precision: 4, scale: 2 }) .notNull() .default('8.0'), timeFlagOvertime: boolean('time_flag_overtime').notNull().default(false), timeRequireOvertimeApproval: boolean('time_require_overtime_approval').notNull().default(false), contractorTimeEnabled: boolean('contractor_time_enabled').notNull().default(true), mileageEnabled: boolean('mileage_enabled').notNull().default(false), timeMagicLinkEnabled: boolean('time_magic_link_enabled').notNull().default(true), // invoices-core: default payment terms for new invoices defaultPaymentTermsDays: integer('default_payment_terms_days').notNull().default(30), defaultTaxRate: numeric('default_tax_rate', { precision: 5, scale: 4 }).notNull().default('0.18'), invoiceNumberPrefix: text('invoice_number_prefix').notNull().default('INV-'), issueTaxInvoices: boolean('issue_tax_invoices').notNull().default(true), proformaNumberPrefix: text('proforma_number_prefix').notNull().default('PROFORMA-'), lateFeeType: text('late_fee_type').notNull().default('none'), lateFeeAmount: numeric('late_fee_amount', { precision: 10, scale: 2 }), lateFeeThresholdDays: integer('late_fee_threshold_days').default(30), invoiceFooterText: text('invoice_footer_text'), invoiceShowPaymentLink: boolean('invoice_show_payment_link').notNull().default(true), // ticket-sla-escalation (P063): SLA feature toggle slaEnabled: boolean('sla_enabled').notNull().default(false), // crm-support-center: days before resolved tickets auto-close (default 7) ticketAutoCloseDays: integer('ticket_auto_close_days').notNull().default(7), // project-settings (P062): project creation defaults projectDefaultBillingType: text('project_default_billing_type').notNull().default('fixed'), projectDefaultHourlyRate: numeric('project_default_hourly_rate', { precision: 10, scale: 2 }).notNull().default('0'), projectDefaultCurrency: text('project_default_currency').notNull().default('ILS'), projectDefaultTimeTracking: boolean('project_default_time_tracking').notNull().default(true), projectDefaultTimeRoundingMinutes: integer('project_default_time_rounding_minutes').notNull().default(0), projectDefaultBudgetAlertPct: integer('project_default_budget_alert_pct').notNull().default(80), projectBudgetAlertEmail: boolean('project_budget_alert_email').notNull().default(true), projectBudgetAlertInApp: boolean('project_budget_alert_in_app').notNull().default(true), // customer-portal-settings-ui (spec 136, wave 11): portal branding + access + permissions portalEnabled: boolean('portal_enabled').notNull().default(true), portalAccessMethod: text('portal_access_method').notNull().default('magic_link'), portalName: text('portal_name'), portalWelcomeText: text('portal_welcome_text'), portalPrimaryColorHex: text('portal_primary_color_hex'), portalLogoUrl: text('portal_logo_url'), portalCanSubmitTickets: boolean('portal_can_submit_tickets').notNull().default(false), portalCanUploadFiles: boolean('portal_can_upload_files').notNull().default(false), portalShowTeamMembers: boolean('portal_show_team_members').notNull().default(false), // portal_visibility is owned by customer-portal-access-control (spec 82, wave 10) — consumed here portalVisibility: jsonb('portal_visibility') .default({}) .$type>(), // settings-customers (spec, wave 11): customer defaults customerDefaultCurrency: text('customer_default_currency').notNull().default('ILS'), customerAutoInvitePortal: text('customer_auto_invite_portal').notNull().default('never'), customerDefaultPortalRole: text('customer_default_portal_role').notNull().default('customer_viewer'), // payment-retry-dunning (wave-11): dunning behavior toggle dunningSuspendAccess: boolean('dunning_suspend_access').notNull().default(false), // invoice-pdf-customization (wave-11): PDF template settings invoicePdfLayout: text('invoice_pdf_layout').notNull().default('classic'), invoicePdfShowProject: boolean('invoice_pdf_show_project').notNull().default(false), invoicePdfShowSku: boolean('invoice_pdf_show_sku').notNull().default(false), invoicePdfDateFormat: text('invoice_pdf_date_format').notNull().default('dd.MM.yyyy'), // null = use system default accent invoicePdfAccentHex: text('invoice_pdf_accent_hex'), // contractor-settings (wave-11 leaf-E): contractor portal + payout settings // contractor_require_time_approval already above (owned by time-management) contractorPortalEnabled: boolean('contractor_portal_enabled').notNull().default(true), contractorPortalShowBilling: boolean('contractor_portal_show_billing').notNull().default(false), contractorApprovalDeadlineDays: integer('contractor_approval_deadline_days').notNull().default(7), // CHECK(contractor_payout_period IN ('weekly','biweekly','monthly')) — enforced in migration SQL contractorPayoutPeriod: text('contractor_payout_period').notNull().default('monthly'), contractorWithholdingNote: text('contractor_withholding_note'), // proposal-expiry-deadline (wave-12): tenant default valid days for new proposals // NULL = no default expiry; positive integer = days from send date proposalDefaultValidDays: integer('proposal_default_valid_days'), // bad-debt-writeoff (wave-12): days overdue before an invoice is surfaced for write-off badDebtThresholdDays: integer('bad_debt_threshold_days').notNull().default(90), // invoice-payment-reminders (wave-13): reminder toggle + configurable schedule invoiceRemindersEnabled: boolean('invoice_reminders_enabled').notNull().default(true), invoiceReminderSchedule: jsonb('invoice_reminder_schedule') .notNull() .default( sql`'[{"offset_days":-3,"enabled":true},{"offset_days":0,"enabled":true},{"offset_days":7,"enabled":true},{"offset_days":14,"enabled":true},{"offset_days":30,"enabled":true}]'::jsonb`, ) .$type>(), // lead-qualification-scoring (wave-14): per-tenant scoring weights + threshold leadScoringCriteria: jsonb('lead_scoring_criteria') .notNull() .default( sql`'{"estimated_value_set":10,"high_value":20,"stage_advancement":15,"recent_activity":20,"inbound_source":10,"company_present":5,"phone_present":5,"proposal_viewed":15,"high_value_threshold":20000}'::jsonb`, ) .$type<{ estimated_value_set: number high_value: number stage_advancement: number recent_activity: number inbound_source: number company_present: number phone_present: number proposal_viewed: number high_value_threshold: number }>(), // israeli-tax-reports (wave-14): VAT filing period + advance tax rate // CHECK (vat_period IN ('monthly','bimonthly')) enforced in migration SQL vatPeriod: text('vat_period').notNull().default('bimonthly'), // ITA-assigned advance payment rate (% of annual tax); NULL = not configured advanceTaxRatePct: numeric('advance_tax_rate_pct', { precision: 5, scale: 2 }), // Manually-entered YTD advance payments already filed (UI: Advance payments YTD); NULL/0 = none advancePaymentsYtdIls: numeric('advance_payments_ytd_ils', { precision: 14, scale: 2 }).default( '0', ), // settings-contracts (wave-13, P165): contract-wide configuration columns // FK to contract_templates(id) — ON DELETE SET NULL (handled in migration SQL) contractDefaultTemplateId: uuid('contract_default_template_id'), contractDefaultExpiryDays: integer('contract_default_expiry_days').notNull().default(365), contractDefaultSigningOrder: text('contract_default_signing_order').notNull().default('parallel'), contractRequireEmailVerification: boolean('contract_require_email_verification').notNull().default(true), contractReminderFirstDays: integer('contract_reminder_first_days').notNull().default(3), contractReminderFollowupDays: integer('contract_reminder_followup_days').notNull().default(7), contractReminderMaxCount: integer('contract_reminder_max_count').notNull().default(3), contractRenewalRemind30d: boolean('contract_renewal_remind_30d').notNull().default(true), contractRenewalRemind7d: boolean('contract_renewal_remind_7d').notNull().default(true), contractRenewalRemind1d: boolean('contract_renewal_remind_1d').notNull().default(false), contractRenewalNotifyEmail: boolean('contract_renewal_notify_email').notNull().default(true), contractRenewalNotifyInApp: boolean('contract_renewal_notify_in_app').notNull().default(true), // settings-kb (wave-14): KB publication workflow + versioning + default space kbRequireReview: boolean('kb_require_review').notNull().default(false), kbVersioningEnabled: boolean('kb_versioning_enabled').notNull().default(true), // FK to kb_spaces(id) ON DELETE SET NULL — handled in migration SQL kbDefaultSpaceId: uuid('kb_default_space_id'), // lead-lost-re-engagement (wave-14): configurable list of lost reason labels leadLostReasons: jsonb('lead_lost_reasons') .default( sql`'["Price too high","Chose competitor","Not the right time","No budget","Unresponsive"]'::jsonb`, ) .$type(), // settings-crm (wave-15): pipeline stage definitions (NULL = use system defaults) pipelineStages: jsonb('pipeline_stages') .$type>(), inventoryMethod: text('inventory_method').notNull().default('fifo'), allowNegative: boolean('allow_negative').notNull().default(true), // settings-crm (wave-15): scoring toggle; when false, cron skips score computation leadScoringEnabled: boolean('lead_scoring_enabled').notNull().default(true), // revenue-forecasting (spec 116): tenant CRM stage → probability map for weighted forecast leadStageProbabilities: jsonb('lead_stage_probabilities') .notNull() .default( sql`'{"NEW":5,"CONTACTED":15,"QUALIFIED":30,"PROPOSAL":60}'::jsonb`, ) .$type<{ NEW: number CONTACTED: number QUALIFIED: number PROPOSAL: number }>(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), }, (t) => ({ lateFeeTypeCheck: check( 'tenant_settings_late_fee_type_check', sql`${t.lateFeeType} IN ('none', 'flat', 'percentage')`, ), filingCadenceCheck: check( 'tenant_settings_filing_cadence_check', sql`${t.filingCadence} IN ('monthly','bimonthly')`, ), taxBasisCheck: check( 'tenant_settings_tax_basis_check', sql`${t.taxBasis} IN ('cash','accrual')`, ), projectDefaultBillingTypeCheck: check( 'tenant_settings_project_default_billing_type_check', sql`${t.projectDefaultBillingType} IN ('fixed','hourly','retainer')`, ), projectDefaultTimeRoundingCheck: check( 'tenant_settings_project_default_time_rounding_check', sql`${t.projectDefaultTimeRoundingMinutes} IN (0,5,15,30)`, ), projectDefaultBudgetAlertPctCheck: check( 'tenant_settings_project_default_budget_alert_pct_check', sql`${t.projectDefaultBudgetAlertPct} BETWEEN 1 AND 100`, ), portalAccessMethodCheck: check( 'tenant_settings_portal_access_method_check', sql`${t.portalAccessMethod} IN ('magic_link', 'password')`, ), customerAutoInvitePortalCheck: check( 'tenant_settings_customer_auto_invite_portal_check', sql`${t.customerAutoInvitePortal} IN ('never', 'on_creation', 'on_first_invoice')`, ), customerDefaultPortalRoleCheck: check( 'tenant_settings_customer_default_portal_role_check', sql`${t.customerDefaultPortalRole} IN ('customer_viewer')`, ), invoicePdfLayoutCheck: check( 'tenant_settings_invoice_pdf_layout_check', sql`${t.invoicePdfLayout} IN ('classic','modern','minimal')`, ), invoicePdfDateFormatCheck: check( 'tenant_settings_invoice_pdf_date_format_check', sql`${t.invoicePdfDateFormat} IN ('dd.MM.yyyy','yyyy-MM-dd','MMMM d, yyyy')`, ), vatPeriodCheck: check( 'tenant_settings_vat_period_check', sql`${t.vatPeriod} IN ('monthly','bimonthly')`, ), forceShellCheck: check( 'tenant_settings_force_shell_check', sql`${t.forceShell} IN ('classic','os') OR ${t.forceShell} IS NULL`, ), }), ) export type TenantRow = typeof tenants.$inferSelect export type NewTenant = typeof tenants.$inferInsert export type TenantSettingsRow = typeof tenantSettings.$inferSelect