Complete Database Design

Database Design

This document defines the target database for the Headkount build phase.

Headkount will use this database as the main record for scheduling. External systems will remain the source for the data that they own.

Database scope

The database will hold six connected areas of information.

Area	Information
People and access	Users, beauty educators, roles, availability, work limits, and eligibility
Business network	Markets, retailers, Doors, and Brands
Contracted work	Contracts, commitments, required visits, and required Events
Scheduling	Schedule requests, generated versions, assignments, gaps, comparisons, and approvals
Daily operations	Retailer slots, bookings, Connecteam shifts, callouts, cancellations, and repairs
History and control	Source records, import problems, important changes, and approved agent actions

Table definitions

These are the target table names. Each definition explains the business purpose of one table.

People and access

Table	Business definition
`user`	A person who can sign in and take action in Headkount.
`account`	The sign-in method that belongs to a User.
`session`	One active sign-in period for a User.
`verification`	A secure record that supports sign-in and account verification.
`user_invitation`	An invitation for a person to join Headkount with an assigned role.
`beauty_educator`	An educator who can receive scheduled work.
`be_hour_rule`	The work target and hour limits for one beauty educator.
`be_regular_availability`	The regular weekly times when a beauty educator can work.
`be_blocked_time`	Time Off or existing work that blocks new assignments.
`be_market_eligibility`	The Markets where a beauty educator can work.
`be_brand_eligibility`	The Brands that a beauty educator can represent.
`be_door_eligibility`	The Doors where a beauty educator can work.

Business network

Table	Business definition
`market`	A geographic area that Headkount schedules as one operating unit.
`retailer`	A retail company that operates Doors.
`door`	A physical store where Headkount delivers work.
`door_proximity`	A relationship between nearby Doors that can support stacked visits.
`brand`	A beauty client that purchases Headkount services.

Contracted work

Table	Business definition
`contract`	The commercial agreement between Headkount and a Brand for a set period.
`service_commitment`	The amount and type of work promised for a Door and period.
`service_requirement`	One visit or Event that the schedule must place.

Scheduling

Table	Business definition
`schedule`	The planning period for one Market.
`schedule_change_request`	The original request to create or change a Schedule.
`scheduling_instruction`	One specific scheduling instruction from a change request.
`schedule_assignment_lock`	A decision to keep an Assignment in place during a new scheduling run.
`schedule_rule_exception`	An approved case that can differ from a standard scheduling rule.
`schedule_generation`	One scheduling run and the information that it used.
`schedule_version`	One complete and unchanged result from a scheduling run.
`schedule_assignment`	One placement of required work with an educator, Door, and time.
`schedule_requirement_result`	The scheduling result for one required visit or Event.
`schedule_version_diff`	The changes between a Schedule Version and its prior version.
`schedule_approval`	The acceptance of one complete Schedule Version.

Daily operations

Table	Business definition
`integration_account`	Approved access to a retailer portal or external service.
`retailer_slot`	An open time from a retailer portal.
`retailer_booking`	A retailer request or confirmation for a Schedule Assignment.
`external_shift`	A Connecteam shift that links to a Schedule Assignment.
`schedule_ticket`	A work item for a callout, cancellation, repair, or operating problem.
`activity_event`	An unchanged record of an important action or business change.

AI Agent control

Table	Business definition
`agent_run`	One User request that the AI Agent handles.
`agent_tool_call`	One external action that the AI Agent attempts.
`action_approval`	A human decision on an AI Agent action that changes business data.

Data imports

Table	Business definition
`ingestion_source`	An external source that sends information to Headkount.
`ingestion_run`	One import from an external source.
`ingestion_raw_record`	The original source evidence saved before Headkount changes its format.
`external_record_link`	The connection between a source record and a Headkount record.
`ingestion_issue`	A problem that stops an import from safely updating Headkount data.

Core model

Brand -> Contract -> Commitment -> Required Work
                                           |
Market -> Door <- Retailer                  |
   |                                       v
   +------ Beauty Educator ----------> Schedule
              |                            |
              +-- Availability             v
              +-- Work Limits         Schedule Version
              +-- Eligibility              |
                                           v
                                       Assignment
                                           |
                              +------------+------------+
                              |                         |
                       Retailer Booking          Connecteam Shift

A Contract defines the work that Headkount owes a Brand. A Commitment assigns that work to a Door and a period.

Required Work defines each visit or Event that the schedule must place. An Assignment connects that work to a beauty educator, time, and Door.

Schedule history

Each scheduling run creates a new Schedule Version. A new version never changes an earlier version.

Each version contains the complete schedule, its gaps, and its comparison with the prior version. An approval accepts one complete version.

This structure gives Headkount a clear record of what changed, why it changed, and who approved it.

Daily operations

The database connects the approved schedule to retailer portals and Connecteam.

Retailer Slot -> Assignment -> Retailer Booking -> Connecteam Shift
                       |
                       +-> Callout or Cancellation -> Repair

This connection keeps the planned work, retailer result, published shift, and later repair in one history.

Source ownership

Information	Source
Users, Contracts, required work, and Schedules	Headkount application
Beauty educator profiles and eligibility	Headkount application after migration
Existing shifts and Time Off	Connecteam
Open slots and booking results	Retailer portals
Messages and files	Slack
Historical visits and Events	Imported tracker records

The database links imported records to Headkount records. It does not replace the external source record.

Data controls

Each person, Brand, Door, Contract, and work requirement has one main record.
Approved Schedule Versions remain unchanged.
Important changes include the User, source, time, and reason.
Import problems remain visible until staff resolve them.
Secrets stay in a secret manager and do not enter business records.
The AI Agent uses approved application commands and cannot write directly to business records.

Migration

The Build phase will move current information into this model.

Save read-only copies of the tracker, Connecteam, Slack, retailer files, and the legacy database.
Standardize names, dates, times, locations, and status values.
Create the main People, Brand, Door, Contract, and Schedule records.
Link historical and external records to the main records.
Compare record counts and work totals before the application changes to the new database.

The migration keeps the source evidence and the links between old and new records.

Repository fit

The build keeps the current clean schemas for authentication, Markets, beauty educators, and Brands.

The build adds schemas for Doors, scheduling, integrations, daily operations, and activity history. The legacy database remains a migration source only.

Result

This model gives Headkount one connected record for contracted work, scheduling, retailer bookings, published shifts, exceptions, approvals, and operating history.