Pivot Database - Complete Field Reference

Table of Contents

1. User Management
2. Company Structure
3. Employee Management
4. Scheduling System
5. Attendance Tracking
6. Communication System
7. Notification System
8. Request Management
9. Integration Systems
10. System Administration

---

User Management

Users Table

Path: /Users/{userId}

Field	Type	Description	Required	Indexed
uid	string	User unique identifier	Yes	Primary Key
email	string	User email address	Yes	No
name	string	First name	Yes	No
surname	string	Last name	No	No
phone	string	Phone number	No	No
phoneCode	string	Country code	No	No
address	string	Physical address	No	No
bornDate	number	Birth date timestamp	No	No
avatar	string	Profile image URL	No	No
locale	string	Language preference (en/fr)	No	No
status	string	Account status (online/offline)	No	No
lastSeen	number	Last activity timestamp	No	Yes
isAdmin	boolean	Location owner flag (restaurant owner)	No	Yes
hasCreatedCompanies	boolean	Has created companies	No	No
headOfficeAccess	boolean	Multi-location access	No	No
incomplete	boolean	Profile incomplete flag	No	No
emergencyContact	string	Emergency contact name	No	No
emergencyPhone	string	Emergency phone	No	No
emergencyPhoneCode	string	Emergency country code	No	No
availabilities	object	Work availability	No	No

---

Company Structure

Companies Table

Path: /Companies/{companyId}

Field	Type	Description	Required	Indexed
key	string	Company unique ID	Yes	Yes
name	string	Company name	Yes	No
createdBy	string	Owner's userId	Yes	Yes
address	string	Company address	No	No
location	object	{lat, lng} coordinates	No	No
timezone	string	Company timezone	Yes	No
image	string	Company logo URL	No	No
imageSize	string	Logo display (cover/contain)	No	No
weekStartingDay	string	Week start day	Yes	No
payrollStartingDay	string	Payroll period start	Yes	No
defaultDuration	number	Default shift duration	No	No
notifyBefor	number	Notification lead time	No	Yes
hasAccessToAttendance	boolean	Attendance module enabled	No	Yes
allowedPunchInRange	number	Geofence radius (meters)	No	No
excludeBreaks	boolean	Exclude breaks from hours	No	No
isGeolocationEnabled	boolean	Location tracking on	No	No
timeRequired	object	Monthly hour requirements	No	No
jobs	object	Position definitions	No	No
daysStructure	object	Daily shift structure	No	No
breaks	object	Break configurations	No	No

Position Structure (within Companies.jobs)

Field	Type	Description
name	string	Position title
archived	boolean	Position inactive
rate	string	Hourly rate
additionalSalary	string	Bonus/tips
priority	number	Display order
subcategories	object	Position subdivisions

Subcategory Structure

Field	Type	Description
name	string	Subcategory name
acronym	string	Short code
color	string	Display color
archived	boolean	Subcategory inactive

---

Employee Management

Employees Table

Path: /Employees/{employeeId}

Field	Type	Description	Required	Indexed
uid	string	Employee unique ID	Yes	Primary Key
userId	string	Links to Users table	No	Yes
companyId	string	Links to Companies	Yes	Yes
name	string	First name	Yes	No
surname	string	Last name	Yes	No
email	string	Email address	Yes	No
emailVerified	boolean	Email confirmed	No	No
phone	string	Phone number	No	No
phoneCode	string	Country code	No	No
avatar	string	Profile photo URL	No	No
description	string	Bio/notes	No	No
direct	boolean	Direct employee	No	No
isAdmin	boolean	Company admin privileges	No	No
status	string	Employment status	No	No
onlineStatus	string	Current status	No	No
isOnline	boolean	Currently active	No	No
lastSeen	number	Last activity	No	No
locale	string	Language preference	No	No
hiring	number	Hire date timestamp	No	No
positions	array	Assigned positions	No	No
lastPositions	array	Previous positions	No	No
priority	object	Position priorities	No	No
additionalSalary	object	Extra compensation	No	No
availabilities	object	Work preferences	No	No
emergencyContact	string	Emergency name	No	No
emergencyPhone	string	Emergency number	No	No
customEmplNumber	string	Employee number	No	Yes
daysOff	object	Time off records	No	No

Availability Structure

Field	Type	Description
Monday-Sunday	object	Daily availability
maxDaysPerWeek	number	Maximum work days
maxHoursInWeek	number	Maximum hours

Daily Availability

Field	Type	Description
allDay	boolean	Available all day
shift1	object	Custom hours {start, end}
priority	number	Day preference (0-6)

---

Scheduling System

Schedule Table

Path: /Schedule/{companyId}/{weekStart}/{date}/{employeeId}/{positionId}/{subcategoryId}/{shiftKey}

Field	Type	Description	Required
start	number	Shift start time	Yes
end	number	Shift end time	No
onHold	boolean	Tentative shift	No
note	string	Shift notes	No
uid	string	Employee ID	Yes

OpenShifts Table

Path: /OpenShifts/{companyId}/{date}/{positionId}/{subcategoryId}/{openShiftId}

Field	Type	Description
start	number	Shift start
end	number	Shift end
positionId	string	Required position
subcategoryId	string	Position subcategory
date	string	Shift date
numberOfEmployees	number	Positions needed
numberOfEmployeesHold	number	Tentative positions
period	string	morning/evening
shiftKey	string	Shift identifier
onHold	boolean	Tentative status
note	string	Shift notes

WeeklyScheduleSettings Table

Path: /WeeklyScheduleSettings/{companyId}/{weekStart}

Field	Type	Description
generatedPositions	object	Auto-generated positions
publishedPositions	object	Published positions
allPositions	object	All position states
isCopy	boolean	Copied from template

ManualShifts Table

Path: /ManualShifts/{companyId}/{shiftId}

Field	Type	Description
date	string	Shift date
employeeId	string	Assigned employee
positionId	string	Position
subcategoryId	string	Subcategory
start	number	Start time
end	number	End time

---

Attendance Tracking

Attendance Table

Path: /Attendance/{companyId}/{employeeId}/{date}/{shiftKey}

Field	Type	Description
start	number	Clock in time
end	number	Clock out time
positionId	string	Working position
subpositionId	string	Position subcategory
rate	string/number	Hourly rate
additionalSalary	string/number	Extra pay
type	string	Shift type
isConflicting	boolean	Schedule conflict
manuallyCreated	boolean	Manual entry
key	string	Record identifier
breaks	object	Break records

Break Structure

Field	Type	Description
start	number	Break start
end	number	Break end
lengthRounded	number	Duration in minutes

---

Communication System

Chats Table

Path: /Chats/{companyId}/{chatId}

Field	Type	Description	Indexed
createdBy	string	Creator employeeId	No
createdAt	number	Creation timestamp	No
archived	boolean	Chat archived	No
type	string	private/group	No
name	string	Group chat name	No
key	string	Chat identifier	No
members	object	{employeeId: boolean}	Yes (values)
unread	object	{employeeId: boolean}	Yes (values)
lastMessage	object	Latest message preview	No

Messages Table

Path: /Messages/{chatId}/{messageId}

Field	Type	Description	Indexed
senderId	string	Sender employeeId	No
text	string	Message content	No
timestamp	number	Send time	No
files	array	Attached files	No
images	array	Attached images	No
seenAt	object	{employeeId: timestamp}	No
dialogId	string	Legacy chat ID	Yes

---

Notification System

Notifications Table

Path: /Notifications/{userId}/{notificationId}

Field	Type	Description	Indexed
companyId	string	Company context	No
createdAt	number	Creation time	No
updatedAt	number	Last update	Yes
employeeId	string	Target employee	No
group	string	people/schedule/posts	No
type	string	Notification type	Yes
status	string	Current status	Yes
statusEmployer	string	Employer status	Yes
time	number	Event time	Yes
key	string	Notification ID	No
seenBy	object	Read receipts	No
requestId	string	Related request	No

NotificationsFlatV2 Table

Path: /NotificationsFlatV2/{companyId}/{employeeId}/{state}/{type}/{notificationId}

Field	Type	Description	Indexed
createdAt	number	Creation timestamp	Yes
[type-specific]	various	Notification data	No

---

Request Management

Requests Table

Path: /Requests/{userId}/{requestId}

Field	Type	Description	Indexed
type	string	Request type	No
status	string	Current status	No
createdAt	number	Submission time	No
shiftString	string	Shift identifier	Yes
employeeId	string	Requester	No
companyId	string	Company	No
details	object	Request specifics	No

Request Types

• dayOff: Time off requests
• swap: Shift swap requests
• replace: Replacement requests
• emergency: Emergency coverage
• availabilities: Availability changes

---

Integration Systems

VeloceInvoices Table

Path: /VeloceInvoices/{companyId}/{invoiceId}

Field	Type	Description	Indexed
openLocalTime	number	Transaction time	Yes
data	object	Invoice details	No

MaitreDInvoices Table

Path: /MaitreDInvoices/{companyId}/{invoiceId}

Field	Type	Description	Indexed
openLocalTime	number	Transaction time	Yes
data	object	Invoice details	No

LightspeedSettings Table

Path: /LightspeedSettings/{settingId}

Field	Type	Description	Indexed
refresh_token	string	API token	Yes
config	object	Integration config	No

EmployeeIntegrationIds Table

Path: /EmployeeIntegrationIds/{companyId}/{employeeId}

Field	Type	Description
externalSystemId	string	System identifier
externalEmployeeId	string	External ID

---

System Administration

Tokens Table (Push Notifications)

Path: /Tokens/{userId}/{tokenId}

Field	Type	Description	Indexed
token	string	Device token	No
active	boolean	Token active	Yes
platform	string	iOS/Android	No
createdAt	number	Registration time	No

CompanySettings Table

Path: /CompanySettings/{companyId}

Field	Type	Description	Indexed
weatherApiEnabled	boolean	Weather integration	Yes
hasAccessToAttendance	boolean	Attendance module	No
hasLaborCost	boolean	Labor cost module	No
features	object	Feature flags	No
integrations	object	Active integrations	No

System Version Tables

Path: /VersionWeb/ and /VersionMobile/

Field	Type	Description
version	string	App version
timestamp	number	Release time
features	object	Version features
platform	string	Target platform

Support Table

Path: /Support/{supportId}

Field	Type	Description
userId	string	User requesting
type	string	Issue type
message	string	Support message
createdAt	number	Request time
status	string	Ticket status

---

Database Indexes Summary

Performance Indexes by Table

• Users: lastSeen, isAdmin
• Companies: createdBy, notifyBefor, key, hasAccessToAttendance
• Employees: companyId, userId, customEmplNumber
• Notifications: time, type, status, statusEmployer, updatedAt
• Chats: Member values, unread values
• Messages: dialogId
• Requests: shiftString
• Applicants: companyId, userId
• Posts: createdAt
• Tokens: active
• Integration Tables: Various timestamp and ID fields

---

Key Foreign Key Relationships

1. Users ↔ Employees: Users.uid = Employees.userId
2. Companies ↔ Employees: Companies.key = Employees.companyId
3. Users ↔ Companies: Users.uid = Companies.createdBy
4. Employees ↔ Schedules: Employees.uid = Schedule.uid
5. Companies ↔ Positions: Nested within company document
6. Positions ↔ Subcategories: Nested within position
7. Chats ↔ Messages: Chats.chatId = Messages.chatId
8. Employees ↔ Notifications: Employees.uid = Notifications.employeeId
9. Requests ↔ Notifications: Requests.requestId = Notifications.requestId
10. Companies ↔ All Entities: Most tables reference companyId