Domains & subsystems

One-page overview of the Ethos Building Services database — a SQL Server backing a building-insurance-repair field-service / job-management platform. Object-level detail lives in Relationships and Object Usage Report; per-table column descriptions live on the data-dictionary pages.

Naming Conventions

Prefix / suffix	Meaning	Examples
prime*	Data synced from the Prime Ecosystem external job-management system	primeJobs, primeContacts, primeAppointments, primeEstimates
primeExport*	Denormalised report extracts built from Prime data	primeExportJobWIPList, primeExportConversionReport
eth* / Eth*	Ethos-built extensions (booking, WIP, KPI, AI, task queues, forms)	ethBooking, ethWIPJobHistory, EthPeerReviewQueue
endata*	Endata integration (cost centres, locations)	endataCostCentres, endataLocations
makesafe*	Emergency make-safe call handling	makesafePhoneCall, makesafeSMSMessage
optimoRoute*	OptimoRoute route-planning integration	optimoRouteOrder, optimoRoutePlan
clicksend* / elevenLabs* / airtop* / n8n*	ClickSend SMS, ElevenLabs voice, Airtop browser sessions, n8n workflow logging	clicksendSMSReply, elevenLabsConversations, n8nExecutions
v* / vw* / V* views	Reporting views: vEth*, vPrime*, vWIP*/vwWIP*, vMakeSafe*, vOptimoRoute*, vReportFilter* (dropdown filter sources), *Objectivedb (Objective DB exports)	vEthKpiActiveJobs, vwPrimeAppointmentReport, vWIPUpdateByWeek
sp* procs	Stored procedures, grouped by domain: spPrime*, spEth*, spMakesafe*, spOptimoRoute*, spEndata*, spN8n*, spClickSend*, spElevenLabs*, spMCP*	spPrimeUpsertJob, spEthBookingCreate
fn* functions	Scalar helpers	fnSanitizeForJsonText, FormatAuPhone, CleanSMSBody
*_stage	Staging tables for imports	primeExportsEndataScopeImport_stage
*_Deprecated (also misspelt _Depcrecated / _Depcreated)	Retired objects kept in the export	ethBookingEstimator_Deprecated, spEthTaskGetNext_Depcrecated

Major Subsystems

Prime Ecosystem job data (prime*)

Mirror of the external Prime Ecosystem system, maintained by spPrimeUpsert* procs (~70 of them). Core entities: primeJobs, primeContacts (+ locations, trades, regions, licences, relationships), primeUsers, primeStatuses / primeStatusHistories_Job, primeAppointments / primeAppointmentType, primeEstimates (+ categories/items, and primeLockedEstimate* point-in-time copies), primeAllocations, primeWorkOrders, primeMilestone*, primeSchedule*, primeAttachment*, primeHistory*, primeSiteForm*, accounts receivable/payable invoice tables. primeExport* tables hold prepared report extracts.

Sync & task engine (ethSync*, ethTask*, ethJobQueue)

Drives the Prime mirror: ethSyncQueue / ethSyncProgress / ethSyncSchedule with spEthSyncQueueUpsert, spEthSyncGetNextDates, spEthQueueMissingJobs; a generic task queue (ethTask, ethTaskLock, spEthTaskGetNext, spEthTaskComplete) used by external workers, plus n8n workflow monitoring (n8nExecutions, spN8nQueueEscalationFailedExecutions, spN8nGetPrimeRateLimitPercent).

Booking & scheduling (ethBooking*, ethAppointment*, ethCalendar)

Customer appointment booking: ethBooking, ethBookingSlot, ethBookingDaySlot, ethBookingConfig, ethBookingOffice, ethBookingService, estimator skills (ethBookingEstimatorSkill, ethBookingAppointmentSkill) and drive-distance tables (ethBookingEstimatorJobDistance, ethBookingJobJobDistance). ~50 spEthBooking* procs handle availability (spEthBookingGetAvailability*), lifecycle (spEthBookingCreate, ...SetOnBook/OnCancel/OnCreate, spEthBookingSetToExpired) and SMS notifications (spEthBookingSMSOnBook). Appointment definitions live in ethAppointmentDefinition / ethAppointmentDefinitionUser / ethAppointmentInstance; ethCalendar + ethPublicHoliday supply business-day logic.

WIP tracking (ethWIP*, vWIP* / vwWIP*, wipStatus)

Weekly work-in-progress updates per job: ethWIPJobHistory (+ Contacts, WorkItems), ethWIPJobSyncHistory, ethWIPJobWeeklySnapshot (captured by spEthWIPCaptureJobWeeklySnapshot), ethWIPJobHidden, wipStatus / wipTextInstructions. Reporting via vWIPUpdateByWeek, vWIPUpdateBySupervisorandWeek, vWIPApprovedCurrentWeekBySupervisor, vwWIPSkippedJobReasons. Client emails queue through spEthWIPJobEmailQueue / spEthWIPJobClientNoteQueue and ethEmailMessage.

KPI & reporting (ethKpi*, vEthKpi*, vReportFilter*, primeExport*)

KPI engine: ethKpiMeasures, ethKpiQueue (processed by spEthProcessKpiQueue), ethKpiResults / ethKpiResultsActive(All), ethKpiJobInstance / ethKpiJobSummary / ethKpiJobTag, role/supervisor lookups. Views vEthKpiActiveJobs, vEthKpiClosedJobs, vEthKPIResultsClosed. vReportFilter* views feed report dropdowns; *Objectivedb views/procs and vPrimeSOM_Report_* / vprimeFinancialschedule_* serve executive/financial reporting; PB_DateDim / vPB_JobDim support Power BI.

AI & automation (ethAI*, ethChatQuestion, ethStagehand*, airtopSession)

ethAIPrimeJobSummary stores AI-generated job summaries (queued via spEthAIPrimeUpsertJobSummaryQueue, hashed with spEthAIPrimeGetJobHash, served by spEthAIGetPrimeJobSummariesPaged). spMCPGetPrimeJob / spMCPSearchTrades back MCP tooling; ethStagehandTasks / ethStagehandProcessLogs and airtopSession support browser automation.

Integrations

• OptimoRoute (route planning): optimoRouteOrder, optimoRoutePlan(+Log/OrderLog), optimoRouteDriverDay, OptimoRouteLog; ~50 spOptimoRoute* procs manage order/plan queues and completions; cutoff logic in fnOptimoRoutePlanningCutoffUtc / fnOptimoRouteSearchCutOff.
• ClickSend SMS: clicksendConversations, clicksendSMSReply; spClickSendInsert*, spClickSendGetLastMFABody.
• ElevenLabs voice: elevenLabsConversations; spElevenLabs* procs for conversation init/status/audio queue.
• Endata (cost centres / scope imports): endataCostCentres, endataCostCentreDescriptions, endataLocations; spEndataSyncLocations, spSyncPrimeExportsEndataScopeImport via primeExportsEndataScopeImport_stage.
• Scope import pipeline: spPrimeScope_MergeCSV_ToStage → spPrimeScope_UpsertStageRowToOrigin with hash-based change detection (spPrimeScope_MergeRow_Hash) and staleness marking.

Makesafe (emergency call-outs)

After-hours make-safe intake and escalation: makesafePhoneCall (+ history, trades, locations), makesafeMessage, makesafeSMSMessage, makesafeUser / makesafeUserSchedule (on-call rosters), makesafeEscalationSettings, makesafeEventLog, makesafeSharingCode, makesafeSiteOptions. ~100 spMakesafe* procs cover intake (spMakesafeInsertFormPhoneCall), assignment, SMS escalation queues (spMakesafeQueueEscalation*, spMakesafeQueueOnCall*), and linkage back to Prime jobs (spMakesafeUpsertPhoneCallFromPrimeJob). Views: vMakeSafePhonecalls.

Peer review, forms & compliance

EthPeerReviewQueue / EthPeerReviewStatus / EthPeerReviewedErrors with spEthPopulatePeerReviewStatus, spPrimePeerReviewJobSummary, spPrimePeerReviewEstimatedetails. Public forms: ethFormTriage, ethFormMakeSafeSLA, ethFormDepartureNotice, secured by ethFormSharingCode (spEthFormSharingCodeInsert / ...MarkUsed). Compliance: EthHOWCompliance (home-owner warranty; see vPrimeHomeOwnerWarrantyCheck), vprimeSWMSreport.

MFA & security

EthMFAAsAService with spEthMFAAsAServiceUpsert / spEthMFAAsAServiceGetLatest (SMS codes read back via spClickSendGetLastMFABody); ApiTokens managed by spPrimeUpsertApiToken / spEthGetLastApiToken / spEthClearApiTokens.

Data Flow

PrimeConnect API ──(n8n workers + ethSyncQueue/ethTask)──► spPrimeUpsert* ──► prime* tables
CSV / Endata scope files ──► *_stage tables ──► spPrimeScope_* / spSync* ──► primeExports* / endata*
prime* tables ──► eth* extensions (booking, WIP history, KPI queues, AI summaries, makesafe links)
eth* + prime* ──► reporting views (vEth*, vPrime*, vWIP*, vReportFilter*, *Objectivedb) ──► dashboards / Power BI / Objective DB

1. Ingest — n8n flows call Prime Ecosystem, driven by ethSyncQueue / ethTask scheduling; spPrimeUpsert* procs land the data in prime* mirror tables. File-based feeds land in _stage tables first.
2. Extend — Ethos logic builds on the mirror: bookings become primeAppointments, WIP history snapshots weekly, KPI queues compute results, AI summaries are generated per job hash.
3. Report — views aggregate for WIP dashboards, KPI reports, financial schedules, and exports to Objective DB / Power BI.

See Relationships for object-to-object dependency mapping and Object Usage Report for which procs/views touch each table.