import { pgTable, serial, text, boolean, timestamp, integer, pgEnum, numeric, json, index, uniqueIndex, } from "drizzle-orm/pg-core"; import { sql } from "drizzle-orm"; import { createInsertSchema } from "drizzle-zod"; import { z } from "zod/v4"; // ═══════════════════════════════════════════════════════════════ // ENUMS // ═══════════════════════════════════════════════════════════════ export const userRoleEnum = pgEnum("user_role_enum", [ "MEMBER", "ADMIN", "SUPER_ADMIN", ]); export const userSegmentEnum = pgEnum("user_segment_enum", [ "PROFESSIONAL", "ENTREPRENEUR", "YOUTH", "SCHOOL", "INSTITUTION", ]); export const membershipTypeEnum = pgEnum("membership_type_enum", [ "INDIVIDUAL", "ORGANIZATION", "INVESTOR", ]); export const membershipStatusEnum = pgEnum("membership_status_enum", [ "PENDING", "ACTIVE", "SUSPENDED", "EXPIRED", "CANCELLED", ]); export const membershipPlanTierEnum = pgEnum("membership_plan_tier_enum", [ "STARTER", "PROFESSIONAL", "ELITE", "ENTREPRENEUR", "YOUTH", "COMMUNITY", "EDUCATION", "GRASSROOT", "WORKER", "IMPACT", "SPONSOR", ]); export const investorCategoryEnum = pgEnum("investor_category_enum", [ "WORKER_OWNER", "COMMUNITY_SPONSOR", "IMPACT_INVESTOR", ]); export const planBillingModeEnum = pgEnum("plan_billing_mode_enum", [ "ONE_TIME", "RECURRING", ]); export const planBillingCycleEnum = pgEnum("plan_billing_cycle_enum", [ "MONTHLY", "ANNUAL", ]); export const contributionFrequencyEnum = pgEnum("contribution_frequency_enum", [ "WEEKLY", "BIWEEKLY", "MONTHLY", ]); export const lendingRepaymentFrequencyEnum = pgEnum( "lending_repayment_frequency_enum", ["WEEKLY", "BIWEEKLY", "MONTHLY"], ); export const contributionStatusEnum = pgEnum("contribution_status_enum", [ "PAID", "PENDING", "MISSED", "WAIVED", ]); export const paymentMethodEnum = pgEnum("payment_method_enum", [ "STRIPE", "EFT", "INTERAC", "WIRE", "WALLET", ]); export const paymentStatusEnum = pgEnum("payment_status_enum", [ "PENDING", "COMPLETED", "FAILED", "REFUNDED", ]); export const invoiceStatusEnum = pgEnum("invoice_status_enum", [ "DRAFT", "SENT", "PAID", "OVERDUE", "CANCELLED", ]); export const circleStatusEnum = pgEnum("circle_status_enum", [ "FORMING", "UPCOMING", "ACTIVE", "COMPLETED", "CANCELLED", ]); export const payoutStatusEnum = pgEnum("payout_status_enum", [ "PENDING", "PAID", "SKIPPED", ]); export const walletOwnerTypeEnum = pgEnum("wallet_owner_type_enum", [ "USER", "ORGANIZATION", "SUSU", "INVESTMENT", "CIRCLE", "LENDING_POOL", "LENDING_MASTER", "PLATFORM", "ASCA", ]); export const walletTransactionTypeEnum = pgEnum( "wallet_transaction_type_enum", [ "TOP_UP", "CASH_OUT", "REFUND", "CONTRIBUTION", "PAYOUT", "ADJUSTMENT", "INVESTMENT_PURCHASE", "INVESTMENT_PRINCIPAL_PAYOUT", "INVESTMENT_RETURN_PAYOUT", "LENDING_PRINCIPAL_PAYOUT", "LENDING_RETURN_PAYOUT", "LOAN_DISBURSEMENT", "LOAN_REPAYMENT", "HELPING_HAND_DISBURSEMENT", "HELPING_HAND_REPAYMENT", "ASCA_CONTRIBUTION", "ASCA_REFUND", "ASCA_INVESTMENT", "ASCA_OFFLINE_WITHDRAWAL", ], ); export const paymentProviderEnum = pgEnum("payment_provider_enum", [ "STRIPE", "INTERAC", "MANUAL", ]); export const walletPaymentIntentTypeEnum = pgEnum( "wallet_payment_intent_type_enum", ["TOP_UP", "CASH_OUT", "REFUND"], ); export const walletPaymentIntentStatusEnum = pgEnum( "wallet_payment_intent_status_enum", ["CREATED", "PENDING", "SUCCEEDED", "FAILED", "CANCELLED"], ); export const webhookEventStatusEnum = pgEnum("webhook_event_status_enum", [ "PENDING", "PROCESSED", "FAILED", ]); export const helpingHandStatusEnum = pgEnum("helping_hand_status_enum", [ "DRAFT", "SUBMITTED", "UNDER_REVIEW", "APPROVED", "REJECTED", "FUNDED", "REPAYING", "CLOSED", ]); export const loanApplicationStatusEnum = pgEnum( "loan_application_status_enum", [ "DRAFT", "SUBMITTED", "WAITLISTED", "UNDER_REVIEW", "APPROVED", "APPROVED_RESERVED", "REJECTED", "CANCELLED", "FUNDED", "REPAYING", "CLOSED", ], ); export const repaymentStatusEnum = pgEnum("repayment_status_enum", [ "PENDING", "PAID", "LATE", "WAIVED", ]); export const investmentCategoryEnum = pgEnum("investment_category_enum", [ "REAL_ESTATE", "AGRICULTURE", "SMALL_BUSINESS", "COMMUNITY_DEVELOPMENT", "BONDS", "OTHER", ]); export const investmentOpportunityStatusEnum = pgEnum( "investment_opportunity_status_enum", ["DRAFT", "OPEN", "FUNDED", "CLOSED"], ); export const investmentInterestStatusEnum = pgEnum( "investment_interest_status_enum", ["PENDING", "CONFIRMED", "CANCELLED"], ); export const investmentIntentReservationStatusEnum = pgEnum( "investment_intent_reservation_status_enum", ["RESERVED", "FINALIZED", "RELEASED"], ); export const riskLevelEnum = pgEnum("risk_level_enum", [ "LOW", "MEDIUM", "HIGH", ]); export const lendingPoolStatusEnum = pgEnum("lending_pool_status_enum", [ "DRAFT", "OPEN", "PAUSED", "CLOSED", ]); export const commitmentStatusEnum = pgEnum("commitment_status_enum", [ "PENDING", "CONFIRMED", "CANCELLED", ]); export const lendingPoolReturnModelEnum = pgEnum( "lending_pool_return_model_enum", ["FIXED_SIMPLE"], ); export const lendingMinAppreciationTypeEnum = pgEnum( "lending_min_appreciation_type_enum", ["NONE", "FIXED", "PCT_OF_PRINCIPAL", "TERM_TIERED"], ); export const lendingLoanTypeEnum = pgEnum("lending_loan_type_enum", [ "COMMUNITY_LENDING", "P2P_LENDING", ]); export const kycStatusEnum = pgEnum("kyc_status_enum", [ "PENDING", "APPROVED", "REJECTED", ]); export const eventTypeEnum = pgEnum("event_type_enum", [ "VIRTUAL", "IN_PERSON", "HYBRID", ]); export const rsvpStatusEnum = pgEnum("rsvp_status_enum", [ "GOING", "WAITLISTED", "CANCELLED", ]); export const notificationCategoryEnum = pgEnum("notification_category_enum", [ "SYSTEM", "MEMBERSHIP", "SAVINGS", "LENDING", "INVESTMENT", "ACADEMY", "PAYMENT", "COMMUNITY", ]); export const notificationSeverityEnum = pgEnum("notification_severity_enum", [ "INFO", "SUCCESS", "WARNING", "ERROR", ]); export const pushPlatformEnum = pgEnum("push_platform_enum", ["EXPO"]); export const notificationChannelEnum = pgEnum("notification_channel_enum", [ "PUSH", "EMAIL", ]); export const notificationDeliveryStatusEnum = pgEnum( "notification_delivery_status_enum", ["SENT", "FAILED", "SKIPPED"], ); export const resourceFileTypeEnum = pgEnum("resource_file_type_enum", [ "PDF", "VIDEO", "DOCUMENT", "SPREADSHEET", "IMAGE", "LINK", "OTHER", ]); export const resourceKindEnum = pgEnum("resource_kind_enum", [ "FILE", "ARTICLE", ]); export const orgMemberRoleEnum = pgEnum("org_member_role_enum", [ "OWNER", "ADMIN", "MEMBER", ]); export const organizationMemberStatusEnum = pgEnum( "organization_member_status_enum", ["PENDING", "ACTIVE"], ); export const intakeStatusEnum = pgEnum("intake_status_enum", [ "PENDING", "IN_REVIEW", "QUOTED", "ACCEPTED", "COMPLETED", "CANCELLED", ]); export const organizationTypeEnum = pgEnum("organization_type_enum", [ "SCHOOL", "GRASSROOTS_ROSCA", "NFP_ALUMNI_FAITH", ]); export const organizationOperationalStatusEnum = pgEnum( "organization_operational_status_enum", ["PENDING", "ACTIVE", "SUSPENDED", "ARCHIVED"], ); export const organizationInvitationStatusEnum = pgEnum( "organization_invitation_status_enum", ["PENDING", "ACCEPTED", "CANCELLED", "EXPIRED"], ); export const organizationMemberObligationStatusEnum = pgEnum( "organization_member_obligation_status_enum", ["PENDING", "PAID", "CANCELLED"], ); export const organizationMemberObligationKindEnum = pgEnum( "organization_member_obligation_kind_enum", ["TREASURY", "ORG_SUSU"], ); export const onboardingProgramStatusEnum = pgEnum( "onboarding_program_status_enum", ["DRAFT", "PUBLISHED", "ARCHIVED"], ); export const onboardingProgramDurationUnitEnum = pgEnum( "onboarding_program_duration_unit_enum", ["WEEK", "DAY"], ); export const onboardingActivityTypeEnum = pgEnum( "onboarding_activity_type_enum", [ "VIDEO", "FILE", "RICH_TEXT", "EXTERNAL_LINK", "QUIZ", "CHECKLIST", "SURVEY", ], ); export const onboardingActivityStatusEnum = pgEnum( "onboarding_activity_status_enum", ["NOT_STARTED", "IN_PROGRESS", "COMPLETED"], ); export const ascaTypeEnum = pgEnum("asca_type_enum", ["GENERAL", "INVESTMENT"]); export const ascaStatusEnum = pgEnum("asca_status_enum", [ "DRAFT", "FUNDING", "READY", "PENDING_INVESTMENT", "INVESTED", "MATURED", "CLOSED", "CANCELLED", ]); export const ascaMemberStatusEnum = pgEnum("asca_member_status_enum", [ "ACTIVE", "DEFAULTED", "WITHDRAWN", ]); export const ascaContributionStatusEnum = pgEnum( "asca_contribution_status_enum", ["PENDING", "PAID", "LATE", "REFUNDED"], ); // ═══════════════════════════════════════════════════════════════ // ORGANIZATION // ═══════════════════════════════════════════════════════════════ export const organizationsTable = pgTable("organizations", { id: serial("id").primaryKey(), name: text("name").notNull(), type: text("type").notNull(), organizationType: organizationTypeEnum("organization_type"), operationalStatus: organizationOperationalStatusEnum("operational_status") .notNull() .default("PENDING"), description: text("description"), website: text("website"), contactName: text("contact_name"), contactTitle: text("contact_title"), businessType: text("business_type"), yearsInBusiness: text("years_in_business"), annualRevenue: text("annual_revenue"), province: text("province"), city: text("city"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); // ═══════════════════════════════════════════════════════════════ // USER & AUTH // ═══════════════════════════════════════════════════════════════ export const usersTable = pgTable( "users", { id: serial("id").primaryKey(), email: text("email").notNull().unique(), username: text("username").unique(), phone: text("phone"), passwordHash: text("password_hash").notNull(), firstName: text("first_name").notNull(), lastName: text("last_name"), segment: userSegmentEnum("segment"), membershipType: membershipTypeEnum("membership_type"), membershipStatus: membershipStatusEnum("membership_status") .notNull() .default("PENDING"), role: text("role").default("member"), userRole: userRoleEnum("user_role").notNull().default("MEMBER"), isEmailVerified: boolean("is_email_verified").notNull().default(false), bio: text("bio"), avatarUrl: text("avatar_url"), city: text("city"), province: text("province"), contactAddress: text("contact_address"), contactStreet: text("contact_street"), contactPostalCode: text("contact_postal_code"), contactCountryIso2: text("contact_country_iso2"), ageRange: text("age_range"), occupation: text("occupation"), employmentEmployed: boolean("employment_employed"), employmentStatus: text("employment_status"), employerName: text("employer_name"), employerAddress: text("employer_address"), employmentPosition: text("employment_position"), businessName: text("business_name"), businessType: text("business_type"), yearsInBusiness: text("years_in_business"), annualRevenue: text("annual_revenue"), howHeard: text("how_heard"), referrerName: text("referrer_name"), referrerPhone: text("referrer_phone"), referrerWwnId: text("referrer_wwn_id"), referrerAddress: text("referrer_address"), referrerStreet: text("referrer_street"), referrerCity: text("referrer_city"), referrerPostalCode: text("referrer_postal_code"), goals: text("goals"), consentAccepted: boolean("consent_accepted").notNull().default(false), consentAcceptedAt: timestamp("consent_accepted_at"), nextOfKinFirstName: text("next_of_kin_first_name"), nextOfKinLastName: text("next_of_kin_last_name"), nextOfKinOtherNames: text("next_of_kin_other_names"), nextOfKinGender: text("next_of_kin_gender"), nextOfKinResidentialAddress: text("next_of_kin_residential_address"), nextOfKinStreet: text("next_of_kin_street"), nextOfKinCity: text("next_of_kin_city"), nextOfKinPostalCode: text("next_of_kin_postal_code"), nextOfKinEmail: text("next_of_kin_email"), nextOfKinPhone: text("next_of_kin_phone"), nextOfKinRelationship: text("next_of_kin_relationship"), nextOfKinOccupation: text("next_of_kin_occupation"), kycIdFrontUrl: text("kyc_id_front_url"), kycIdBackUrl: text("kyc_id_back_url"), kycSelfieUrl: text("kyc_selfie_url"), kycStatus: kycStatusEnum("kyc_status"), kycSubmittedAt: timestamp("kyc_submitted_at"), kycReviewedAt: timestamp("kyc_reviewed_at"), kycReviewedByUserId: integer("kyc_reviewed_by_user_id"), kycRejectionReason: text("kyc_rejection_reason"), investorCategory: investorCategoryEnum("investor_category"), organizationId: integer("organization_id").references( () => organizationsTable.id, ), onboardingCompletedAt: timestamp("onboarding_completed_at"), lastLoginAt: timestamp("last_login_at"), authTokenVersion: integer("auth_token_version").notNull().default(0), membershipRegistrationId: text("membership_registration_id"), deletedAt: timestamp("deleted_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("users_membership_created_idx").on( t.membershipStatus, t.membershipType, t.createdAt, ), uniqueIndex("users_membership_registration_id_uidx").on( t.membershipRegistrationId, ), ], ); export const verificationTokensTable = pgTable("verification_tokens", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), codeHash: text("code_hash").notNull(), expiresAt: timestamp("expires_at").notNull(), attempts: integer("attempts").notNull().default(0), createdAt: timestamp("created_at").notNull().defaultNow(), }); export const passwordResetTokensTable = pgTable("password_reset_tokens", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), tokenHash: text("token_hash").notNull(), expiresAt: timestamp("expires_at").notNull(), attempts: integer("attempts").notNull().default(0), isExchangeToken: boolean("is_exchange_token").notNull().default(false), used: boolean("used").notNull().default(false), createdAt: timestamp("created_at").notNull().defaultNow(), }); export const organizationMembersTable = pgTable( "organization_members", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), role: orgMemberRoleEnum("role").notNull().default("MEMBER"), status: organizationMemberStatusEnum("status").notNull().default("ACTIVE"), joinedAt: timestamp("joined_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("org_members_org_user_uidx").on(t.organizationId, t.userId), ], ); export const organizationInvitationsTable = pgTable( "organization_invitations", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), email: text("email").notNull(), firstName: text("first_name"), lastName: text("last_name"), role: orgMemberRoleEnum("role").notNull().default("MEMBER"), invitedByUserId: integer("invited_by_user_id").references( () => usersTable.id, { onDelete: "set null", }, ), tokenHash: text("token_hash").notNull().unique(), status: organizationInvitationStatusEnum("status") .notNull() .default("PENDING"), expiresAt: timestamp("expires_at").notNull(), acceptedAt: timestamp("accepted_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_invites_org_status_created_idx").on( t.organizationId, t.status, t.createdAt, ), uniqueIndex("org_invites_org_email_pending_uidx") .on(t.organizationId, t.email) .where(sql`${t.status} = 'PENDING'`), ], ); export const organizationIntakesTable = pgTable( "organization_intakes", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), programInterests: text("program_interests"), annualBudget: text("annual_budget"), staffCount: text("staff_count"), goals: text("goals"), additionalNotes: text("additional_notes"), status: intakeStatusEnum("status").notNull().default("PENDING"), quoteAmount: numeric("quote_amount", { precision: 12, scale: 2 }), quoteNote: text("quote_note"), quoteLink: text("quote_link"), reviewedByAdminId: integer("reviewed_by_admin_id"), reviewedAt: timestamp("reviewed_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_intakes_org_created_idx").on(t.organizationId, t.createdAt), index("org_intakes_org_updated_idx").on(t.organizationId, t.updatedAt), ], ); export const organizationMemberObligationsTable = pgTable( "organization_member_obligations", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), memberUserId: integer("member_user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), assignedByUserId: integer("assigned_by_user_id").references( () => usersTable.id, { onDelete: "set null", }, ), kind: organizationMemberObligationKindEnum("kind") .notNull() .default("TREASURY"), title: text("title").notNull(), description: text("description"), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), currency: text("currency").notNull().default("CAD"), status: organizationMemberObligationStatusEnum("status") .notNull() .default("PENDING"), dueDate: timestamp("due_date"), paidAt: timestamp("paid_at"), walletTransactionId: integer("wallet_transaction_id"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_member_obligation_org_member_status_idx").on( t.organizationId, t.memberUserId, t.status, ), index("org_member_obligation_org_created_idx").on( t.organizationId, t.createdAt, ), ], ); export const organizationBankSettingsTable = pgTable( "organization_bank_settings", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .unique() .references(() => organizationsTable.id, { onDelete: "cascade" }), eftAccountName: text("eft_account_name").notNull(), eftInstitutionNumber: text("eft_institution_number").notNull(), eftTransitNumber: text("eft_transit_number").notNull(), eftAccountNumber: text("eft_account_number").notNull(), interacRecipientName: text("interac_recipient_name").notNull(), interacEmail: text("interac_email").notNull(), cashoutMinAmount: numeric("cashout_min_amount", { precision: 12, scale: 2, }).notNull(), cashoutFeePct: numeric("cashout_fee_pct", { precision: 6, scale: 3, }).notNull(), cashoutFeeFixed: numeric("cashout_fee_fixed", { precision: 12, scale: 2, }).notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_bank_settings_org_updated_idx").on( t.organizationId, t.updatedAt, ), ], ); export const organizationMembershipIdSettingsTable = pgTable( "organization_membership_id_settings", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .unique() .references(() => organizationsTable.id, { onDelete: "cascade" }), prefix: text("prefix").notNull().default("WWN"), digits: integer("digits").notNull().default(7), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_membership_id_settings_org_updated_idx").on( t.organizationId, t.updatedAt, ), ], ); // ═══════════════════════════════════════════════════════════════ // MEMBERSHIP PLANS & SUBSCRIPTIONS // ═══════════════════════════════════════════════════════════════ export const membershipPlansTable = pgTable("membership_plans", { id: serial("id").primaryKey(), membershipType: membershipTypeEnum("membership_type").notNull(), organizationType: organizationTypeEnum("organization_type"), tier: membershipPlanTierEnum("tier").notNull(), name: text("name").notNull(), description: text("description"), priceMonthly: numeric("price_monthly", { precision: 10, scale: 2, }).notNull(), priceAnnual: numeric("price_annual", { precision: 10, scale: 2 }).notNull(), currency: text("currency").notNull().default("CAD"), features: json("features").notNull().default([]), entitlements: json("entitlements") .$type>() .notNull() .default({}), isActive: boolean("is_active").notNull().default(true), sortOrder: integer("sort_order").notNull().default(0), billingMode: planBillingModeEnum("billing_mode") .notNull() .default("RECURRING"), defaultBillingCycle: planBillingCycleEnum("default_billing_cycle") .notNull() .default("ANNUAL"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const membershipSubscriptionsTable = pgTable( "membership_subscriptions", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), planId: integer("plan_id") .notNull() .references(() => membershipPlansTable.id), status: membershipStatusEnum("status").notNull().default("PENDING"), startDate: timestamp("start_date"), endDate: timestamp("end_date"), renewsAt: timestamp("renews_at"), cancelledAt: timestamp("cancelled_at"), stripeCustomerId: text("stripe_customer_id"), stripeSubscriptionId: text("stripe_subscription_id"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("member_sub_user_status_created_idx").on( t.userId, t.status, t.createdAt, ), uniqueIndex("member_sub_user_active_uidx") .on(t.userId) .where(sql`${t.status} in ('PENDING', 'ACTIVE')`), ], ); export const organizationSubscriptionsTable = pgTable( "organization_subscriptions", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), planId: integer("plan_id") .notNull() .references(() => membershipPlansTable.id), status: membershipStatusEnum("status").notNull().default("PENDING"), startDate: timestamp("start_date"), endDate: timestamp("end_date"), renewsAt: timestamp("renews_at"), cancelledAt: timestamp("cancelled_at"), stripeCustomerId: text("stripe_customer_id"), stripeSubscriptionId: text("stripe_subscription_id"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_sub_org_status_created_idx").on( t.organizationId, t.status, t.createdAt, ), uniqueIndex("org_sub_org_active_uidx") .on(t.organizationId) .where(sql`${t.status} in ('PENDING', 'ACTIVE')`), ], ); // ═══════════════════════════════════════════════════════════════ // PAYMENTS & INVOICES // ═══════════════════════════════════════════════════════════════ export const invoicesTable = pgTable( "invoices", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), subscriptionId: integer("subscription_id").references( () => membershipSubscriptionsTable.id, ), invoiceNumber: text("invoice_number").notNull().unique(), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), currency: text("currency").notNull().default("CAD"), status: invoiceStatusEnum("status").notNull().default("DRAFT"), dueDate: timestamp("due_date").notNull(), paidAt: timestamp("paid_at"), pdfUrl: text("pdf_url"), lineItems: json("line_items").notNull().default([]), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("invoices_user_created_idx").on(t.userId, t.createdAt), index("invoices_sub_created_idx").on(t.subscriptionId, t.createdAt), ], ); export const paymentsTable = pgTable( "payments", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), currency: text("currency").notNull().default("CAD"), method: paymentMethodEnum("method").notNull(), status: paymentStatusEnum("status").notNull().default("PENDING"), reference: text("reference"), description: text("description"), metadata: json("metadata"), invoiceId: integer("invoice_id").references(() => invoicesTable.id), processedAt: timestamp("processed_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("payments_invoice_created_idx").on(t.invoiceId, t.createdAt), index("payments_user_created_idx").on(t.userId, t.createdAt), index("payments_status_created_idx").on(t.status, t.createdAt), ], ); // ═══════════════════════════════════════════════════════════════ // WWN ACADEMY DIRECTORY // ═══════════════════════════════════════════════════════════════ export const academyCategoriesTable = pgTable("academy_categories", { id: serial("id").primaryKey(), name: text("name").notNull().unique(), slug: text("slug").notNull().unique(), sortOrder: integer("sort_order").notNull().default(0), }); export const academyCoursesTable = pgTable("academy_courses", { id: serial("id").primaryKey(), title: text("title").notNull(), slug: text("slug").notNull().unique(), description: text("description").notNull(), thumbnailUrl: text("thumbnail_url"), categoryId: integer("category_id") .notNull() .references(() => academyCategoriesTable.id), instructor: text("instructor").notNull(), duration: text("duration").notNull(), level: text("level").notNull(), rating: numeric("rating", { precision: 3, scale: 1 }), enrolledCount: integer("enrolled_count").notNull().default(0), externalUrl: text("external_url").notNull(), tags: json("tags").notNull().default([]), isPublished: boolean("is_published").notNull().default(true), sortOrder: integer("sort_order").notNull().default(0), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const courseInterestsTable = pgTable( "course_interests", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), courseId: integer("course_id") .notNull() .references(() => academyCoursesTable.id, { onDelete: "cascade" }), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("course_interests_user_course_uidx").on(t.userId, t.courseId), ], ); // ═══════════════════════════════════════════════════════════════ // SUSU SAVINGS CIRCLES // ═══════════════════════════════════════════════════════════════ export const savingsCirclesTable = pgTable( "savings_circles", { id: serial("id").primaryKey(), name: text("name").notNull(), description: text("description"), termsHtml: text("terms_html"), contributionAmount: numeric("contribution_amount", { precision: 10, scale: 2, }).notNull(), frequency: contributionFrequencyEnum("frequency") .notNull() .default("MONTHLY"), helpingHandMultiplier: numeric("helping_hand_multiplier", { precision: 6, scale: 2, }), orgAutoDeductEnabled: boolean("org_auto_deduct_enabled") .notNull() .default(false), isFreeToJoin: boolean("is_free_to_join").notNull().default(false), maxMembers: integer("max_members").notNull(), formingTimeoutDays: integer("forming_timeout_days").notNull().default(14), status: circleStatusEnum("status").notNull().default("UPCOMING"), startDate: timestamp("start_date"), endDate: timestamp("end_date"), organizationId: integer("organization_id").references( () => organizationsTable.id, { onDelete: "cascade" }, ), createdByAdminId: integer("created_by_admin_id"), createdByOrganizationMemberUserId: integer( "created_by_organization_member_user_id", ).references(() => usersTable.id, { onDelete: "set null" }), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("savings_circles_org_idx").on(t.organizationId, t.createdAt), index("savings_circles_org_status_idx").on(t.organizationId, t.status), ], ); export const circleMembershipsTable = pgTable( "circle_memberships", { id: serial("id").primaryKey(), circleId: integer("circle_id") .notNull() .references(() => savingsCirclesTable.id, { onDelete: "cascade" }), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), position: integer("position").notNull(), memberAutoDeductEnabled: boolean("member_auto_deduct_enabled") .notNull() .default(false), joinedAt: timestamp("joined_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("circle_memberships_circle_user_uidx").on(t.circleId, t.userId), uniqueIndex("circle_memberships_circle_pos_uidx").on( t.circleId, t.position, ), index("circle_memberships_user_idx").on(t.userId), ], ); export const savingsContributionsTable = pgTable( "savings_contributions", { id: serial("id").primaryKey(), circleId: integer("circle_id") .notNull() .references(() => savingsCirclesTable.id), userId: integer("user_id") .notNull() .references(() => usersTable.id), dueDate: timestamp("due_date").notNull(), paidAt: timestamp("paid_at"), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), status: contributionStatusEnum("status").notNull().default("PENDING"), method: paymentMethodEnum("method"), receiptRef: text("receipt_ref"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("savings_contributions_circle_user_due_uidx").on( t.circleId, t.userId, t.dueDate, ), index("savings_contrib_circle_status_due_idx").on( t.circleId, t.status, t.dueDate, ), index("savings_contrib_user_due_idx").on(t.userId, t.dueDate), ], ); export const circlePayoutsTable = pgTable( "circle_payouts", { id: serial("id").primaryKey(), circleId: integer("circle_id") .notNull() .references(() => savingsCirclesTable.id), userId: integer("user_id").notNull(), position: integer("position").notNull(), scheduledDate: timestamp("scheduled_date").notNull(), paidDate: timestamp("paid_date"), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), status: payoutStatusEnum("status").notNull().default("PENDING"), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("circle_payouts_circle_user_pos_uidx").on( t.circleId, t.userId, t.position, ), index("circle_payouts_circle_status_sched_idx").on( t.circleId, t.status, t.scheduledDate, ), ], ); export const walletsTable = pgTable( "wallets", { id: serial("id").primaryKey(), ownerType: walletOwnerTypeEnum("owner_type").notNull(), ownerId: integer("owner_id").notNull(), currency: text("currency").notNull().default("CAD"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [uniqueIndex("wallets_owner_uidx").on(t.ownerType, t.ownerId)], ); export const walletTransactionsTable = pgTable( "wallet_transactions", { id: serial("id").primaryKey(), type: walletTransactionTypeEnum("type").notNull(), idempotencyKey: text("idempotency_key"), fromWalletId: integer("from_wallet_id").references(() => walletsTable.id), toWalletId: integer("to_wallet_id").references(() => walletsTable.id), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), metadata: json("metadata").notNull().default({}), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("wallet_transactions_idempotency_uidx").on(t.idempotencyKey), index("wallet_txs_from_created_idx").on(t.fromWalletId, t.createdAt), index("wallet_txs_to_created_idx").on(t.toWalletId, t.createdAt), index("wallet_txs_type_wallets_idx").on( t.type, t.fromWalletId, t.toWalletId, ), ], ); export const walletPaymentIntentsTable = pgTable( "wallet_payment_intents", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), walletId: integer("wallet_id") .notNull() .references(() => walletsTable.id, { onDelete: "cascade" }), provider: paymentProviderEnum("provider").notNull().default("MANUAL"), type: walletPaymentIntentTypeEnum("type").notNull(), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), currency: text("currency").notNull().default("CAD"), status: walletPaymentIntentStatusEnum("status") .notNull() .default("CREATED"), providerReference: text("provider_reference"), metadata: json("metadata").notNull().default({}), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("wallet_payment_intents_provider_ref_uidx").on( t.provider, t.providerReference, ), index("wallet_intents_user_created_idx").on(t.userId, t.createdAt), index("wallet_intents_wallet_status_idx").on( t.walletId, t.status, t.createdAt, ), ], ); export const bankSetupTable = pgTable("bank_setup", { id: serial("id").primaryKey(), eftAccountName: text("eft_account_name").notNull(), eftInstitutionNumber: text("eft_institution_number").notNull(), eftTransitNumber: text("eft_transit_number").notNull(), eftAccountNumber: text("eft_account_number").notNull(), interacRecipientName: text("interac_recipient_name").notNull(), interacEmail: text("interac_email").notNull(), cashoutMinAmount: numeric("cashout_min_amount", { precision: 12, scale: 2, }).notNull(), cashoutFeePct: numeric("cashout_fee_pct", { precision: 6, scale: 3, }).notNull(), cashoutFeeFixed: numeric("cashout_fee_fixed", { precision: 12, scale: 2, }).notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const membershipIdSettingsTable = pgTable("membership_id_settings", { id: integer("id").primaryKey(), prefix: text("prefix").notNull().default("WWN"), digits: integer("digits").notNull().default(7), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const paymentWebhookEventsTable = pgTable( "payment_webhook_events", { id: serial("id").primaryKey(), provider: paymentProviderEnum("provider").notNull(), eventId: text("event_id").notNull(), eventType: text("event_type").notNull(), payload: json("payload").notNull(), status: webhookEventStatusEnum("status").notNull().default("PENDING"), error: text("error"), attempts: integer("attempts").notNull().default(0), receivedAt: timestamp("received_at").notNull().defaultNow(), processedAt: timestamp("processed_at"), }, (t) => [ uniqueIndex("payment_webhook_events_provider_event_uidx").on( t.provider, t.eventId, ), index("webhook_events_status_received_idx").on(t.status, t.receivedAt), ], ); // ═══════════════════════════════════════════════════════════════ // WWN CREDIT SCORE ENGINE // ═══════════════════════════════════════════════════════════════ export const wwnCreditScoresTable = pgTable("wwn_credit_scores", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .unique() .references(() => usersTable.id, { onDelete: "cascade" }), score: integer("score").notNull(), computedAt: timestamp("computed_at").notNull().defaultNow(), }); export const creditScoreFactorsTable = pgTable("credit_score_factors", { id: serial("id").primaryKey(), creditScoreId: integer("credit_score_id") .notNull() .references(() => wwnCreditScoresTable.id, { onDelete: "cascade" }), factorName: text("factor_name").notNull(), score: integer("score").notNull(), weight: text("weight").notNull(), }); // ═══════════════════════════════════════════════════════════════ // HELPING HAND // ═══════════════════════════════════════════════════════════════ export const helpingHandApplicationsTable = pgTable( "helping_hand_applications", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), requestedAmount: numeric("requested_amount", { precision: 10, scale: 2, }).notNull(), purpose: text("purpose").notNull(), term: text("term").notNull(), repaymentSchedule: text("repayment_schedule").notNull(), notes: text("notes"), status: helpingHandStatusEnum("status").notNull().default("DRAFT"), reviewedByAdminId: integer("reviewed_by_admin_id"), reviewedAt: timestamp("reviewed_at"), reviewNote: text("review_note"), qualifyingCircleId: integer("qualifying_circle_id").references( () => savingsCirclesTable.id, ), qualifyingPayoutId: integer("qualifying_payout_id").references( () => circlePayoutsTable.id, ), qualifyingPayoutAmount: numeric("qualifying_payout_amount", { precision: 12, scale: 2, }), qualifyingMultiplier: numeric("qualifying_multiplier", { precision: 6, scale: 2, }), approvedAmount: numeric("approved_amount", { precision: 10, scale: 2 }), disbursedAmount: numeric("disbursed_amount", { precision: 10, scale: 2 }), disbursementTransactionId: integer( "disbursement_transaction_id", ).references(() => walletTransactionsTable.id), disbursedAt: timestamp("disbursed_at"), organizationId: integer("organization_id").references(() => organizationsTable.id), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("hh_apps_user_created_idx").on(t.userId, t.createdAt), index("hh_apps_status_created_idx").on(t.status, t.createdAt), index("hh_apps_org_created_idx").on(t.organizationId, t.createdAt), ], ); export const helpingHandRepaymentsTable = pgTable( "helping_hand_repayments", { id: serial("id").primaryKey(), helpingHandApplicationId: integer("helping_hand_application_id") .notNull() .references(() => helpingHandApplicationsTable.id), dueDate: timestamp("due_date").notNull(), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), paidAmount: numeric("paid_amount", { precision: 10, scale: 2 }), paidAt: timestamp("paid_at"), status: repaymentStatusEnum("status").notNull().default("PENDING"), method: paymentMethodEnum("method"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), organizationId: integer("organization_id").references(() => organizationsTable.id), }, (t) => [ index("hh_repayments_app_due_idx").on( t.helpingHandApplicationId, t.dueDate, ), index("hh_repayments_status_due_idx").on(t.status, t.dueDate), index("hh_repayments_org_due_idx").on(t.organizationId, t.status, t.dueDate), ], ); // ═══════════════════════════════════════════════════════════════ // SUSUNOMICS — COLLECTIVE INVESTMENTS // ═══════════════════════════════════════════════════════════════ export const investmentOpportunitiesTable = pgTable( "investment_opportunities", { id: serial("id").primaryKey(), title: text("title").notNull(), slug: text("slug").notNull().unique(), description: text("description").notNull(), termsHtml: text("terms_html"), category: investmentCategoryEnum("category").notNull(), expectedReturnPct: integer("expected_return_pct").notNull(), minInvestment: numeric("min_investment", { precision: 10, scale: 2, }).notNull(), targetAmount: numeric("target_amount", { precision: 12, scale: 2, }).notNull(), raisedAmount: numeric("raised_amount", { precision: 12, scale: 2 }) .notNull() .default("0"), deadline: timestamp("deadline").notNull(), riskLevel: riskLevelEnum("risk_level").notNull().default("MEDIUM"), status: investmentOpportunityStatusEnum("status") .notNull() .default("DRAFT"), featured: boolean("featured").notNull().default(false), fundingModel: text("funding_model") .$type<"CROWD" | "NON_CROWD">() .notNull() .default("CROWD"), eligibleInvestorTiers: json("eligible_investor_tiers") .$type() .notNull() .default([]), organizationId: integer("organization_id").references( () => organizationsTable.id, ), videoUrl: text("video_url"), academyLink: text("academy_link"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("invest_opps_status_featured_deadline_idx").on( t.status, t.featured, t.deadline, ), ], ); export const investmentInterestsTable = pgTable( "investment_interests", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), opportunityId: integer("opportunity_id") .notNull() .references(() => investmentOpportunitiesTable.id), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), notes: text("notes"), status: investmentInterestStatusEnum("status").notNull().default("PENDING"), confirmedAt: timestamp("confirmed_at"), cancelledAt: timestamp("cancelled_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("investment_interests_user_opp_uidx").on( t.userId, t.opportunityId, ), index("invest_interests_opp_status_idx").on(t.opportunityId, t.status), index("invest_interests_user_created_idx").on(t.userId, t.createdAt), ], ); export const investmentIntentReservationsTable = pgTable( "investment_intent_reservations", { id: serial("id").primaryKey(), interestId: integer("interest_id") .notNull() .references(() => investmentInterestsTable.id, { onDelete: "cascade" }), walletId: integer("wallet_id") .notNull() .references(() => walletsTable.id, { onDelete: "cascade" }), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), status: investmentIntentReservationStatusEnum("status") .notNull() .default("RESERVED"), finalizedTransactionId: integer("finalized_transaction_id").references( () => walletTransactionsTable.id, ), reservedAt: timestamp("reserved_at").notNull().defaultNow(), finalizedAt: timestamp("finalized_at"), releasedAt: timestamp("released_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("investment_intent_reservations_interest_uidx").on( t.interestId, ), index("invest_reservations_wallet_status_idx").on(t.walletId, t.status), index("invest_reservations_status_created_idx").on(t.status, t.createdAt), ], ); export const investmentTransactionsTable = pgTable( "investment_transactions", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), opportunityId: integer("opportunity_id") .notNull() .references(() => investmentOpportunitiesTable.id), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), type: text("type").notNull(), description: text("description"), processedAt: timestamp("processed_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ index("invest_txs_user_processed_idx").on(t.userId, t.processedAt), index("invest_txs_user_opp_type_idx").on(t.userId, t.opportunityId, t.type), ], ); // ═══════════════════════════════════════════════════════════════ // WWN LENDING PLATFORM // ═══════════════════════════════════════════════════════════════ export const lendingTemplatesTable = pgTable( "lending_templates", { id: serial("id").primaryKey(), legacyPoolId: integer("legacy_pool_id").unique(), sourceOpportunityId: integer("source_opportunity_id").unique(), loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"), title: text("title").notNull(), description: text("description").notNull(), termsHtml: text("terms_html"), status: lendingPoolStatusEnum("status").notNull().default("OPEN"), isSnapshot: boolean("is_snapshot").notNull().default(false), minCommitment: numeric("min_commitment", { precision: 10, scale: 2, }).notNull(), minRequestAmount: numeric("min_request_amount", { precision: 10, scale: 2, }) .notNull() .default("500.00"), maxRequestAmount: numeric("max_request_amount", { precision: 12, scale: 2, }) .notNull() .default("25000.00"), borrowerAppreciationAmount: numeric("borrower_appreciation_amount", { precision: 10, scale: 2, }) .notNull() .default("0.00"), borrowerTermMonths: integer("borrower_term_months").notNull().default(12), repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency") .notNull() .default("MONTHLY"), processingFeePct: numeric("processing_fee_pct", { precision: 5, scale: 2, }), wwnMarginPct: numeric("wwn_margin_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), reserveContributionPct: numeric("reserve_contribution_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), platformAppreciationSharePct: numeric("platform_appreciation_share_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), minAppreciationType: lendingMinAppreciationTypeEnum("min_appreciation_type") .notNull() .default("NONE"), minAppreciationValue: numeric("min_appreciation_value", { precision: 10, scale: 2, }) .notNull() .default("0.00"), minAppreciationTiers: json("min_appreciation_tiers"), economicsUpdatedAt: timestamp("economics_updated_at"), economicsUpdatedByAdminId: integer( "economics_updated_by_admin_id", ).references(() => usersTable.id), economicsUpdateReason: text("economics_update_reason"), expectedReturnPct: numeric("expected_return_pct", { precision: 5, scale: 2, }) .notNull() .default("8.00"), termMonths: integer("term_months").notNull().default(12), returnModel: lendingPoolReturnModelEnum("return_model") .notNull() .default("FIXED_SIMPLE"), expectedReturn: text("expected_return").notNull(), term: text("term").notNull(), minBorrowerPlanTier: membershipPlanTierEnum("min_borrower_plan_tier") .notNull() .default("STARTER"), borrowerType: text("borrower_type"), organizationId: integer("organization_id").references( () => organizationsTable.id, ), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("lending_templates_status_created_idx").on(t.status, t.createdAt), index("idx_lending_templates_org_status_created").on( t.organizationId, t.status, t.createdAt, ), ], ); export const lendingOpportunitiesTable = pgTable( "lending_opportunities", { id: serial("id").primaryKey(), legacyPoolId: integer("legacy_pool_id").unique(), templateId: integer("template_id").references( () => lendingTemplatesTable.id, ), applicationId: integer("application_id").unique(), loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"), title: text("title").notNull(), description: text("description").notNull(), status: lendingPoolStatusEnum("status").notNull().default("DRAFT"), targetAmount: numeric("target_amount", { precision: 12, scale: 2, }).notNull(), committedAmount: numeric("committed_amount", { precision: 12, scale: 2 }) .notNull() .default("0"), minCommitment: numeric("min_commitment", { precision: 10, scale: 2, }).notNull(), borrowerAppreciationAmount: numeric("borrower_appreciation_amount", { precision: 10, scale: 2, }) .notNull() .default("0.00"), borrowerTermMonths: integer("borrower_term_months").notNull().default(12), repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency") .notNull() .default("MONTHLY"), processingFeePct: numeric("processing_fee_pct", { precision: 5, scale: 2, }), wwnMarginPct: numeric("wwn_margin_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), reserveContributionPct: numeric("reserve_contribution_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), platformAppreciationSharePct: numeric("platform_appreciation_share_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), minAppreciationType: lendingMinAppreciationTypeEnum("min_appreciation_type") .notNull() .default("NONE"), minAppreciationValue: numeric("min_appreciation_value", { precision: 10, scale: 2, }) .notNull() .default("0.00"), minAppreciationTiers: json("min_appreciation_tiers"), expectedReturnPct: numeric("expected_return_pct", { precision: 5, scale: 2, }) .notNull() .default("8.00"), termMonths: integer("term_months").notNull().default(12), returnModel: lendingPoolReturnModelEnum("return_model") .notNull() .default("FIXED_SIMPLE"), expectedReturn: text("expected_return").notNull(), term: text("term").notNull(), minBorrowerPlanTier: membershipPlanTierEnum("min_borrower_plan_tier") .notNull() .default("STARTER"), borrowerType: text("borrower_type"), organizationId: integer("organization_id").references( () => organizationsTable.id, ), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("lending_opps_status_created_idx").on(t.status, t.createdAt), index("lending_opps_template_status_created_idx").on( t.templateId, t.status, t.createdAt, ), index("idx_lending_opps_org_status_created").on( t.organizationId, t.status, t.createdAt, ), ], ); export const lendingCommitmentsTable = pgTable( "lending_commitments", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id), opportunityId: integer("opportunity_id") .notNull() .references(() => lendingOpportunitiesTable.id), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), status: commitmentStatusEnum("status").notNull().default("PENDING"), confirmedAt: timestamp("confirmed_at"), statusChangedAt: timestamp("status_changed_at"), statusChangedByAdminId: integer("status_changed_by_admin_id").references( () => usersTable.id, ), reviewNote: text("review_note"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("lend_commit_opp_status_created_idx").on( t.opportunityId, t.status, t.createdAt, ), index("lend_commit_user_status_created_idx").on( t.userId, t.status, t.createdAt, ), uniqueIndex("lend_commit_user_opp_active_uidx") .on(t.userId, t.opportunityId) .where(sql`${t.status} in ('PENDING', 'CONFIRMED')`), ], ); export const loanApplicationsTable = pgTable( "loan_applications", { id: serial("id").primaryKey(), ascaId: integer("asca_id").references(() => ascasTable.id), borrowerId: integer("borrower_id") .notNull() .references(() => usersTable.id), templateId: integer("template_id").references( () => lendingTemplatesTable.id, ), opportunityId: integer("opportunity_id"), loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"), requestedAmount: numeric("requested_amount", { precision: 10, scale: 2, }).notNull(), purpose: text("purpose").notNull(), term: text("term").notNull(), repaymentSchedule: text("repayment_schedule").notNull(), termMonths: integer("term_months").notNull().default(12), borrowerAppreciationAmount: numeric("borrower_appreciation_amount", { precision: 10, scale: 2, }) .notNull() .default("0.00"), repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency") .notNull() .default("MONTHLY"), processingFeePct: numeric("processing_fee_pct", { precision: 5, scale: 2, }), wwnMarginPct: numeric("wwn_margin_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), reserveContributionPct: numeric("reserve_contribution_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), platformAppreciationSharePct: numeric("platform_appreciation_share_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), pricingLockedAt: timestamp("pricing_locked_at"), totalRepayableAmount: numeric("total_repayable_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), estimatedInstallmentAmount: numeric("estimated_installment_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), funderOpportunityTitle: text("funder_opportunity_title"), notes: text("notes"), status: loanApplicationStatusEnum("status").notNull().default("DRAFT"), poolAssignedAt: timestamp("pool_assigned_at"), poolAssignedByAdminId: integer("pool_assigned_by_admin_id").references( () => usersTable.id, ), poolReassignedAt: timestamp("pool_reassigned_at"), poolReassignedByAdminId: integer("pool_reassigned_by_admin_id").references( () => usersTable.id, ), poolReassignmentNote: text("pool_reassignment_note"), reviewedByAdminId: integer("reviewed_by_admin_id"), reviewedAt: timestamp("reviewed_at"), reviewNote: text("review_note"), approvedAmount: numeric("approved_amount", { precision: 10, scale: 2 }), reservedAt: timestamp("reserved_at"), reservedByAdminId: integer("reserved_by_admin_id").references( () => usersTable.id, ), disbursedAt: timestamp("disbursed_at"), disbursedAmount: numeric("disbursed_amount", { precision: 10, scale: 2 }), disbursementTransactionId: integer( "disbursement_transaction_id", ).references(() => walletTransactionsTable.id), organizationId: integer("organization_id").references( () => organizationsTable.id, ), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("loan_apps_borrower_created_idx").on(t.borrowerId, t.createdAt), index("loan_apps_asca_status_created_idx").on(t.ascaId, t.status, t.createdAt), index("loan_apps_opp_status_created_idx").on( t.opportunityId, t.status, t.createdAt, ), index("idx_loan_apps_org_status_created").on( t.organizationId, t.status, t.createdAt, ), uniqueIndex("loan_apps_borrower_active_uidx") .on(t.borrowerId) .where( sql`${t.status} in ('DRAFT', 'SUBMITTED', 'WAITLISTED', 'UNDER_REVIEW', 'APPROVED', 'APPROVED_RESERVED', 'FUNDED', 'REPAYING')`, ), ], ); export const loanRepaymentsTable = pgTable( "loan_repayments", { id: serial("id").primaryKey(), loanApplicationId: integer("loan_application_id") .notNull() .references(() => loanApplicationsTable.id), dueDate: timestamp("due_date").notNull(), amount: numeric("amount", { precision: 10, scale: 2 }).notNull(), paidAmount: numeric("paid_amount", { precision: 10, scale: 2 }), principalPaidAmount: numeric("principal_paid_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), returnPaidAmount: numeric("return_paid_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), feePaidAmount: numeric("fee_paid_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), creditedToPoolAmount: numeric("credited_to_pool_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), paidAt: timestamp("paid_at"), status: repaymentStatusEnum("status").notNull().default("PENDING"), method: paymentMethodEnum("method"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("loan_repayments_app_due_idx").on(t.loanApplicationId, t.dueDate), index("loan_repayments_status_due_idx").on(t.status, t.dueDate), ], ); export const orgLendingPoolsTable = pgTable( "org_lending_pools", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), templateId: integer("template_id").references(() => lendingTemplatesTable.id), name: text("name").notNull(), description: text("description"), eligibilityMode: text("eligibility_mode") .$type<"CONTRIBUTORS_ONLY" | "ALL_ORG_MEMBERS">() .notNull() .default("CONTRIBUTORS_ONLY"), status: text("status") .$type<"DRAFT" | "OPEN" | "CLOSED">() .notNull() .default("DRAFT"), minRequestAmount: numeric("min_request_amount", { precision: 10, scale: 2, }) .notNull() .default("500.00"), maxRequestAmount: numeric("max_request_amount", { precision: 12, scale: 2, }) .notNull() .default("25000.00"), defaultTermMonths: integer("default_term_months").notNull().default(12), defaultRepaymentFrequency: lendingRepaymentFrequencyEnum( "default_repayment_frequency", ) .notNull() .default("MONTHLY"), defaultBorrowerAppreciationPct: numeric( "default_borrower_appreciation_pct", { precision: 5, scale: 2 }, ) .notNull() .default("0.00"), defaultProcessingFeePct: numeric("default_processing_fee_pct", { precision: 5, scale: 2, }) .notNull() .default("0.00"), notes: text("notes"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("org_lending_pools_org_status_created_idx").on( t.organizationId, t.status, t.createdAt, ), uniqueIndex("org_lending_pools_org_template_uniq_idx") .on(t.organizationId, t.templateId) .where(sql`${t.templateId} IS NOT NULL`), ], ); export const orgLendingPoolFunderUsesTable = pgTable( "org_lending_pool_funder_uses", { id: serial("id").primaryKey(), organizationId: integer("organization_id") .notNull() .references(() => organizationsTable.id, { onDelete: "cascade" }), poolId: integer("pool_id") .notNull() .references(() => orgLendingPoolsTable.id, { onDelete: "cascade" }), loanApplicationId: integer("loan_application_id") .notNull() .references(() => loanApplicationsTable.id, { onDelete: "cascade" }), ascaId: integer("asca_id") .notNull() .references(() => ascasTable.id, { onDelete: "cascade" }), fundedAmount: numeric("funded_amount", { precision: 12, scale: 2 }) .notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("org_lending_pool_funder_uses_org_pool_app_asca_uq").on( t.organizationId, t.poolId, t.loanApplicationId, t.ascaId, ), index("org_lending_pool_funder_uses_org_created_idx").on( t.organizationId, t.createdAt, ), index("org_lending_pool_funder_uses_pool_created_idx").on( t.poolId, t.createdAt, ), index("org_lending_pool_funder_uses_asca_idx").on(t.ascaId), index("org_lending_pool_funder_uses_loan_app_idx").on(t.loanApplicationId), ], ); export const lendingOpportunityReturnEventsTable = pgTable( "lending_opportunity_return_events", { id: serial("id").primaryKey(), legacyReturnEventId: integer("legacy_return_event_id").unique(), opportunityId: integer("opportunity_id") .notNull() .references(() => lendingOpportunitiesTable.id), repaymentId: integer("repayment_id") .notNull() .references(() => loanRepaymentsTable.id), loanApplicationId: integer("loan_application_id") .notNull() .references(() => loanApplicationsTable.id), walletTransactionId: integer("wallet_transaction_id").references( () => walletTransactionsTable.id, ), grossAmount: numeric("gross_amount", { precision: 10, scale: 2 }) .notNull() .default("0"), principalAmount: numeric("principal_amount", { precision: 10, scale: 2 }) .notNull() .default("0"), returnAmount: numeric("return_amount", { precision: 10, scale: 2 }) .notNull() .default("0"), allocatedReturnAmount: numeric("allocated_return_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), occurredAt: timestamp("occurred_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("lending_opportunity_return_events_repayment_wallet_uidx").on( t.repaymentId, t.walletTransactionId, ), ], ); export const lendingOpportunityReturnAllocationsTable = pgTable( "lending_opportunity_return_allocations", { id: serial("id").primaryKey(), returnEventId: integer("return_event_id") .notNull() .references(() => lendingOpportunityReturnEventsTable.id), commitmentId: integer("commitment_id") .notNull() .references(() => lendingCommitmentsTable.id), opportunityId: integer("opportunity_id") .notNull() .references(() => lendingOpportunitiesTable.id), userId: integer("user_id") .notNull() .references(() => usersTable.id), commitmentAmountSnapshot: numeric("commitment_amount_snapshot", { precision: 10, scale: 2, }) .notNull() .default("0"), ownershipPctSnapshot: numeric("ownership_pct_snapshot", { precision: 8, scale: 6, }) .notNull() .default("0"), allocatedReturnAmount: numeric("allocated_return_amount", { precision: 10, scale: 2, }) .notNull() .default("0"), occurredAt: timestamp("occurred_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex( "lending_opportunity_return_allocations_event_commitment_uidx", ).on(t.returnEventId, t.commitmentId), ], ); // ═══════════════════════════════════════════════════════════════ // NOTIFICATIONS // ═══════════════════════════════════════════════════════════════ export const notificationsTable = pgTable( "notifications", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), category: notificationCategoryEnum("category").notNull().default("SYSTEM"), severity: notificationSeverityEnum("severity").notNull().default("INFO"), title: text("title").notNull(), body: text("body").notNull(), read: boolean("read").notNull().default(false), readAt: timestamp("read_at"), actionLabel: text("action_label"), actionHref: text("action_href"), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ index("notifications_user_read_created_idx").on( t.userId, t.read, t.createdAt, t.id, ), index("notifications_user_created_idx").on(t.userId, t.createdAt, t.id), index("notifications_created_idx").on(t.createdAt), index("notifications_read_read_at_idx").on(t.read, t.readAt), ], ); export const userPushTokensTable = pgTable( "user_push_tokens", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), platform: pushPlatformEnum("platform").notNull().default("EXPO"), token: text("token").notNull().unique(), deviceName: text("device_name"), deviceId: text("device_id"), isEnabled: boolean("is_enabled").notNull().default(true), lastSeenAt: timestamp("last_seen_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [index("push_tokens_user_enabled_idx").on(t.userId, t.isEnabled)], ); export const userNotificationPreferencesTable = pgTable( "user_notification_preferences", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), channel: notificationChannelEnum("channel").notNull(), category: notificationCategoryEnum("category").notNull(), enabled: boolean("enabled").notNull(), updatedAt: timestamp("updated_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("user_notification_prefs_user_channel_category_uidx").on( t.userId, t.channel, t.category, ), ], ); export const notificationDeliveriesTable = pgTable( "notification_deliveries", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), channel: notificationChannelEnum("channel").notNull(), category: notificationCategoryEnum("category").notNull(), status: notificationDeliveryStatusEnum("status").notNull(), idempotencyKey: text("idempotency_key").notNull().unique(), notificationId: integer("notification_id").references( () => notificationsTable.id, { onDelete: "set null" }, ), provider: text("provider"), errorMessage: text("error_message"), metadata: json("metadata").notNull().default({}), occurredAt: timestamp("occurred_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ index("notification_deliveries_user_occurred_idx").on( t.userId, t.occurredAt, ), index("notification_deliveries_occurred_idx").on(t.occurredAt), ], ); // ═══════════════════════════════════════════════════════════════ // COMMUNITY // ═══════════════════════════════════════════════════════════════ export const announcementsTable = pgTable("announcements", { id: serial("id").primaryKey(), title: text("title").notNull(), body: text("body").notNull(), category: text("category").notNull(), pinned: boolean("pinned").notNull().default(false), publishedBy: integer("published_by"), publishedAt: timestamp("published_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const communityEventsTable = pgTable("community_events", { id: serial("id").primaryKey(), title: text("title").notNull(), description: text("description").notNull(), date: timestamp("date").notNull(), startTime: text("start_time").notNull(), endTime: text("end_time"), location: text("location").notNull(), type: eventTypeEnum("type").notNull().default("VIRTUAL"), maxAttendees: integer("max_attendees"), organizerId: integer("organizer_id"), coverImageUrl: text("cover_image_url"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const eventRsvpsTable = pgTable( "event_rsvps", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), eventId: integer("event_id") .notNull() .references(() => communityEventsTable.id, { onDelete: "cascade" }), status: rsvpStatusEnum("status").notNull().default("GOING"), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [uniqueIndex("event_rsvps_user_event_uidx").on(t.userId, t.eventId)], ); export const discussionsTable = pgTable("discussions", { id: serial("id").primaryKey(), authorId: integer("author_id") .notNull() .references(() => usersTable.id), title: text("title").notNull(), body: text("body").notNull(), category: text("category").notNull(), pinned: boolean("pinned").notNull().default(false), viewCount: integer("view_count").notNull().default(0), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const discussionRepliesTable = pgTable("discussion_replies", { id: serial("id").primaryKey(), discussionId: integer("discussion_id") .notNull() .references(() => discussionsTable.id, { onDelete: "cascade" }), authorId: integer("author_id") .notNull() .references(() => usersTable.id), body: text("body").notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const memberSpotlightsTable = pgTable("member_spotlights", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .unique() .references(() => usersTable.id, { onDelete: "cascade" }), achievement: text("achievement").notNull(), featured: boolean("featured").notNull().default(false), approvedAt: timestamp("approved_at"), createdAt: timestamp("created_at").notNull().defaultNow(), }); // ═══════════════════════════════════════════════════════════════ export const communityPostTypeEnum = pgEnum("community_post_type", [ "USER_UPDATE", "MILESTONE", "ADMIN_STORY", "RESOURCE_SHARE", ]); export const communityProfilesTable = pgTable("community_profiles", { id: serial("id").primaryKey(), userId: integer("user_id") .unique() .references(() => usersTable.id, { onDelete: "cascade" }), organizationId: integer("organization_id") .unique() .references(() => organizationsTable.id, { onDelete: "cascade" }), bio: text("bio"), location: text("location"), interests: text("interests").array(), badges: text("badges").array(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("community_profiles_user_idx").on(t.userId), index("community_profiles_org_idx").on(t.organizationId), ]); export const communityPostsTable = pgTable("community_posts", { id: serial("id").primaryKey(), postType: communityPostTypeEnum("post_type").notNull().default("USER_UPDATE"), authorId: integer("author_id").references(() => usersTable.id), // For USER_UPDATE / ADMIN_STORY organizationId: integer("organization_id").references(() => organizationsTable.id), // If an Org posts title: text("title"), // Required for ADMIN_STORY slug: text("slug").unique(), // For ADMIN_STORY permalinks excerpt: text("excerpt"), // For ADMIN_STORY contentHtml: text("content_html").notNull(), coverImageUrl: text("cover_image_url"), // Visibility Flags (Primarily used for ADMIN_STORY) showToIndividuals: boolean("show_to_individuals").notNull().default(true), showToInvestors: boolean("show_to_investors").notNull().default(true), showToOrganizations: boolean("show_to_organizations").notNull().default(true), isPublished: boolean("is_published").notNull().default(true), publishedAt: timestamp("published_at").defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("community_posts_type_published_idx").on(t.postType, t.isPublished, t.publishedAt), ]); export const communityCommentsTable = pgTable("community_comments", { id: serial("id").primaryKey(), postId: integer("post_id").notNull().references(() => communityPostsTable.id, { onDelete: "cascade" }), authorId: integer("author_id").notNull().references(() => usersTable.id), parentId: integer("parent_id"), // Self-referencing for threaded replies. Can't use references() inline due to Drizzle circular dep limits without explicit relations contentHtml: text("content_html").notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("community_comments_post_idx").on(t.postId, t.createdAt), ]); export const communityReactionsTable = pgTable("community_reactions", { id: serial("id").primaryKey(), postId: integer("post_id").references(() => communityPostsTable.id, { onDelete: "cascade" }), commentId: integer("comment_id").references(() => communityCommentsTable.id, { onDelete: "cascade" }), userId: integer("user_id").notNull().references(() => usersTable.id), reactionType: text("reaction_type").notNull().default("LIKE"), createdAt: timestamp("created_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("community_reactions_post_user_uidx").on(t.postId, t.userId), uniqueIndex("community_reactions_comment_user_uidx").on(t.commentId, t.userId), ]); export const communityForumsTable = pgTable("community_forums", { id: serial("id").primaryKey(), name: text("name").notNull().unique(), slug: text("slug").notNull().unique(), description: text("description"), sortOrder: integer("sort_order").notNull().default(0), isRestricted: boolean("is_restricted").notNull().default(false), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const forumThreadsTable = pgTable("forum_threads", { id: serial("id").primaryKey(), forumId: integer("forum_id").notNull().references(() => communityForumsTable.id, { onDelete: "cascade" }), authorId: integer("author_id").notNull().references(() => usersTable.id), title: text("title").notNull(), slug: text("slug").notNull().unique(), contentHtml: text("content_html").notNull(), isPinned: boolean("is_pinned").notNull().default(false), isLocked: boolean("is_locked").notNull().default(false), viewCount: integer("view_count").notNull().default(0), lastActivityAt: timestamp("last_activity_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("forum_threads_forum_idx").on(t.forumId, t.lastActivityAt), ]); export const messageThreadsTable = pgTable("message_threads", { id: serial("id").primaryKey(), isGroup: boolean("is_group").notNull().default(false), name: text("name"), // For group chats ascaId: integer("asca_id").references(() => ascasTable.id), // Link to a Savings Circle createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); export const messageThreadParticipantsTable = pgTable("message_thread_participants", { id: serial("id").primaryKey(), threadId: integer("thread_id").notNull().references(() => messageThreadsTable.id, { onDelete: "cascade" }), userId: integer("user_id").notNull().references(() => usersTable.id, { onDelete: "cascade" }), lastReadAt: timestamp("last_read_at"), joinedAt: timestamp("joined_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("message_thread_participants_uidx").on(t.threadId, t.userId), ]); export const directMessagesTable = pgTable("direct_messages", { id: serial("id").primaryKey(), threadId: integer("thread_id").notNull().references(() => messageThreadsTable.id, { onDelete: "cascade" }), senderId: integer("sender_id").notNull().references(() => usersTable.id), content: text("content").notNull(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("direct_messages_thread_idx").on(t.threadId, t.createdAt), ]); // RESOURCE CENTRE // export const resourceCategoriesTable = pgTable("resource_categories", { id: serial("id").primaryKey(), name: text("name").notNull().unique(), slug: text("slug").notNull().unique(), sortOrder: integer("sort_order").notNull().default(0), }); export const resourcesTable = pgTable("resources", { id: serial("id").primaryKey(), title: text("title").notNull(), slug: text("slug").notNull().unique(), description: text("description"), resourceKind: resourceKindEnum("resource_kind").notNull().default("FILE"), categoryId: integer("category_id") .notNull() .references(() => resourceCategoriesTable.id), fileUrl: text("file_url"), fileType: resourceFileTypeEnum("file_type").notNull().default("PDF"), fileSize: integer("file_size"), coverImageUrl: text("cover_image_url"), authorName: text("author_name"), contentHtml: text("content_html"), showToIndividuals: boolean("show_to_individuals").notNull().default(false), showToInvestors: boolean("show_to_investors").notNull().default(true), showToOrganizations: boolean("show_to_organizations").notNull().default(true), uploadedById: integer("uploaded_by_id"), isPublished: boolean("is_published").notNull().default(false), publishedAt: timestamp("published_at"), downloads: integer("downloads").notNull().default(0), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); // ═══════════════════════════════════════════════════════════════ // ZOD SCHEMAS & TYPES // ═══════════════════════════════════════════════════════════════ // ═══════════════════════════════════════════════════════════════ // ONBOARDING SLIDES // ═══════════════════════════════════════════════════════════════ export const onboardingSlidesTable = pgTable("onboarding_slides", { id: serial("id").primaryKey(), title: text("title").notNull(), subtitle: text("subtitle"), body: text("body"), illustrationIcon: text("illustration_icon") .notNull() .default("diamond-stone"), illustrationUrl: text("illustration_url"), accentColor: text("accent_color").notNull().default("#f27a4a"), sortOrder: integer("sort_order").notNull().default(0), isActive: boolean("is_active").notNull().default(true), createdAt: timestamp("created_at").defaultNow(), updatedAt: timestamp("updated_at").defaultNow(), }); export type OnboardingSlide = typeof onboardingSlidesTable.$inferSelect; export type InsertOnboardingSlide = typeof onboardingSlidesTable.$inferInsert; export const onboardingProgramsTable = pgTable( "onboarding_programs", { id: serial("id").primaryKey(), title: text("title").notNull(), description: text("description"), targetMembershipType: membershipTypeEnum("target_membership_type") .notNull() .default("INDIVIDUAL"), numberOfWeeks: integer("number_of_weeks").notNull(), durationUnit: onboardingProgramDurationUnitEnum("duration_unit") .notNull() .default("WEEK"), startDate: timestamp("start_date"), endDate: timestamp("end_date"), status: onboardingProgramStatusEnum("status").notNull().default("DRAFT"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("onboarding_programs_status_idx").on(t.status), index("onboarding_programs_target_membership_idx").on( t.targetMembershipType, ), index("onboarding_programs_created_idx").on(t.createdAt), ], ); export const onboardingProgramWeeksTable = pgTable( "onboarding_program_weeks", { id: serial("id").primaryKey(), programId: integer("program_id") .notNull() .references(() => onboardingProgramsTable.id, { onDelete: "cascade" }), weekNumber: integer("week_number").notNull(), title: text("title").notNull(), description: text("description"), sortOrder: integer("sort_order").notNull().default(0), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("onboarding_program_weeks_unique_idx").on( t.programId, t.weekNumber, ), index("onboarding_program_weeks_order_idx").on(t.programId, t.sortOrder), ], ); export const onboardingWeekActivitiesTable = pgTable( "onboarding_week_activities", { id: serial("id").primaryKey(), weekId: integer("week_id") .notNull() .references(() => onboardingProgramWeeksTable.id, { onDelete: "cascade", }), title: text("title").notNull(), description: text("description"), durationMinutes: integer("duration_minutes"), type: onboardingActivityTypeEnum("type").notNull(), content: json("content").$type | null>(), sortOrder: integer("sort_order").notNull().default(0), isRequired: boolean("is_required").notNull().default(true), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("onboarding_week_activities_order_idx").on(t.weekId, t.sortOrder), uniqueIndex("onboarding_week_activities_unique_title_idx").on( t.weekId, t.title, ), ], ); export const onboardingEnrollmentsTable = pgTable( "onboarding_enrollments", { id: serial("id").primaryKey(), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), programId: integer("program_id") .notNull() .references(() => onboardingProgramsTable.id, { onDelete: "restrict" }), enrolledAt: timestamp("enrolled_at").notNull().defaultNow(), completedAt: timestamp("completed_at"), lastActivityAt: timestamp("last_activity_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("onboarding_enrollments_user_program_unique_idx").on( t.userId, t.programId, ), index("onboarding_enrollments_user_idx").on(t.userId), index("onboarding_enrollments_program_idx").on(t.programId), ], ); export const onboardingActivityProgressTable = pgTable( "onboarding_activity_progress", { id: serial("id").primaryKey(), enrollmentId: integer("enrollment_id") .notNull() .references(() => onboardingEnrollmentsTable.id, { onDelete: "cascade" }), activityId: integer("activity_id") .notNull() .references(() => onboardingWeekActivitiesTable.id, { onDelete: "cascade", }), status: onboardingActivityStatusEnum("status") .notNull() .default("NOT_STARTED"), startedAt: timestamp("started_at"), completedAt: timestamp("completed_at"), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("onboarding_activity_progress_unique_idx").on( t.enrollmentId, t.activityId, ), index("onboarding_activity_progress_enrollment_idx").on(t.enrollmentId), ], ); export type OnboardingProgram = typeof onboardingProgramsTable.$inferSelect; export type OnboardingProgramWeek = typeof onboardingProgramWeeksTable.$inferSelect; export type OnboardingWeekActivity = typeof onboardingWeekActivitiesTable.$inferSelect; export type OnboardingEnrollment = typeof onboardingEnrollmentsTable.$inferSelect; export type OnboardingActivityProgress = typeof onboardingActivityProgressTable.$inferSelect; // ═══════════════════════════════════════════════════════════════ // ASCA (Accumulating Savings and Credit Association) // ═══════════════════════════════════════════════════════════════ export const ascasTable = pgTable( "ascas", { id: serial("id").primaryKey(), name: text("name").notNull(), description: text("description"), termsHtml: text("terms_html"), organizationId: integer("organization_id").references( () => organizationsTable.id, ), type: ascaTypeEnum("type").notNull().default("GENERAL"), status: ascaStatusEnum("status").notNull().default("DRAFT"), investmentId: integer("investment_id").references( () => investmentOpportunitiesTable.id, ), escrowWalletId: integer("escrow_wallet_id").references( () => walletsTable.id, ), targetAmount: numeric("target_amount", { precision: 12, scale: 2 }), currentAmount: numeric("current_amount", { precision: 12, scale: 2 }) .notNull() .default("0"), fundingStartAt: timestamp("funding_start_at"), fundingEndAt: timestamp("funding_end_at"), constitution: json("constitution") .$type>() .notNull() .default({}), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("ascas_status_idx").on(t.status), index("ascas_type_idx").on(t.type), index("ascas_investment_idx").on(t.investmentId), index("ascas_org_status_idx").on(t.organizationId, t.status), ], ); export const ascaMembersTable = pgTable( "asca_members", { id: serial("id").primaryKey(), ascaId: integer("asca_id") .notNull() .references(() => ascasTable.id, { onDelete: "cascade" }), userId: integer("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), targetContribution: numeric("target_contribution", { precision: 12, scale: 2, }).notNull(), contributedAmount: numeric("contributed_amount", { precision: 12, scale: 2, }) .notNull() .default("0"), status: ascaMemberStatusEnum("status").notNull().default("ACTIVE"), joinedAt: timestamp("joined_at").notNull().defaultNow(), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ uniqueIndex("asca_members_asca_user_idx").on(t.ascaId, t.userId), index("asca_members_user_idx").on(t.userId), ], ); export const ascaContributionsTable = pgTable( "asca_contributions", { id: serial("id").primaryKey(), ascaId: integer("asca_id") .notNull() .references(() => ascasTable.id, { onDelete: "cascade" }), memberId: integer("member_id") .notNull() .references(() => ascaMembersTable.id, { onDelete: "cascade" }), amount: numeric("amount", { precision: 12, scale: 2 }).notNull(), status: ascaContributionStatusEnum("status").notNull().default("PENDING"), dueDate: timestamp("due_date"), paidAt: timestamp("paid_at"), transactionId: integer("transaction_id").references( () => walletTransactionsTable.id, ), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }, (t) => [ index("asca_contributions_member_idx").on(t.memberId), index("asca_contributions_asca_idx").on(t.ascaId), index("asca_contributions_status_idx").on(t.status), ], ); export type Asca = typeof ascasTable.$inferSelect; export type AscaMember = typeof ascaMembersTable.$inferSelect; export type AscaContribution = typeof ascaContributionsTable.$inferSelect; // ═══════════════════════════════════════════════════════════════ // DERIVED TYPES // ═══════════════════════════════════════════════════════════════ export const insertUserSchema = createInsertSchema(usersTable).omit({ id: true, createdAt: true, updatedAt: true, }); export type InsertUser = z.infer; export type User = typeof usersTable.$inferSelect; export type VerificationToken = typeof verificationTokensTable.$inferSelect; export type Organization = typeof organizationsTable.$inferSelect; export type MembershipPlan = typeof membershipPlansTable.$inferSelect; export type SavingsCircle = typeof savingsCirclesTable.$inferSelect; export type Wallet = typeof walletsTable.$inferSelect; export type WalletTransaction = typeof walletTransactionsTable.$inferSelect; export type InvestmentOpportunity = typeof investmentOpportunitiesTable.$inferSelect; export type Notification = typeof notificationsTable.$inferSelect;