Real-time Data Foundation Tiquo’s data layer is built on Convex, providing a real-time, reactive database that eliminates the complexity of traditional polling or WebSocket implementations.
Core Concepts Live Queries Every query in Tiquo is a live subscription. When data changes anywhere in the system, all connected clients update automatically:
// This query automatically updates when customers change const customers = useQuery ( api . customers . list , { locationId: "loc_123" , lifecycle: "active" , }); // UI reflects changes from any source: // - Other dashboard users // - Mobile app updates // - API calls // - PDQ terminal transactions
Optimistic Updates Mutations update the UI immediately, before the server confirms:
User Action
User clicks “Create Customer”
Immediate Update
UI shows the new customer instantly
Server Processing
Convex processes the mutation
Confirmation
Server confirms or rolls back if failed
This pattern provides sub-100ms perceived latency for most operations.
Database Schema Core Tables
Customer profiles and contact information: Field Type Description firstNamestring Customer’s first name lastNamestring Customer’s last name emailstring Primary email address phonestring? Phone number clerkOrganizationIdstring Organization isolation lifecycleenum lead, active, at-risk, churned clvnumber? Customer lifetime value notesstring? Internal notes tagsarray Customer tags customFieldsobject Dynamic custom fields
All order types (bookings, enquiries, purchases): Field Type Description customerIdid Reference to customer locationIdid Reference to location typeenum booking, enquiry, purchase statusenum pending, confirmed, completed, cancelled itemsarray Line items with services/products totalnumber Order total paidAmountnumber Amount paid scheduledAtnumber? Appointment time notesstring? Order notes
Service definitions and configurations: Field Type Description namestring Service name descriptionstring Service description categoryIdid Reference to category durationnumber Duration in minutes pricenumber Base price locationIdid Reference to location isActiveboolean Availability status resourcesarray Required resources
Physical or virtual business locations: Field Type Description namestring Location name addressobject Address details timezonestring Location timezone operatingHoursobject Hours by day settingsobject Location-specific settings floorPlanIdid? Associated floor plan
Loyalty and membership programs: Field Type Description customerIdid Reference to customer programIdid Reference to program tierstring Current tier level pointsnumber Accumulated points startDatenumber Membership start expiryDatenumber? Expiration date walletPassIdstring? Apple/Google Wallet ID
Analytics Tables
Pre-aggregated metrics for fast dashboards: Field Type Description datestring Aggregation date locationIdid Location reference metricstring Metric name valuenumber Metric value dimensionsobject Additional dimensions
Activity timeline for customers and orders: Field Type Description entityTypeenum customer, order, etc. entityIdid Reference to entity actionstring Action type userIdstring User who performed action timestampnumber When it occurred metadataobject Additional details
Query Patterns Filtered Lists // Get active customers for a location export const list = query ({ args: { locationId: v . id ( "locations" ), lifecycle: v . optional ( v . string ()), search: v . optional ( v . string ()), limit: v . optional ( v . number ()), }, handler : async ( ctx , args ) => { let q = ctx . db . query ( "customers" ) . withIndex ( "by_location" , ( q ) => q . eq ( "locationId" , args . locationId )); if ( args . lifecycle ) { q = q . filter (( q ) => q . eq ( q . field ( "lifecycle" ), args . lifecycle )); } if ( args . search ) { q = q . filter (( q ) => q . or ( q . contains ( q . field ( "firstName" ), args . search ), q . contains ( q . field ( "lastName" ), args . search ), q . contains ( q . field ( "email" ), args . search ) ) ); } return await q . take ( args . limit ?? 50 ); }, });
Aggregations // Calculate customer lifetime value export const calculateCLV = mutation ({ args: { customerId: v . id ( "customers" ) }, handler : async ( ctx , args ) => { const orders = await ctx . db . query ( "orders" ) . withIndex ( "by_customer" , ( q ) => q . eq ( "customerId" , args . customerId )) . filter (( q ) => q . eq ( q . field ( "status" ), "completed" )) . collect (); const totalSpend = orders . reduce (( sum , order ) => sum + order . total , 0 ); const avgOrderValue = totalSpend / orders . length ; // Update customer CLV await ctx . db . patch ( args . customerId , { clv: totalSpend , avgOrderValue , orderCount: orders . length , }); }, });
Indexes Tiquo uses strategic indexes for query performance:
// Example index definitions export default defineSchema ({ customers: defineTable ({ // ... fields }) . index ( "by_organization" , [ "clerkOrganizationId" ]) . index ( "by_location" , [ "locationId" ]) . index ( "by_email" , [ "email" ]) . index ( "by_lifecycle" , [ "lifecycle" , "locationId" ]) . searchIndex ( "search" , { searchField: "searchText" , filterFields: [ "locationId" , "lifecycle" ], }) , orders: defineTable ({ // ... fields }) . index ( "by_customer" , [ "customerId" ]) . index ( "by_location" , [ "locationId" ]) . index ( "by_status" , [ "status" , "locationId" ]) . index ( "by_date" , [ "scheduledAt" , "locationId" ]) , }) ;
File Storage Convex provides built-in file storage for:
Customer profile photos
Product images
Document attachments
Email campaign assets
Floor plan exports
// Upload a customer photo export const uploadPhoto = mutation ({ args: { customerId: v . id ( "customers" ), storageId: v . id ( "_storage" ), }, handler : async ( ctx , args ) => { const url = await ctx . storage . getUrl ( args . storageId ); await ctx . db . patch ( args . customerId , { photoUrl: url , photoStorageId: args . storageId , }); }, });
Scheduled Jobs Background processing with Convex crons:
// crons.ts import { cronJobs } from "convex/server" ; const crons = cronJobs (); // Daily CLV recalculation crons . daily ( "recalculate-clv" , { hourUTC: 2 , minuteUTC: 0 }, api . clvCalculation . runBatch ); // Hourly analytics aggregation crons . interval ( "aggregate-analytics" , { hours: 1 }, api . analyticsAggregation . run ); // Check booking reminders every 15 minutes crons . interval ( "booking-reminders" , { minutes: 15 }, api . bookingNotifications . checkReminders ); export default crons ;
Data Isolation Organization-based Isolation All data is isolated by Clerk organization:
// Every query filters by organization export const list = query ({ handler : async ( ctx ) => { const identity = await ctx . auth . getUserIdentity (); const orgId = identity ?. org_id ; if ( ! orgId ) throw new Error ( "Not authenticated" ); return await ctx . db . query ( "customers" ) . withIndex ( "by_organization" , ( q ) => q . eq ( "clerkOrganizationId" , orgId )) . collect (); }, });
Row-level Security Each mutation validates ownership:
export const update = mutation ({ args: { customerId: v . id ( "customers" ), updates: v . object ({ /* ... */ }), }, handler : async ( ctx , args ) => { const customer = await ctx . db . get ( args . customerId ); const identity = await ctx . auth . getUserIdentity (); // Verify organization ownership if ( customer ?. clerkOrganizationId !== identity ?. org_id ) { throw new Error ( "Unauthorized" ); } await ctx . db . patch ( args . customerId , args . updates ); }, });
Migrations Database migrations for schema evolution:
// migrations/add-clv-fields.ts export const migrate = internalMutation ({ handler : async ( ctx ) => { const customers = await ctx . db . query ( "customers" ). collect (); for ( const customer of customers ) { if ( customer . clv === undefined ) { await ctx . db . patch ( customer . _id , { clv: 0 , avgOrderValue: 0 , orderCount: 0 , }); } } }, });
