import { defineSchema, defineTable } from "convex/server"; import { v } from "convex/values"; import { authTables } from "@convex-dev/auth/server"; /** * LocaJour data model on Convex — one synchronized source of truth. * * Conventions * - Calendar dates (check-in, check-out, block ranges, expense dates) are ISO * day strings "YYYY-MM-DD" so they compare lexicographically and index well. * - Instants (createdAt, paidAt, lastSeenAt…) are epoch milliseconds. * - Every important record carries a human reference (`code`). * - Nullable columns from the previous relational model are optional here. */ const day = v.string(); // "YYYY-MM-DD" const ms = v.number(); const opt = | ReturnType | ReturnType>(t: T) => v.optional(v.union(t, v.null())); export default defineSchema({ ...authTables, // ── People & access ─────────────────────────────────────── users: defineTable({ // Convex Auth fields name: v.optional(v.string()), email: v.optional(v.string()), image: v.optional(v.string()), emailVerificationTime: v.optional(v.number()), phone: v.optional(v.string()), phoneVerificationTime: v.optional(v.number()), isAnonymous: v.optional(v.boolean()), // LocaJour worker profile code: v.optional(v.string()), // WRK-0001 fullName: v.optional(v.string()), username: v.optional(v.string()), roleId: v.optional(v.id("roles")), status: v.optional(v.string()), // ACTIVE | INACTIVE | SUSPENDED hireDate: opt(day), emergencyContact: opt(v.string()), avatarStorageId: v.optional(v.id("_storage")), locale: v.optional(v.string()), timezone: v.optional(v.string()), twoFactorEnabled: v.optional(v.boolean()), permissionOverrides: v.optional(v.array(v.object({ key: v.string(), granted: v.boolean() }))), lastLoginAt: opt(ms), lastSeenAt: opt(ms), passwordChangedAt: opt(ms), deletedAt: opt(ms), }) .index("email", ["email"]) .index("phone", ["phone"]) .index("by_code", ["code"]) .index("by_username", ["username"]) .index("by_role", ["roleId"]) .index("by_status", ["status"]), roles: defineTable({ key: v.string(), // ADMIN | MANAGER | RECEPTION | CLEANER | custom name: v.string(), description: opt(v.string()), isSystem: v.boolean(), permissions: v.array(v.string()), }).index("by_key", ["key"]), /** Device / IP metadata for Convex Auth sessions (the session itself lives in authSessions). */ sessionMeta: defineTable({ sessionId: v.id("authSessions"), userId: v.id("users"), device: opt(v.string()), browser: opt(v.string()), os: opt(v.string()), ipAddress: opt(v.string()), userAgent: opt(v.string()), lastSeenAt: ms, revokedAt: opt(ms), }) .index("by_session", ["sessionId"]) .index("by_user", ["userId"]), loginHistory: defineTable({ userId: v.optional(v.id("users")), email: v.string(), success: v.boolean(), reason: opt(v.string()), ipAddress: opt(v.string()), userAgent: opt(v.string()), device: opt(v.string()), browser: opt(v.string()), at: ms, }) .index("by_user_at", ["userId", "at"]) .index("by_at", ["at"]) .index("by_email_at", ["email", "at"]), // ── Apartments ──────────────────────────────────────────── apartments: defineTable({ code: v.string(), // A03 name: v.string(), building: opt(v.string()), floor: opt(v.string()), address: v.string(), city: v.string(), bedrooms: v.number(), beds: v.number(), bathrooms: v.number(), maxGuests: v.number(), basePrice: v.number(), weekendPrice: opt(v.number()), status: v.string(), // AVAILABLE | OCCUPIED | RESERVED | CLEANING | MAINTENANCE | BLOCKED cleaningStatus: v.string(), // CLEAN | NEEDS_CLEANING | IN_PROGRESS | READY maintenanceStatus: v.string(), // OK | ISSUE | BLOCKED amenities: v.array(v.string()), notes: opt(v.string()), coverImageId: v.optional(v.id("apartmentImages")), isActive: v.boolean(), updatedAt: ms, deletedAt: opt(ms), }) .index("by_code", ["code"]) .index("by_status", ["status"]) .index("by_active", ["isActive"]), apartmentImages: defineTable({ apartmentId: v.id("apartments"), storageId: v.id("_storage"), mimeType: v.string(), width: opt(v.number()), height: opt(v.number()), size: opt(v.number()), caption: opt(v.string()), category: v.string(), isCover: v.boolean(), sortOrder: v.number(), uploadedById: v.optional(v.id("users")), archivedAt: opt(ms), }).index("by_apartment", ["apartmentId", "sortOrder"]), /** Manual, maintenance, external-channel blocks and inventory holds. */ apartmentBlocks: defineTable({ apartmentId: v.id("apartments"), startDate: day, endDate: day, // exclusive type: v.string(), // MANUAL | MAINTENANCE | EXTERNAL | HOLD source: v.string(), // AIRBNB | BOOKING | EXPEDIA | EXTERNAL | OWNER | MAINTENANCE | CLEANING | PRIVATE | HOLD | OTHER reason: opt(v.string()), externalRef: opt(v.string()), guestName: opt(v.string()), amount: opt(v.number()), notes: opt(v.string()), reservationId: v.optional(v.id("reservations")), maintenanceId: v.optional(v.id("maintenanceTickets")), pendingApproval: v.boolean(), releaseOnCleaning: v.boolean(), createdById: v.optional(v.id("users")), createdAt: ms, }) .index("by_apartment_start", ["apartmentId", "startDate"]) .index("by_type", ["type"]) .index("by_reservation", ["reservationId"]) .index("by_start", ["startDate"]), /** Immutable inventory ledger. */ inventoryEvents: defineTable({ apartmentId: v.id("apartments"), action: v.string(), startDate: day, endDate: day, nights: v.number(), previousState: opt(v.string()), newState: opt(v.string()), reason: opt(v.string()), source: opt(v.string()), reservationId: v.optional(v.id("reservations")), blockId: v.optional(v.id("apartmentBlocks")), userId: v.optional(v.id("users")), userName: v.string(), estimatedValue: opt(v.number()), at: ms, }) .index("by_apartment_at", ["apartmentId", "at"]) .index("by_action_at", ["action", "at"]) .index("by_reservation", ["reservationId"]) .index("by_at", ["at"]), // ── Customers ───────────────────────────────────────────── customers: defineTable({ code: v.string(), // CUS-0001 firstName: v.string(), lastName: v.string(), fullName: v.string(), nameKey: v.string(), // normalised for duplicate detection phone: v.string(), phoneKey: v.string(), // last 8 digits secondaryPhone: opt(v.string()), email: opt(v.string()), nationality: opt(v.string()), dateOfBirth: opt(day), idType: opt(v.string()), idNumber: opt(v.string()), idExpiration: opt(day), address: opt(v.string()), preferredLanguage: v.string(), notes: opt(v.string()), photoStorageId: v.optional(v.id("_storage")), /** Chosen guest avatar (0-99). Unset means the one derived from the id. */ avatarIndex: v.optional(v.number()), isBlacklisted: v.boolean(), riskLevel: v.string(), // NORMAL | WATCHLIST | HIGH_ATTENTION | RESTRICTED | BLOCKED riskReason: opt(v.string()), riskSetAt: opt(ms), riskSetById: v.optional(v.id("users")), verificationStatus: v.string(), // UNVERIFIED | PENDING | VERIFIED verifiedAt: opt(ms), mergedIntoId: v.optional(v.id("customers")), updatedAt: ms, deletedAt: opt(ms), }) .index("by_code", ["code"]) .index("by_phoneKey", ["phoneKey"]) .index("by_idNumber", ["idNumber"]) .index("by_email", ["email"]) .index("by_nameKey", ["nameKey"]) .index("by_risk", ["riskLevel"]) .index("by_updated", ["updatedAt"]) .searchIndex("search", { searchField: "fullName", filterFields: ["deletedAt"] }), customerNotes: defineTable({ customerId: v.id("customers"), authorId: v.id("users"), body: v.string(), at: ms }).index("by_customer", ["customerId"]), customerIncidents: defineTable({ code: v.string(), // INC-0001 customerId: v.id("customers"), reservationId: v.optional(v.id("reservations")), type: v.string(), severity: v.string(), title: v.string(), description: opt(v.string()), amount: opt(v.number()), occurredAt: ms, reportedById: v.id("users"), resolvedAt: opt(ms), resolution: opt(v.string()), }) .index("by_customer", ["customerId", "occurredAt"]) .index("by_code", ["code"]) .index("by_open", ["resolvedAt", "occurredAt"]), // ── Reservations ────────────────────────────────────────── reservations: defineTable({ code: v.string(), // RES-1001 customerId: v.id("customers"), apartmentId: v.id("apartments"), checkIn: day, checkOut: day, originalCheckIn: opt(day), originalCheckOut: opt(day), actualCheckOut: opt(day), nights: v.number(), adults: v.number(), children: v.number(), source: v.string(), status: v.string(), // INQUIRY | PENDING | CONFIRMED | CHECKED_IN | CHECKED_OUT | CANCELLED | NO_SHOW nightlyPrice: v.number(), discount: v.number(), totalAmount: v.number(), deposit: v.number(), amountPaid: v.number(), paymentMethod: opt(v.string()), createdById: v.id("users"), assignedToId: v.optional(v.id("users")), externalRef: opt(v.string()), internalNotes: opt(v.string()), customerRequests: opt(v.string()), cancelledAt: opt(ms), cancelReason: opt(v.string()), checkedInAt: opt(ms), checkedInById: v.optional(v.id("users")), checkedOutAt: opt(ms), checkedOutById: v.optional(v.id("users")), checkInNotes: opt(v.string()), checkOutNotes: opt(v.string()), earlyCheckout: v.boolean(), releasedNights: v.number(), recoveredNights: v.number(), recoveredFromReservationId: v.optional(v.id("reservations")), stayGroupId: opt(v.string()), segmentIndex: v.number(), createdAt: ms, updatedAt: ms, }) .index("by_code", ["code"]) .index("by_apartment_checkIn", ["apartmentId", "checkIn"]) .index("by_checkIn", ["checkIn"]) .index("by_checkOut", ["checkOut"]) .index("by_status_checkIn", ["status", "checkIn"]) .index("by_customer_checkIn", ["customerId", "checkIn"]) .index("by_createdBy_createdAt", ["createdById", "createdAt"]) .index("by_createdAt", ["createdAt"]) .index("by_stayGroup", ["stayGroupId"]), /** * A single row ("staff"), holding the last time any staff member was seen. * The public site reads this instead of the users table: a heartbeat used to * invalidate a query over every user document for every connected visitor. */ presence: defineTable({ key: v.string(), lastSeenAt: ms }).index("by_key", ["key"]), /** Website / ads enquiries: every visitor who left a phone number. */ leads: defineTable({ name: v.string(), phone: v.string(), phoneKey: v.string(), email: opt(v.string()), apartmentId: v.optional(v.id("apartments")), checkIn: opt(day), checkOut: opt(day), guests: v.number(), message: opt(v.string()), /** LEAD_FORM | WAITLIST | CALLBACK | EXIT */ kind: v.string(), status: v.string(), // NEW | CONTACTED | CONVERTED | LOST utmSource: opt(v.string()), utmMedium: opt(v.string()), utmCampaign: opt(v.string()), utmContent: opt(v.string()), fbclid: opt(v.string()), referrer: opt(v.string()), page: opt(v.string()), handledById: v.optional(v.id("users")), handledAt: opt(ms), reservationId: v.optional(v.id("reservations")), customerId: v.optional(v.id("customers")), notes: opt(v.string()), createdAt: ms, }) .index("by_status_createdAt", ["status", "createdAt"]) .index("by_createdAt", ["createdAt"]) .index("by_phoneKey_createdAt", ["phoneKey", "createdAt"]), /** Short-lived "someone is booking this right now" locks from the front desk. */ bookingHolds: defineTable({ apartmentId: v.id("apartments"), checkIn: day, checkOut: day, userId: v.id("users"), userName: v.string(), startedAt: ms, expiresAt: ms, }) .index("by_apartment", ["apartmentId"]) .index("by_user", ["userId"]) .index("by_expires", ["expiresAt"]), reservationGuests: defineTable({ reservationId: v.id("reservations"), fullName: v.string(), idNumber: opt(v.string()), isChild: v.boolean() }).index("by_reservation", ["reservationId"]), reservationHistory: defineTable({ reservationId: v.id("reservations"), type: v.string(), previousValue: opt(v.string()), newValue: opt(v.string()), reason: opt(v.string()), fromApartmentId: v.optional(v.id("apartments")), toApartmentId: v.optional(v.id("apartments")), priceDifference: opt(v.number()), performedById: v.optional(v.id("users")), at: ms, }).index("by_reservation_at", ["reservationId", "at"]), payments: defineTable({ code: v.string(), // PAY-0001 reservationId: v.id("reservations"), customerId: v.id("customers"), amount: v.number(), type: v.string(), // PAYMENT | DEPOSIT | REFUND | DEPOSIT_REFUND method: v.string(), paidAt: ms, recordedById: v.id("users"), notes: opt(v.string()), reversedAt: opt(ms), reversalReason: opt(v.string()), }) .index("by_reservation", ["reservationId"]) .index("by_customer", ["customerId"]) .index("by_paidAt", ["paidAt"]) .index("by_code", ["code"]), contracts: defineTable({ code: v.string(), // CTR-0001 reservationId: v.id("reservations"), customerId: v.id("customers"), version: v.number(), status: v.string(), // GENERATED | SIGNED | VOID contentHtml: v.string(), terms: v.string(), generatedById: v.id("users"), signedAt: opt(ms), signatureData: opt(v.string()), at: ms, }) .index("by_reservation", ["reservationId"]) .index("by_customer", ["customerId"]) .index("by_code", ["code"]) .index("by_status", ["status"]), documents: defineTable({ code: v.string(), // DOC-000001 category: v.string(), fileName: v.string(), storageId: v.id("_storage"), mimeType: v.string(), size: v.number(), customerId: v.optional(v.id("customers")), reservationId: v.optional(v.id("reservations")), apartmentId: v.optional(v.id("apartments")), expenseId: v.optional(v.id("expenses")), uploadedById: v.id("users"), replacesId: v.optional(v.id("documents")), isSensitive: v.boolean(), at: ms, deletedAt: opt(ms), }) .index("by_customer", ["customerId"]) .index("by_reservation", ["reservationId"]) .index("by_apartment", ["apartmentId"]) .index("by_expense", ["expenseId"]) .index("by_category", ["category"]) .index("by_code", ["code"]), // ── Money ───────────────────────────────────────────────── expenseCategories: defineTable({ key: v.string(), name: v.string(), icon: opt(v.string()), sortOrder: v.number(), isActive: v.boolean() }).index("by_key", ["key"]), expenses: defineTable({ code: v.string(), // EXP-0001 date: day, categoryId: v.id("expenseCategories"), apartmentId: v.optional(v.id("apartments")), description: v.string(), amount: v.number(), paymentMethod: v.string(), vendor: opt(v.string()), isRecurring: v.boolean(), recurrence: opt(v.string()), recurringParentId: v.optional(v.id("expenses")), addedById: v.id("users"), notes: opt(v.string()), updatedAt: ms, deletedAt: opt(ms), }) .index("by_date", ["date"]) .index("by_category", ["categoryId"]) .index("by_apartment", ["apartmentId"]) .index("by_code", ["code"]) .index("by_parent", ["recurringParentId"]), commissions: defineTable({ code: v.string(), // COM-0001 workerId: v.id("users"), reservationId: v.id("reservations"), amount: v.number(), status: v.string(), // PENDING | APPROVED | PAID | CANCELLED | REVERSED triggerEvent: v.string(), approvedById: v.optional(v.id("users")), approvedAt: opt(ms), paidAt: opt(ms), adminNotes: opt(v.string()), history: v.array(v.object({ from: v.string(), to: v.string(), by: v.string(), at: ms, note: v.optional(v.string()) })), createdAt: ms, }) .index("by_worker", ["workerId", "createdAt"]) .index("by_reservation", ["reservationId"]) .index("by_status", ["status"]) .index("by_code", ["code"]), // ── Operations ──────────────────────────────────────────── tasks: defineTable({ title: v.string(), description: opt(v.string()), type: v.string(), apartmentId: v.optional(v.id("apartments")), reservationId: v.optional(v.id("reservations")), customerId: v.optional(v.id("customers")), assigneeId: v.optional(v.id("users")), createdById: v.id("users"), priority: v.string(), dueDate: opt(day), dueTime: opt(v.string()), status: v.string(), // TODO | IN_PROGRESS | COMPLETED | CANCELLED notes: opt(v.string()), completedAt: opt(ms), createdAt: ms, updatedAt: ms, }) .index("by_assignee_status", ["assigneeId", "status"]) .index("by_status", ["status"]) .index("by_apartment", ["apartmentId"]) .index("by_reservation", ["reservationId"]) .index("by_customer", ["customerId"]) .index("by_dueDate", ["dueDate"]), cleaningTasks: defineTable({ apartmentId: v.id("apartments"), reservationId: v.optional(v.id("reservations")), assigneeId: v.optional(v.id("users")), status: v.string(), // NEEDS_CLEANING | IN_PROGRESS | READY scheduledFor: opt(day), startedAt: opt(ms), completedAt: opt(ms), completedById: v.optional(v.id("users")), notes: opt(v.string()), createdAt: ms, }) .index("by_apartment_status", ["apartmentId", "status"]) .index("by_status", ["status"]) .index("by_createdAt", ["createdAt"]), maintenanceTickets: defineTable({ code: v.string(), // MNT-0001 apartmentId: v.id("apartments"), title: v.string(), category: v.string(), priority: v.string(), description: opt(v.string()), assigneeId: v.optional(v.id("users")), reportedById: v.id("users"), cost: v.number(), status: v.string(), // REPORTED | IN_PROGRESS | WAITING | COMPLETED blocksApartment: v.boolean(), startDate: opt(day), completionDate: opt(day), blockId: v.optional(v.id("apartmentBlocks")), createdAt: ms, updatedAt: ms, }) .index("by_apartment_status", ["apartmentId", "status"]) .index("by_status", ["status"]) .index("by_code", ["code"]) .index("by_createdAt", ["createdAt"]), // ── Notifications ───────────────────────────────────────── notifications: defineTable({ userId: v.id("users"), type: v.string(), title: v.string(), body: v.string(), priority: v.string(), href: opt(v.string()), entityType: opt(v.string()), entityId: opt(v.string()), readAt: opt(ms), at: ms, }) .index("by_user_at", ["userId", "at"]) .index("by_user_read", ["userId", "readAt"]), notificationRules: defineTable({ eventType: v.string(), label: v.string(), enabled: v.boolean(), inApp: v.boolean(), email: v.boolean(), push: v.boolean(), priority: v.string(), recipientRoles: v.array(v.string()), recipientUserIds: v.array(v.id("users")), notifyAssigned: v.boolean(), notifyActor: v.boolean(), }).index("by_event", ["eventType"]), // ── Ledger & settings ───────────────────────────────────── auditLog: defineTable({ code: v.string(), // EVT-000001 action: v.string(), module: v.string(), severity: v.string(), // INFO | WARNING | CRITICAL userId: v.optional(v.id("users")), userName: v.string(), roleKey: v.string(), entityType: opt(v.string()), entityId: opt(v.string()), entityLabel: opt(v.string()), previousValue: opt(v.string()), newValue: opt(v.string()), reason: opt(v.string()), apartmentId: v.optional(v.id("apartments")), reservationId: v.optional(v.id("reservations")), customerId: v.optional(v.id("customers")), ipAddress: opt(v.string()), device: opt(v.string()), browser: opt(v.string()), sessionId: opt(v.string()), at: ms, }) .index("by_at", ["at"]) .index("by_user_at", ["userId", "at"]) .index("by_module_action", ["module", "action"]) .index("by_action_at", ["action", "at"]) .index("by_entity", ["entityType", "entityId"]) .index("by_reservation", ["reservationId"]) .index("by_apartment", ["apartmentId"]) .index("by_customer", ["customerId"]) .index("by_severity_at", ["severity", "at"]) .index("by_code", ["code"]), settings: defineTable({ key: v.string(), value: v.any(), group: v.string(), updatedAt: ms }).index("by_key", ["key"]).index("by_group", ["group"]), sequences: defineTable({ name: v.string(), value: v.number() }).index("by_name", ["name"]), });