Records and Data Model

The data model is the backbone of the product. D2C relies on these operating records when shifts, staff responses, availability, notifications, and field reports need to stay connected.

What the Records Are Optimised For

The records are optimised for the flow D2C needs to trust. Client, campaign, and shift records keep each piece of work tied to the commercial job being delivered. Store records capture retailer, state, address, and coordinates so directions, map views, and routing can use the same location source. Staff records include skills, home state, optional coordinates, and optional hourly rate, so the same profile can support ranking, maps, and payroll estimates. Assignment records capture invitations, direct assignments, responses, and check-in time or location. Availability records cover unavailable and preferred windows, including recurring weekly patterns. Briefing files stay attached to shifts, shift reports connect completed work to samples, conversations, notes, and optional photo evidence, and notification records keep invite, assignment, nudge, cancel, and response messages inspectable even when provider delivery is unavailable. Equipment now exists as a catalogued record connected to shifts, although the visible product still needs final staff-facing equipment handling.

Important Constraints

Several constraints keep the operating record clean. Assignment records prevent duplicate invitations to the same shift and give direct assignment a repeatable path. Shift reports are limited per staff member and shift, so a staffer can correct a report without creating duplicate field evidence. Required skills act as a hard gate, while staff skills support softer ranking logic. Store, staff, and check-in coordinates stay optional so the app can run before all location data is cleaned or before a staffer grants browser location permission. Notification records can stand alone because some operational messages may not map perfectly to one staff/shift pair.

Schema explorerShows the final record map or ERD annotation for campaign, shift, staff, assignment, and report records.

Admin data screenShows campaign, store, staff, and equipment management screens once CRUD copy is settled.

Why This Matters for D2C

This data model gives D2C a path from roster operations to client reporting and field control. A client has campaigns. Campaigns create shifts at stores. Staff are invited or directly assigned to those shifts, respond to the work, receive briefing links, optionally check in with location, and later submit reports. Those reports aggregate into client-facing metrics and exports.

That path is deliberately narrow, but it is the right spine for later ERP, CRM, payroll, file, equipment, and route-planning depth. The latest model already includes hourly rates and equipment records; the app now has admin pages for payroll estimates and day-route summaries, plus a JSON export endpoint for broader integration handoff.