Data Dictionary
This document details the complete database schema as defined by TypeORM migrations, with functional descriptions for each table and column.
1. Core Entities
users
Represents all system users. The role column determines permissions (RBAC). Technically, a single user row represents one "account", but salon-users allows them to be associated with multiple salons.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| fullname | varchar | - | Display name for reports and UI. |
| email | varchar | - | Unique identifier for login (Admins/Managers). Nullable for Technicians who use techCode. |
| phone | varchar | - | Contact number. |
| role | varchar | - | SUPERADMIN, MANAGER, RECEPTIONIST, TECHNICIAN. |
| status | varchar | 'ACTIVE' | Soft deletion/ban flag. |
| avatarColor | varchar | '#838dc4' | UI styling for avatar circles. |
| isOnlineBookingAvailable | boolean | - | If true, this technician appears in the customer online booking flow. |
| isSelfCheckInAvailable | boolean | - | If true, this technician appears on the Kiosk/Self Check-in iPad. |
| address | varchar | - | Mailing address. |
| city | varchar | - | Mailing city. |
| state | varchar | - | Mailing state. |
| zip | varchar | - | Mailing zip. |
| gender | varchar | - | Used for gender-preference booking matching. |
salons
Represents physical locations (Tenants). Almost all other tables have a salonId to enforce data isolation.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| name | varchar | - | Salon name (e.g., "Wynn Nails"). |
| address | varchar | - | Physical location. |
| timezone | varchar | - | Critical for queue calculations, appointment times and business hours. |
| city | varchar | - | - |
| state | varchar | - | - |
| zip | varchar | - | - |
tickets
The central operational unit. Represents a "Visit" or "Transaction", whether it's a Walk-in or a pre-booked Appointment. Linked to a daily signin-sheet.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| status | varchar | 'Pending' | Lifecycle: Pending -> Serving -> Completed (or Cancelled). |
| checkin | timestamp | - | The exact time the customer arrived/checked in. |
| ticketNumber | int | - | Daily sequential ID (1, 2, 3...) reset per SigninSheet. |
| partySize | int | - | Number of people in this group (affects turn calculation). |
| isWalkin | boolean | false | Distinguishes pure walk-ins from appointments. |
| technicianId | int | - | The technician servicing this ticket. |
| customerId | int | - | Link to the specific walk-in customer profile. |
| signinSheetId | int | - | The daily sheet this ticket belongs to. |
| genderCustomer | varchar | - | Customer's gender preference for the technician (renamed from preference). |
| totalPrice | float | - | Final charge amount. |
| splitCode | varchar | - | Used if a single ticket is split among multiple techs. |
| splitIndex | int | - | - |
| partyCode | varchar | - | Group identifier for multi-ticket parties. |
| appointment | boolean | - | True if this ticket originated from an appointment record. |
| serviceNote | varchar | - | Notes for the technician. |
| turnCount | float | - | calculated turns value for the queue system (usually 1.0 or 0.5). |
| turnRule | varchar | - | Explanation of which rule set the turn calculation. |
| appointmentTime | varchar | - | Snapshot of scheduled time if applicable. |
| totalBackbar | float | 0 | Cost of supplies used (deducted from revenue). |
| isTechRequest | boolean | false | True if customer requested a specific technician (affects turns). |
| genderRequest | varchar | - | Specific gender request (e.g., "Female Tech only"). |
| notifyTech | boolean | - | Flag to trigger push notification to tech. |
| salonId | int | - | Tenant ID. |
signin-sheets
Represents a single "Business Day". Tickets must belong to an open sheet.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| date | varchar | - | Day of operation (YYYY-MM-DD). |
| status | varchar | - | OPEN (accepting tickets) or CLOSED. |
| salonId | int | - | Tenant ID. |
| turnSettingsId | int | - | Snapshot of turn settings active for this specific day. |
services
The menu of available salon services.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| name | varchar | - | e.g., "Manicure", "Pedicure". |
| code | varchar | - | Short code for quick entry. |
| price | float | 0 | Base price. |
| duration | float | 0 | Estimated time (minutes) for scheduling. |
| turnCount | float | 0 | How many "turns" this service serves in the queue rotation. |
| backbar | float | 0 | Supply cost. |
| description | varchar | - | Public description for online booking. |
| isOnlineBookingAvailable | boolean | - | Visibility toggle for online booking. |
| isSelfCheckInAvailable | boolean | - | Visibility toggle for kiosk. |
| index_reference | int | - | Sorting order. |
| index_reference_selfCheckIn | int | - | Sorting order on kiosk. |
| salonId | int | - | Tenant ID. |
| categoryId | int | - | Grouping (e.g., "Nails", "Waxing"). |
customer
Persistent profiles for recurring clients (synced with AWS Cognito Customer Pool).
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| firstName | varchar | - | - |
| lastName | varchar | - | - |
| fullName | varchar | - | Derived field. |
| email | varchar | - | Contact email. |
| phone | varchar | - | Unique identifier usually used for lookup. |
| cognitoUsername | varchar | - | AWS Cognito Sub ID/Username. |
| status | varchar | 'active' | - |
| gender | varchar | 'F' | - |
| canReceiveMessage | boolean | true | SMS Marketing consent toggle. |
| isOnlineBookingAvailable | boolean | true | Can this user book online? |
| isAgreementAccepted | boolean | true | Terms of service status. |
| lastReceivedCampaign | varchar | - | Timestamp of last marketing blast. |
| note | varchar | - | Internal staff notes. |
| firstCheckInDate | varchar | - | Stats: first visit. |
| lastCheckInDate | varchar | - | Stats: most recent visit. |
| salonId | int | - | Tenant ID. |
appointment
Reservations for future dates. When a customer arrives, this is converted/linked to a ticket.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| appointmentDate | varchar | - | YYYY-MM-DD. |
| startTime | varchar | - | HH:MM format. |
| endTime | varchar | - | Calculated based on service duration. |
| status | varchar | - | CONFIRMED, CANCELLED, CHECKED_IN, NO_SHOW. |
| confirmationStatus | varchar | 'NONE' | CONFIRMED_BY_USER, NONE. |
| guestName | varchar | - | Name provided at booking. |
| gender | varchar | - | Guest gender. |
| type | varchar | - | ONLINE or MANUAL (internal booking). |
| appointmentGroupId | varchar | - | Group ID for multi-person bookings. |
| partySize | int | - | - |
| createdByNamespace | varchar | - | Origin of booking (e.g., CustomerApp, AdminPanel). |
| totalPrice | float | 0 | Estimated price. |
| totalBackbar | float | 0 | - |
| isRequested | boolean | - | True if specific tech was requested. |
| ticketNote | varchar | - | Note to copy to Ticket. |
| technicianId | int | - | Preferred technician. |
| customerId | int | - | Link to customer profile. |
| salonId | int | - | Tenant ID. |
| bookedById | int | - | Reference to booked_by source (e.g., "Phone", "Walk-in"). |
| experienceAppointmentId | int | - | Link to feedback/review record. |
| ticketReferenceId | int | - | Link to ticket_reference. |
2. Operational & Details
ticket-services
Resolves the many-to-many relationship between Tickets and Services. Records the actual price at the time of service (snapshot).
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| serviceId | int | - | Link to original Service definition. |
| code | varchar | - | Snapshot code. |
| name | varchar | - | Snapshot name. |
| price | float | 0 | Actual price charged. |
| turnCount | float | - | Turns awarded for this specific item. |
| duration | float | 0 | - |
| backbar | float | 0 | - |
| ticketId | int | - | Owner ticket. |
ticket-addons
Records extra add-ons (modifiers) applied to a ticket.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | Primary Key. |
| serviceId | int | - | Parent service ID. |
| addonId | int | - | Addon definition ID. |
| code | varchar | - | - |
| name | varchar | - | - |
| price | float | 0 | - |
| turnCount | float | - | - |
| duration | float | 0 | - |
| backbar | float | 0 | - |
| ticketId | int | - | Owner ticket. |
ticket-customers
A lightweight customer record stored directly with the ticket. capturing the name/number for Walk-ins who might not have a full customer profile yet.
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| name | varchar | Guest name (Nullable for quick check-ins). |
| phone | varchar | Contact number. |
| splitCode | varchar | - |
appointment-services
Items scheduled for a future appointment.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| serviceId | int | - | FK to Service. |
| type | varchar | 'SERVICE' | - |
| code | varchar | - | - |
| name | varchar | - | - |
| price | float | 0 | Estimated price. |
| turnCount | float | - | - |
| duration | float | 0 | - |
| backbar | float | 0 | - |
| appointmentId | int | - | Owner appointment. |
appointment-services-addons
Add-ons selected during booking.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| status | varchar | 'inactive' | - |
| appointmentId | int | - | Owner appointment. |
| serviceId | int | - | Parent Service. |
| addOnsId | int | - | Addon definition. |
| ticketId | int | - | (Deprecated) Legacy link. |
salon-users
Join table linking Users to Salons. Since a user can work at multiple salons, this table stores context-specific data like their "Tech Number" for that specific location.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| techCode | varchar | - | Clock-in code for this salon. |
| techNumber | int | - | Internal ID # for the queue board at this salon. |
| status | varchar | 'ACTIVE' | Status at this specific location. |
| salonId | int | - | FK |
| userId | int | - | FK |
3. Configuration & Settings
salon-turn-settings
Defines the "rules of the game" for the technician queue (Who is next?).
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| method | varchar | - | Algorithm: ROUND_ROBIN, LOWEST_TURNS, etc. |
| turnType | varchar | - | DOLLAR_AMOUNT or COUNT. |
| combineTurnCount | boolean | - | If true, multiple services on one ticket might merge into 1 turn. |
| latePenaltyTurns | varchar | - | Penalty logic for late techs. |
| latePenaltyManual | int | 0 | Manual intervention value. |
| techRequestCount | boolean | - | Does a requested appointment count as a "turn"? |
| priceRule | int | - | Price threshold to count as a turn. |
| techRequestRule | boolean | - | - |
| techAvailableFirst | boolean | true | Priority rule. |
| earlyBonusActive | boolean | false | Do early bird techs get a bonus turn? |
| startTime | varchar | - | Shift definition. |
| endTime | varchar | - | Shift definition. |
| salonId | int | - | One-to-One with Salon. |
| bonusTurnId | int | - | Link to salon-turn-bonus rules. |
online-booking-settings
Configuration for the customer-facing booking site.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| onlineBookingLink | varchar | - | The specific slug/URL for this salon's booking page. |
| isEnabled | boolean | false | Master toggle for online booking. |
| startTime | varchar | - | Booking availability window start. |
| endTime | varchar | - | Booking availability window end. |
| minimumLeadTime | int | - | Minutes in advance required for booking. |
| isServicePricesEnabled | boolean | false | Show prices to customers? |
| isServiceDescriptionsEnabled | boolean | false | Show descriptions? |
| isShowAppointmentDelayed | boolean | false | - |
| appointmentDelayTime | int | 15 | Padding between slots. |
| isShowAppointmentAfterBusiness | boolean | false | Allow bookings that finish after close? |
| salonId | int | - | FK |
self-check-in-settings
Configuration for the iPad Kiosk.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| isShowServicePrices | boolean | false | - |
| isShowServiceDescriptions | boolean | false | - |
| isShowWalkIn | boolean | false | Allow Walk-ins on Kiosk? |
| autoDisableTechnicianRequest | int | - | Threshold to hide busy techs. |
| isEnabledCustomerAddServices | boolean | false | Can customers self-serve add-ons? |
| isEnabledCustomerDeleteServices | boolean | false | Can customers remove items? |
| salonId | int | - | FK |
add-ons
Definitions of "Extras" (e.g. "Nail Design", "Extra Massage").
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| code | varchar | - | Short code. |
| name | varchar | - | Internal name. |
| displayName | varchar | - | Customer facing name. |
| price | float | 0 | Cost. |
| duration | float | 0 | Time added. |
| backbar | float | 0 | - |
| setSelfCheckIn | boolean | - | Show on Kiosk? |
| setOnlineBooking | boolean | - | Show on Web? |
| turnCount | float | 0 | Does this add-on affect turn count? |
| index_reference | int | - | Order. |
| salonId | int | - | FK |
| serviceSubCategoryIdId | int | - | Parent category. |
service-categories
Categories for organizing the services menu (e.g. "Pedicure", "Manicure", "Wax").
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| name | varchar | - | - |
| description | varchar | - | - |
| index_reference | int | - | Sort order. |
| index_reference_selfCheckIn | int | - | Sort order on Kiosk. |
| salonId | int | - | FK |
service-subcategories
Sub-groups within categories.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| name | varchar | - | - |
| description | varchar | - | - |
| index_reference | int | - | - |
| index_reference_selfCheckIn | int | - | - |
| salonId | int | - | FK |
| serviceId | int | - | Link to parent Service (Note: Naming is slightly ambiguous, usually category -> sub -> service). |
booked_by
Dropdown options for "Source" when creating an appointment manually (e.g. "Instagram", "Walk-in", "Referral").
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| booked_name | varchar | - | Label for the source/reason (e.g., "Instagram", "Walk-in", "Manager"). |
| index_reference | int | - | Order. |
| isDefault | boolean | false | - |
| default_by | varchar | - | - |
| salonId | int | - | FK |
Usage: Acts as a customizable "Source" or "Originator" list. Used in appointment to track how it was booked (e.g. "Instagram"), and in technician-exception-schedule to track who/what blocked the time (e.g. "Manager" or "Holiday").
ticket_reference
Tags or references for tickets.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| reference_name | varchar | - | Label. |
| color | varchar | - | UI Badge color. |
| index_reference | int | - | - |
| isDefault | boolean | false | - |
| default_reference | varchar | - | - |
| salonId | int | - | FK |
4. Notifications & Marketing
campaign
Marketing blasts (SMS/Email) configured by the Manager.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| campaignName | varchar | - | Internal name. |
| message | varchar | - | Content. |
| campaignDate | varchar | - | Execution date. |
| sendTime | varchar | - | Execution time. |
| nextCampaignDate | varchar | - | For recurring campaigns. |
| campaignType | varchar | - | ONE_TIME or RECURRING. |
| campaignStatus | varchar | 'ACTIVE' | - |
| audienceCount | int | - | Estimated reach. |
| salonId | int | - | FK |
sms-sending
Audit log of every SMS sent via a campaign.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| phone | varchar | - | Recipient. |
| message | varchar | - | Copy of message. |
| type | varchar | - | - |
| sent | int | 0 | 1 if success. |
| failed | int | 0 | 1 if failed. |
| campaignId | int | - | Parent campaign. |
| salonId | int | - | FK |
message-history
History of transactional or marketing messages for a specific customer.
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| message | varchar | Content. |
| type | varchar | Marketing/Transactional. |
| customerId | int | Owner. |
review-booster
Automated "Please review us" text message settings.
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| isEnabled | boolean | - |
| hoursAfterCheckInForTextMessages | int | Delay before sending. |
| reviewMessage | varchar | The template body. |
| onlineReviewLink | varchar | URL to Google/Yelp. |
| salonId | int | FK |
5. Schedules & Exceptions
technician-schedule
Defines recurring weekly shifts for technicians.
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| day | varchar | MONDAY, TUESDAY, etc. |
| startTime | varchar | HH:MM. |
| endTime | varchar | HH:MM. |
| frequent | varchar | WEEKLY, etc. |
| startDate | varchar | Validity start. |
| endDate | varchar | Validity end. |
| technicianId | int | FK |
| salonId | int | FK |
technician-exception-schedule
One-off overrides (e.g. Sick day, Vacation, or extra shift).
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| startTime | varchar | - |
| endTime | varchar | - |
| type | varchar | OFF or WORK. |
| exceptionDate | varchar | Specific date. |
| blockNote | varchar | Reason. |
| technicianId | int | FK |
| salonId | int | FK |
| blockedById | int | FK |
6. Miscellaneous & Join Tables
faqs
CMS content for the FAQ section of the site.
| Column | Type | Description |
| :--- | :--- | :--- |
| id | SERIAL | PK |
| question | varchar | - |
| answer | varchar | - |
service-addon
Mapping between Services and Add-ons. (Many-to-Many).
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| addOnStatus | varchar | 'inactive' | - |
| salonId | int | - | FK |
| serviceId | int | - | FK |
| subcategoryId | int | - | FK |
| addOnId | int | - | FK |
technician-addons
Mapping showing which technicians are qualified/allowed to perform which add-ons.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| status | varchar | 'inactive' | - |
| technicianId | int | - | FK |
| serviceId | int | - | FK |
| subCategoryId | int | - | FK |
| addOnsId | int | - | FK |
| salonId | int | - | FK |
appointment-link
Generated links for appointment management/notifications.
| Column | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| id | SERIAL | - | PK |
| appointmentLinkId | varchar | - | Unique Token/ID. |
| date | varchar | - | - |
| phone | varchar | - | - |
| notificationType | varchar | 'NONE' | - |
| salonId | int | - | FK |