Database Schema Overview

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/package.json
• packages/db/src/seed.ts
• packages/db/drizzle/0000_normal_the_order.sql
• packages/db/drizzle/meta/0000_snapshot.json
• apps/api/package.json

The Subito project utilizes a PostgreSQL database managed via Drizzle ORM to handle a complex ecosystem of service bookings, user management, and geographic hub configurations. The schema is designed to support a multi-tenant-like structure where services are localized to specific hubs and micro-hubs, and bookings can be instant, scheduled, or recurring.

Sources: packages/db/package.json:30-34, packages/db/drizzle/0000_normal_the_order.sql:1-20

User and Identity Management

The core of the identity system revolves around the users table, which integrates with Firebase for authentication via firebase_uid. Users are assigned roles (customer, partner, or admin) that dictate their interactions with the system.

User Roles and Preferences

• Roles: Defined as an ENUM containing customer, partner, and admin.
• Preferences: Stored in user_preferences, managing notification toggles (SMS, Email, Push) and language settings.
• Session Management: Handled via refresh_tokens linked to specific devices.

Sources: packages/db/drizzle/0000_normal_the_order.sql:1, 46-77, packages/db/drizzle/meta/0000_snapshot.json:tables.public.users

Entity Relationship: Core Users

The following diagram illustrates the relationship between users, their authentication tokens, and their application preferences.

Service and Catalog Architecture

The system organizes its offerings through a hierarchy of categories, listings, and catalogs. This allows for base service definitions (listings) to have multiple pricing variants (catalogs) based on units like apartment size (BHK).

Table	Purpose	Key Fields
categories	Groups related services	name, slug, parent_id (for sub-categories)
listings	Defines a specific service type	category_id, base_price, duration, tags
catalogs	Specific sellable units/variants	listing_id, price, unit, min_quantity
add_ons	Optional extras for a service	price, listing_id or category_id

Sources: packages/db/drizzle/0000_normal_the_order.sql:167-240, packages/db/src/seed.ts:317-380

Geographic Hubs and Infrastructure

Services are localized using a two-tier hub system. hubs represent major regions (e.g., Hyderabad Central), while micro_hubs represent specific neighborhoods or clusters of pincodes.

Hub Configuration

• Hubs: Store regional coordinates and operational radius.
• Micro Hubs: Link to a parent hub and contain an array of supported pincodes.
• Hub Configs: Define operational hours (day_start_time, day_closing_time), slot durations, and capacity constraints per slot.

Sources: packages/db/drizzle/0000_normal_the_order.sql:129-166, packages/db/src/seed.ts:225-270

Booking and Fulfillment System

The booking system is the most complex component, supporting three distinct types: INSTANT, SCHEDULED, and RECURRING.

Booking States and History

Bookings progress through various statuses defined in the booking_status ENUM, including PENDING_PAYMENT, MATCHED, ARRIVING, and COMPLETED. Every state transition is recorded in booking_status_history for auditing.

Sources: packages/db/drizzle/0000_normal_the_order.sql:10, 411-450, packages/db/src/seed.ts:835-870

Recurring Bookings

When a booking is marked as RECURRING, a configuration is created in recurring_bookings. Individual instances (e.g., "Monday Service") are generated in booking_instances.

Sources: packages/db/drizzle/0000_normal_the_order.sql:362-383, 464-477, packages/db/src/seed.ts:770-790

Partner (Provider) Module

Users with the partner role have an associated profile in the partners table. This tracks their verification status, financial details, and performance metrics.

Field	Type	Description
status	ENUM	pending, approved, rejected, suspended
availability_status	ENUM	online, offline, busy
average_rating	Numeric	Calculated provider rating
total_earnings	Numeric	Lifetime earnings in INR

Sources: packages/db/drizzle/0000_normal_the_order.sql:2-4, 96-118, packages/db/src/seed.ts:580-620

Real-time Tracking and Payouts

• Locations: The partner_locations table stores high-frequency GPS updates (latitude, longitude, speed, heading).
• Availability: partner_availability defines the weekly schedule (days and times) a partner is willing to work.
• Payouts: Tracks transfers to partners with statuses like pending or completed.

Sources: packages/db/drizzle/0000_normal_the_order.sql:80-95, packages/db/src/seed.ts:630-660

Financial and Promotion Logic

The schema handles transactions via a combination of orders, payments, and refunds.

Payment Flow

1. Order Creation: An entry in orders links a user and a booking.
2. Payment Execution: The payments table stores gateway-specific details (e.g., juspay_txn_id, bank_reference_number).
3. Saved Methods: Users can save UPI IDs or Card tokens in saved_payment_methods.

Sources: packages/db/drizzle/0000_normal_the_order.sql:478-531, packages/db/src/seed.ts:920-960

Promotions and Credits

• Coupons: Supports both PERCENTAGE and FLAT discounts with constraints on min_cart_value and usage limits.
• Referrals: Tracks the referral_reward_status and amounts for both the referrer and referee.
• Credits: A virtual wallet system (user_credits) tracks balance and lifetime earnings, with a detailed credit_transactions log.

Sources: packages/db/drizzle/0000_normal_the_order.sql:250-269, 545-585, packages/db/src/seed.ts:1000-1050

Summary

The database schema is highly normalized and structured to support the lifecycle of on-demand home services. By separating service definitions from geographic configurations and provider availability, the system ensures scalability across different cities and service categories. Comprehensive auditing via status history and transaction logs maintains data integrity across complex financial and operational workflows.

Sources: packages/db/drizzle/0000_normal_the_order.sql:1-630, packages/db/src/seed.ts:40-1150