SculptFlow system design

1. Overview

SculptFlow is a multi-tenant SaaS for plastic surgery clinics. A prospective patient (a lead) messages a clinic on WhatsApp or Telegram. An AI agent, orchestrated in n8n, answers from the clinic's Knowledge Base, qualifies the lead and books, moves or cancels consultations through SculptFlow's own tool endpoints. Staff watch every conversation live in the Inbox and can take over at any moment. Clinics also run WhatsApp template campaigns, mainly to reactivate old leads.

2. High-level design

2.1 System context

One ASP.NET Core process serves the dashboard (server-rendered Razor Pages plus small vanilla JS files), a JSON API, a SignalR hub and two in-process background workers. PostgreSQL (Supabase) is the single source of truth. n8n holds no business state: it runs the AI agent and two helper workflows, and talks to the app only over HTTP.

2.2 Multi-tenancy

• Tenant = clinic. Every business table carries clinic_id (FK to clinics, on delete cascade), and every service method takes a clinicId and filters on it.
• Dashboard requests never trust a clinic id from the browser. ICurrentClinicContext resolves it from the signed-in Identity user through clinic_users (one active membership per user, enforced by the partial unique index ux_clinic_users_user_active).
• Webhooks resolve the clinic from the stored connection: Meta events by phone_number_id then WABA id on channel_integrations; Telegram by the connectionId in the URL plus its secret token.
• n8n tool calls pass clinicId explicitly (query or body) and are trusted because of the shared X-Ingest-Key. This is the weakest link; see §10.
• Registration always creates a brand-new, empty clinic plus its first clinic_users row in one transaction (ClinicRegistrationService). There is no invite or join flow yet, and no roles: every member of a clinic has full access.
• SignalR groups are clinic:{id} (joined on connect from the server-resolved clinic) and conversation:{id} (joined only after an ownership check).

2.3 Trust zones and authentication

OAuth state for calendar and TikTok connects is protected with ASP.NET Core DataProtection. POST /api/leads takes the clinic id from the body, so it needs the ingest key ([RequireIngestKey], 401 without it). Sign-up POSTs (/Account/Register, /Account/CompleteGoogleSignup) are rate limited per client IP (Accounts:SignupsPerIpPerHour, 429 over the limit; IP from X-Forwarded-For), and Accounts:SignupEnabled = false closes self-signup altogether.

2.4 Deployment and runtime

• Multi-stage Dockerfile (SDK 10.0 build, aspnet 10.0 runtime). Listens on plain HTTP ${PORT:-8080}; Render terminates TLS. Deployed from main to https://sculptflowapp.onrender.com. No CI workflow files are in the repo.
• UseForwardedHeaders trusts X-Forwarded-Proto so redirect URIs and secure cookies see https.
• Swagger UI at /swagger in Development only. HSTS and /Error handler outside Development.
• Schema is applied by hand: psql -f Database/schema.sql (idempotent, append-only blocks), optional Database/seed.sql. EF Core migrations are not used.
• Secrets: dotnet user-secrets locally, Render env vars (Section__Key) in production. Properties/launchSettings.json on dev machines holds real keys and must never be committed.
• Single instance assumed: the scrape and benchmark queues are in-memory Channel<Guid>s, recovered from DB state on startup. SignalR has no backplane. (Billing is the exception: its correctness rests on database row locks and unique keys, so two instances running the billing worker can't double-charge.)

2.5 Design principles visible in the code

1. PostgreSQL is authoritative; SignalR only says "something changed". Every client handler re-fetches over REST.
2. Business logic lives in Business/, shared by Razor PageModels and API controllers. Controllers and PageModels hold no logic; database access belongs in Persistence/ repositories (§4.1).
3. The backend decides; the AI asks. Mode checks, slot validity, duplicate-booking guard and AI eligibility are decided in .NET, not in n8n.
4. Idempotency at the database. Partial unique indexes on external ids (messages, leads, Telegram threads, health events) make webhook retries safe.
5. Side effects after commit, never throwing. Notifications, calendar sync and AI triggers run after the main write succeeds and swallow their own failures.
6. Time: every instant is timestamptz (UTC). Activity shows in the viewer's timezone; appointments default to clinic time with a Clinic/My time switch; availability is entered and stored clinic-local.
7. Tolerate provider vocabularies. Meta template and health statuses are free text (CHECK constraints dropped) so unknown values never fail a webhook.
8. Money moves only through the ledger. Every balance change is an append-only ledger row written in the same transaction, under the clinic's billing-account row lock, with an idempotency key (§2.6).

2.6 Subscriptions and usage billing

An internal module (the Billing feature folders under Business/, Entities/ and Persistence/, tables in the Postgres schema billing.*, guide in docs/billing.md) built around three separate questions that are never collapsed into one:

1. Platform subscription: what the clinic pays SculptFlow for access. The plan decides features and limits (entitlements) and may grant a monetary included credit each period.
2. Provider billing responsibility: who pays the upstream provider (Meta, an SMS operator…), set per connected channel account: customer pays directly, SculptFlow pays, customer pays an external provider, or no provider usage fee. Two WhatsApp accounts can differ.
3. SculptFlow usage billing: whether SculptFlow charges the clinic for the usage. By default only when SculptFlow pays the provider; an account can also carry a SculptFlow usage fee, or SculptFlow can absorb the cost.

Every usage is a billable event and is recorded (volumes, provider cost where known). Only what SculptFlow charges is rated, reserved, settled and paid, included credit first and then the prepaid wallet, through the ledger. There are no per-channel quotas. Everything is off while the Billing / Enabled setting is false (on by default; switch it on the admin portal's Configuration page).

• Billable event, not message. A typed event (whatsapp_marketing_message, telegram_message, later sms_segment, voice_minute, ai_token…) with a quantity, plus who pays the provider and whether SculptFlow charges. New types need only rates, and only where SculptFlow charges.
• Channel policies own channel rules (IChannelBillingPolicy: which event, how many units, when it counts); accounts own commercial arrangements (ProviderBillingService: account override → configured default per channel/provider → built-in default).
• Usage records keep money and provider apart: charge_status (reserved, settled, released, failed, not_charged) is SculptFlow's money; provider_outcome (pending, billable, not_billable) is what happened at the provider.
• Rates hold provider cost and client rate as independent numbers, optionally narrowed by country, operator, provider and responsibility, versioned by effective period; never edited, never backdated, so history is never re-priced. A customer-paid account is only ever charged a rate set for its own responsibility (a usage fee), never the platform rate by fallback.
• Prepaid. Spendable = wallet + included credit − reserved. The wallet and credit only finance what the clinic owes SculptFlow. Only a settlement that costs more than its estimate can take the wallet below zero.
• Correctness. Every money operation runs in one transaction holding the clinic's billing.accounts row lock, checks idempotency after taking the lock, and writes the ledger; unique keys, CHECKs, an exclusion constraint on rate versions and triggers that block ledger/rate/final-usage edits back it up. Cached balances must equal the ledger sums (reconciliation endpoint).
• Postpaid later fits as a credit limit on the billing account (added to the spendable formula) plus invoices built from the ledger; nothing else has to change.

3. Frontend

Server-rendered ASP.NET Core Razor Pages with two layouts (Pages/Shared/_Layout.cshtml for signed-in staff, _AuthLayout.cshtml for sign-in and error pages), the vendored "Aurora" UI kit plus an app stylesheet, and one small vanilla JS file per interactive page. There is no bundler, no npm and no frontend framework. Razor runtime compilation is off, so .cshtml changes need a rebuild. How screens look is governed by docs/UI_GUIDE.md (§0.4 = how this app applies it).

3.1 Page map

Two data patterns coexist: server-rendered pages whose PageModel calls services directly (forms post back with OnPost* handlers), and JS-driven pages that render a shell and then call /api/** with the session cookie.

Shared partials: _Sidebar, _Header, _ThemeScript (pre-paint theme), _Messages (success toast / error alert), _EmptyState, _NoClinic, _GoogleButton, _StatusHelp (status glossary overlay), _TimeViewSwitch (Clinic time / My time). C# view helpers in Common/Helpers: ChannelIconHelper, FilterLabelHelper, LocalTimeHelper, StatusBadgeHelper, UiTextHelper, UrlDisplayHelper, ViewerTimeZone; view models in Entities/Dtos/Ui.

3.2 JavaScript modules

Every file is an IIFE-style script loaded by its page; none depends on another except through globals noted below. Loaded by the layout: local-time.js (head) and aurora.js on every page; for signed-in staff also the SignalR client (@microsoft/signalr@8.0.0 from jsDelivr, once: in <head> on pages that set ViewData["SignalRFirst"] because their own scripts use it while loading, at the end otherwise), notifications.js and sidebar.js.

3.3 CSS, theming and time display

• Styles load as fonts.css → aurora.css → site.css. aurora.css / aurora.js are the vendored Aurora kit, shared with the admin portal and extracted from docs/UI_GUIDE.md's appendices (never edited by hand). site.css is the app layer: older token and class names mapped onto the kit's tokens (the page scripts still build markup with them), the brand calibration (teal, calm, AA) and per-screen styles.
• Fonts are self-hosted in wwwroot/fonts: Inter and a Material Symbols subset holding only the icons in use, rebuilt with node docs/tools/icon-font.js after adding an icon.
• Light and dark: the theme follows the OS until the user picks one; the choice is stored in localStorage['client.themeMode'] and applied as <html data-theme> by _ThemeScript before the first paint. The collapsed sidebar rail is localStorage['client.sidebarCollapsed'] (<html class="au-side-collapsed">). The old sf-theme / sf-sidebar keys are read once as a fallback.
• Brand: wwwroot/logo.svg (also the favicon), teal (<html data-brand="teal">), dark sidebar. User-visible name is MySculptFlow.
• Time rule: UTC in the DB; activity in viewer time; appointments in clinic time with a switch; availability clinic-local. Server-side day boundaries use the sf-tz cookie via ViewerTimeZone.

4. Backend

4.1 Layers and folders

Agreed code layout (standing rule in CLAUDE.md; the admin portal uses the same one). One top-level type per file, namespace = folder path. A request flows Controller or PageModel → Service → Repository; controllers and PageModels hold no logic and never touch the DbContext, and only repositories query the database. Services decide when to save through IUnitOfWork; billing money operations use IBillingUnitOfWork (their own context).

4.2 Service catalog

All scoped per request unless noted. Each interface file carries the authoritative behavioral notes.

4.3 API surface

Tags: session logged-in staff, ingest X-Ingest-Key, open provider-authenticated or anonymous.

Dashboard APIs (session)

Billing admin API (platform admin key; used by the SculptFlowAdmin portal's Billing pages)

* requires an Idempotency-Key header; repeating a request with the same key returns the first result.

Automation cleanup API (platform admin key; used by the SculptFlowAutomation tester)

The separate SculptFlowAutomation app (own folder and repo next to this one) tests the system end to end: each run registers a throwaway clinic through /Account/Register, exercises the APIs and pages as that clinic, then deletes it here. AutomationAdminController only acts on an automation clinic: name starts with [Automation] AND every user's email ends with @sculptflow-automation.test; anything else gets 409 and nothing changes.

Configuration API (platform admin key; used by the SculptFlowAdmin portal's Configuration page)

SettingsAdminController → SettingsService. Only settings declared in ConfigDefaults, only values that fit their type and range (400); X-Admin-Actor is stored as updated_by.

Cache (CacheAdminController): the in-process cache behind ICacheManager. Keys contain :, so they go in the query string. Clear-all also reloads the settings snapshot. Per instance: with more than one app instance a remove reaches only the instance that answers.

API timings (ApiTimingsAdminController, ApiTimingAdminService): read-only per-route summary of activity.api_route_stats (§4.6) for the portal's API timings page. Totals cover every route; routes holds the first take after sorting (always descending). p95Ms is the request-weighted average of the per-minute p95s (exact percentiles can't be combined); avgMs and maxMs are exact.

Error log (AppLogsAdminController, AppLogAdminService): read-only activity.app_logs (§4.6) for the portal's Errors page and Overview tile. level is a minimum: warning (default, everything) · error (errors + criticals) · critical; hours 1–8760 (default 24). Occurrence counts sum repeat_count. Unknown level or sort is 400, an unknown id 404.

Message trace (MessageTracesAdminController, MessageTraceAdminService, reads in MessageTraceAdminRepository): read-only, for the portal's Message traces pages. A timeline merges the trace's steps (§4.6), the messages created in it (crm.messages.trace_id) or named by its steps with their delivery statuses, their charges (billing.usage_records), the conversations' activity.events from a minute before the trace to 10 minutes after it, and its saved logs. Unknown traces 404, an empty search 400.

Admin portal APIs: everything the SculptFlowAdmin portal reads or changes about clinics goes through these (the portal has no access to the main tables). Reads come from the cross-clinic repositories in Persistence/Repositories/PlatformAdmin; writes go through the same services a clinic uses (disconnects at the provider, calendar sync, plan limits, cache clearing). Writes answer {clinicId} so the portal can file its audit entry. Details never include secrets (only hasAccessToken). Lists take page and answer {items, total, page, pageSize}.

AI tool surface for n8n (ingest)

Other server-to-server and provider endpoints

4.4 SignalR

One hub, InboxHub at /hubs/inbox, [Authorize]. Server-to-client only, all sent to clinic:{id} through IInboxNotifier. Client methods: JoinConversation(id) (ownership checked), LeaveConversation(id).

4.5 Background workers

The billing worker and the campaign sender are the scheduled business jobs (the config refresh only reloads settings). The campaign sender is what makes "Schedule" on the Create Campaign page actually send, and it finishes a running campaign after the dashboard sent its first batch.

4.6 Cross-cutting concerns

• Audit/analytics: events rows via IEventLogger, typed by EventTypes constants (lead_created, consultation_booked, human_handoff, ai_message_sent, conversation_closed, conversation_reopened, whatsapp_* …).
• Notifications: types NEW_LEAD, HANDOFF, APPOINTMENT_BOOKED / RESCHEDULED / CANCELLED, CAMPAIGN_REPLY, OUTBOUND_MESSAGE_FAILED, INTEGRATION_UNHEALTHY; each row precomputes a link.
• Errors: one exception filter, Controllers/Filters/ApiErrorsAttribute ([ApiErrors] on DashboardApiController, ConversationsController, the platform-admin controllers and the n8n/AI controllers), turns service exceptions into { "error": message }: ArgumentException → 400, KeyNotFoundException → 404, duplicate/overlapping record, closed WhatsApp window, benchmark already running → 409, InvalidOperationException → 422, channel send / Meta Graph / benchmark generator failure → 502, generator not configured → 503; anything else stays 500. Controllers catch only where a response must differ (AI tool results for n8n, the human-mode conflict body, OAuth redirects, the knowledge "embeddings not configured" 503). Provider webhooks don't use it, so they keep answering 500 and the provider retries.
• API timings: Middleware/RequestTimingMiddleware (after routing) times every request by method + matched route template (never the raw path; no match = (unmatched); static files, /hubs and the status-page re-execute are skipped) into the singleton IApiTimingCollector: counts, 5xx / 4xx / slow counts, total, max and a bounded sample (2,000 per route per minute) for the p95. No database write per request; ApiTimingFlushJob writes the minute (§4.5). A request at or over Monitoring:SlowRequestMs (1000) logs a warning Slow request: {Method} {Route} took {ElapsedMs} ms. Monitoring:Enabled switches it all off. Each app instance writes its own rows; readers sum them. Read through /api/platform-admin/api-timings (§4.3).
• Error log: Business/Providers/Logging/DatabaseLoggerProvider is registered as an extra ILoggerProvider, so every warning, error and critical any ILogger writes (including the framework's "unhandled exception" log) is also queued in the singleton IAppLogCollector; no database write on the caller's path, and a logging failure never reaches the caller. Each entry keeps the rendered message, template, exception (type, message, full text capped at 8,000 chars), a fingerprint (category + template + exception type + top stack frame) that groups one problem, the W3C trace id (Activity.Current), method + route template (never the raw path), clinic/conversation/message ids found in the logged values or scopes, and the other values as properties with names containing token/secret/password/authorization/apikey/signature/cookie/credential masked. Per fingerprint at most ErrorLog:MaxPerFingerprintPerMinute (20) rows per minute; the rest of that minute becomes one row with repeat_count. Up to 5,000 entries wait in memory; beyond that they are dropped and one row says how many. ErrorLog:Enabled, MinimumLevel (Warning) and ExcludedCategories are copied in by AppLogFlushJob (§4.5) every flush, because the logging path can't depend on ConfigManager (it logs itself). [ApiErrors] now logs the 502/503 answers it maps (provider failures) as errors so they reach the log; 4xx answers are not logged. Read by /api/platform-admin/logs (§4.6) and the portal's Errors page.
• Message trace: the singleton IMessageTracer records each step a message takes under the request's W3C trace id (Activity.Current; TraceContext.StartNew gives each campaign recipient's send its own trace). Steps (Common/Statics/TraceSteps): webhook.received / rejected (signature, bad JSON, unknown Telegram connection or secret) / clinic_unresolved / ignored (MetaWebhookProcessor, WhatsAppWebhookController, TelegramWebhookProcessor; a status-only Meta batch is not a step); message.saved / deduplicated, conversation.reopened / mode_changed (with reason), inbox.notified, bot.skipped (why: conversation_with_staff, message_type_not_supported, plan_without_ai_agent, reopened_to_staff, n8n_not_configured) (MessageService.IngestAsync); bot.triggered / trigger_failed (AiTriggerNotifier, which also puts traceparent in the payload); bot.tool_called for every /api/ai/* call ([TraceBotTool] on AiController); send.requested / blocked (billing or plan) / rejected_mode / provider_accepted / provider_failed with timings (MessageService send paths). Every message row saved gets trace_id. Delivery statuses, charges and business events are not steps; the trace view reads them. Details carry ids, codes and reasons only, never message text or phones. X-N8n-Execution-Id on n8n's calls is stored. MessageTrace:Enabled switches it off. Read by /api/platform-admin/message-traces (§4.6).
• Billing logs: structured Billing: log lines for reservations, settlements, releases, missing rates, insufficient balance, duplicates, renewals and wallet adjustments; no phone numbers or message bodies.
• HTTP clients: typed clients via AddHttpClient. Telegram and the benchmark generator use RemoveAllLoggers() because their URLs carry secrets. The crawler client disables redirects, cookies and proxy, and routes every connect through SsrfGuard.ConnectAsync.
• Testing: PlasticSurgery.Tests (xUnit) covers billing: pure rules (rating, phone country, WhatsApp policy, entitlements) and database tests that need SCULPTFLOW_TEST_DB pointing at a throwaway local PostgreSQL (each run creates and drops its own database from schema.sql; without it those tests are skipped; remote hosts are refused). Nothing else is covered by automated tests yet. AiTestController gives n8n canned outcomes.

5. Key flows (low-level)

5.1 Inbound WhatsApp message to AI reply

1. Meta posts raw JSON to POST /api/integrations/whatsapp/webhook.
2. MetaWebhookParser splits it into ParsedMetaEvents (customer message, business-app echo, status, template, health, history, app-state, unknown). Interactive/button replies become clean text plus SelectedValue.
3. MetaWebhookProcessor resolves the clinic per event from channel_integrations (phone_number_id, else WABA id) and dispatches to the handler.
4. CustomerMessageHandler → LeadService.GetOrCreateByPhoneAsync → conversation get-or-create → MessageService.IngestAsync: idempotent on (clinic, channel, external_message_id), stores the message, opens the 24h window, unread, campaign reply attribution, events, notifications, NewMessage.
5. The conversation's mode decides who answers: a new conversation starts in human when the plan lacks ai_agent or the clinic turned the bot off for new conversations; a patient reopening a closed AI conversation moves it to human when the clinic sends reopened conversations to staff (or the plan lacks ai_agent). See InboxSettingsService.
6. If the event is AI-eligible (customer message, conversation mode ai, not a duplicate) the controller calls AiTriggerNotifier → N8n:AiWebhookUrl with {clinicId, conversationId, leadId, messageId, channel, messageType, messageText, selectedValue, traceparent}. Never throws.
7. Meta always gets HTTP 200.
8. n8n AI agent calls /api/ai/* tools, then POST /api/conversations/{id}/messages/send?clinicId= with sender:"ai".
9. MessageService.SendAiReplyAsync re-reads the mode; if a human took over it returns 409 and nothing is sent. Otherwise WhatsAppChannelSender sends through the Graph API and the message is stored with origin=ai.

5.2 Staff reply, echo and status

• Staff reply from the Inbox: POST …/messages/send (session) → MessageService.SendAsync → channel sender (throws before persisting if it cannot send; WhatsApp requires an open 24h window) → store origin=dashboard → mode flips to human.
• Closed window: 409; the Inbox offers send-template with an approved template.
• Coexistence echo: a reply typed in the WhatsApp Business phone app arrives as a BusinessAppEcho, is stored with origin=whatsapp_business_app, flips the conversation to human, and never triggers AI.
• Delivery status: MessageStatusHandler updates delivery_status and the matching delivered_at/read_at/failed_at, mirrors campaign recipient status, raises OUTBOUND_MESSAGE_FAILED on failure, broadcasts MessageStatusUpdated.

5.3 Conversation modes

mode ∈ {ai, human, approval}. Switches to human: staff Take Over, a staff message or template, a business-app echo, the AI's handoff_to_human, a campaign template send, a customer message while the plan lacks ai_agent, a patient reopening a conversation when the clinic's reopened switch is off. A new conversation starts in human when the plan or the clinic's new-conversation switch says the bot doesn't answer. Only Return to AI switches back (refused without ai_agent; the Inbox hides the button). approval exists in the schema but is never set automatically.

5.4 Telegram

1. Staff paste a BotFather token on Messaging Integrations → POST /api/channel-integrations/telegram/connect → getMe, then setWebhook to {App:PublicBaseUrl}/api/integrations/telegram/webhook/{channel_integrations.id} with a random secret_token.
2. Telegram posts an update; the controller checks the row is telegram + connected (else 404) and the secret header (else 403).
3. TelegramWebhookProcessor → lead by external_lead_id (no phone) → conversation keyed by external_thread_id = chat.id → IngestAsync with external id chatId:messageId, origin=telegram_customer.
4. Same AI trigger as WhatsApp with channel:"telegram". Outbound goes through TelegramChannelSender → sendMessage. No 24h window; templates are WhatsApp-only.
5. Voice notes arrive as message_type=audio (Telegram's file_id kept in metadata_json). They are saved and played in the Inbox through MessageMediaService → TelegramVoiceMediaSource (getFile + download with the clinic's own bot token); they are not yet forwarded to n8n (only text/interactive are AI-eligible).

5.5 Booking (AI and dashboard)

1. get_available_slots → AvailabilityService.GetSlotsAsync builds slots in the clinic's timezone and returns the lead's booking context.
2. schedule_consultation → AppointmentService.ScheduleAsync: no upcoming appointment → book; one upcoming and ConfirmReplaceExisting → reschedule; otherwise CONFIRMATION_REQUIRED.
3. Every mutation takes pg_advisory_xact_lock(hashtext(clinicId)), re-runs CheckSlotAsync (excluding the appointment being moved) and the one-upcoming-per-lead guard.
4. After commit: event row, APPOINTMENT_* notification, AppointmentChanged, and CalendarIntegrationService.TriggerAppointmentSyncAsync.

5.6 Campaigns

1. Create (/Campaigns/Create): pick an approved template, fill variables, choose audience all_eligible | reactivation_no_consultation | custom (filters JSON or manual selection). Recipients are snapshotted; non-contactable leads are skipped with a reason.
2. Send: POST /api/campaigns/{id}/send moves to running, queues pending recipients and processes the first batch (20); CampaignSenderWorker sends the rest (process-batch still works by hand). A scheduled campaign is started by the worker once scheduled_at passes.
3. Stop: POST /api/campaigns/{id}/stop (Details page "Stop campaign") moves a scheduled or running campaign to paused, shown as "Stopped", with a conditional set-based update. Nothing more is sent, including the rest of a batch in progress; waiting recipients stay pending/queued. "Resume sending" is send again (keeps the first started_at); Cancel ends it. A campaign stopped or cancelled during its last batch is never marked completed.
4. Each recipient: conversation get-or-create → MessageService.SendCampaignTemplateAsync (origin=campaign, links campaign ids) → mode flips to human.
5. Attribution: status webhooks move recipients through sent → delivered → read; the first inbound reply after sent_at sets replied_at and CAMPAIGN_REPLY; bookings set booked_at/appointment_id.

5.7 Calendar sync (one way, SculptFlow → Google/Outlook)

1. Connect: /calendar-oauth/{provider}/connect marks the row pending and redirects to consent; /callback exchanges the code, stores access/refresh tokens on calendar_integrations, lists calendars into calendar_integration_calendars.
2. Staff select a calendar and enable sync.
3. On every committed appointment change, the service refreshes the token if needed, upserts appointment_calendar_syncs (one row per appointment × integration, new last_request_id, last_operation) and posts CalendarSyncTriggerPayload to N8n:CalendarSyncWebhookUrl (access token, calendar id, operation create/update/cancel, request id, external event id, start/end with offset, clinic timezone).
4. n8n makes the provider call and posts {clinicId, appointmentId, calendarIntegrationId, requestId, success, externalEventId, errorMessage} to /api/calendar-integrations/sync-callback. A stale requestId is ignored. Failures mark the integration unhealthy.

5.8 Knowledge Base ingestion and search

• Manual / upload: text (or extracted text, max 5 MB file, 250k chars) → chunk by the clinic's settings (tokens ≈ chars ÷ 4) → embed all chunks first → transaction replaces knowledge_chunks for that document.
• Website: create source → pending run enqueued → worker crawls breadth-first (robots.txt, SSRF-checked fetch, same-site links, max pages/depth/concurrency from Knowledge:WebScraping) → analyze (canonicals, boilerplate stripping by block hash, SHA-256 of normalized text, duplicates) → apply (unchanged pages skip embedding, changed pages rebuild only their chunks, new pages create source_type='website' documents, missing pages deactivate after confirmed absence).
• Search: embed the query with the clinic's model, exact cosine scan over the clinic's active documents' chunks, take top-K, drop anything under minimum_similarity. No ANN index on purpose.

5.9 Retrieval benchmark

1. Driven from the admin portal (Search benchmark page, clinic picked in its header) through /api/platform-admin/knowledge-benchmark/clinics/{clinicId}; clinic staff have no benchmark page or API. Generate: the app creates a pending generation row, samples 20 active chunks and posts them with the generationId to N8n:KnowledgeBenchmarkWebhookUrl; returns 202.
2. n8n's LLM writes one question per chunk and calls back POST /api/knowledge/benchmark/generations/{id}/questions. Every id is validated against the chunks that generation sent; the clinic comes from the generation row. Cancelled generations refuse late callbacks.
3. Run: a pending run is queued; the worker searches each non-stale case via SearchRankedAsync and records chunk/document ranks, Top-1/3/5, MRR, latency and a settings snapshot.

5.10 Sign up and sign in

• Register: full name, clinic name, email, password → new clinic (unique slug) + user + membership in one transaction → signed in → /dashboard. With Accounts:SignupEnabled off, Register shows "sign-ups are closed" instead of the form, Login hides its "Create a clinic account" link, a new Google identity is sent back to Login (externalError=signup_closed), and both register methods refuse before creating anything.
• Google: /auth/google/start → Google → /auth/google/callback: existing linked login or verified matching email signs in; a new email goes to /Account/CompleteGoogleSignup to collect the clinic name. Errors return to Login with externalError=cancelled|failed.

5.11 Billing an outbound WhatsApp template (usage)

1. MessageService (Inbox template, campaign recipient, or a billed free-form message) checks the subscription (EnsureCanSendMessages; expired/cancelled/no plan → 422) and generates the message id it will save.
2. WhatsAppBillingPolicy describes the message: template category → whatsapp_{category}_message (utility inside an open 24h window and free-form replies are free by default), quantity 1, destination country from the lead's number, provider meta, settle on delivery.
3. ProviderBillingService resolves the clinic's WhatsApp account: who pays the provider (account override, else the default — platform_funded for WhatsApp) and whether SculptFlow charges usage.
4. SculptFlow doesn't charge (customer pays Meta directly, external provider, no fee): BillingService.Reserve only records the usage as not_charged, with Meta's cost if a rate knows it. No rate is required, nothing is held, the send goes ahead even with an empty wallet, and the outcome steps below only update provider_outcome.
5. SculptFlow charges: BillingService.Reserve (own transaction, clinic account locked) rates it (clinic card → plan card → default, rate effective now, most specific country/operator/provider/arrangement; a customer-paid account only matches a fee rate for its own arrangement), refuses if no rate or spendable < price (recorded as a failed usage row; the send is refused with 422 before the provider is called), else holds it and inserts the usage record keyed whatsapp:message:{messageId}.
6. The provider call runs. If it throws, the hold is released (send_failed) and the error goes back as before.
7. The message row is saved with the same id. Later, the provider's status callback reaches MessageService.HandleStatusUpdateAsync → MessageBillingService: delivered/read → settle (release the hold, charge included credit first, then wallet, one ledger row per balance), failed → release. Repeated or out-of-order callbacks are no-ops; settle-after-release is refused and logged.
8. If no outcome arrives within Billing:ReservationTimeoutHours, the maintenance worker settles it when the message row shows it was delivered (callback processed but billing failed) and releases it otherwise.

5.12 Subscription start, renewal and expiry

1. Start / change plan (admin API, or the signup plan): wallet must cover the price while keeping existing holds covered (unless the first period is waived); old included credit expires, the plan's credit is granted, the price is charged (a 0 charge row for free/trial periods), the period runs from now. Same idempotency key → no second charge.
2. Renewal (worker, when current_period_end has passed): same steps for the next contiguous period. If the wallet can't pay: past_due, the ended period's credit expires, the plan keeps working for Billing:GracePeriodDays; every worker run retries, so a top-up renews it (from the old period end).
3. Expiry: grace over without payment → expired. Cancel: now, or at period end instead of renewing → cancelled. Expired/cancelled clinics can sign in and see everything, but can't send, run campaigns, use the AI or connect channels until a plan is started again.

6. Integrations

7. n8n (from the app's side)

Every outbound n8n call is best effort: a missing URL logs a warning and returns, a failure logs and is swallowed. The originating operation (webhook ack, appointment change) is never failed by n8n.

AiTriggerPayload (camelCase JSON):

{ "clinicId": "uuid", "conversationId": "uuid", "leadId": "uuid|null", "messageId": "uuid|null",
  "channel": "whatsapp|telegram", "messageType": "text|interactive|button|…", "messageText": "clean visible text",
  "selectedValue": "stable reply id or null",
  "traceparent": "00-{traceId}-{spanId}-01" }

Message trace (n8n side, to set up in the workflow): every HTTP Request node that calls back into the app (the /api/ai/* tools, the AI reply send, handoff) should send the header traceparent: {{ $('Webhook').item.json.body.traceparent }} (the value the trigger carried; the trigger request also carries it as a traceparent header) and X-N8n-Execution-Id: {{ $execution.id }}. ASP.NET Core continues the incoming trace, so the bot's tool calls and its reply land in the customer message's trace, with a link to the n8n execution. Without the header everything still works; the bot's calls just form their own traces.

CalendarSyncTriggerPayload: clinicId, appointmentId, calendarIntegrationId, provider, accessToken, externalCalendarId, operation (create|update|cancel), requestId, externalEventId, scheduledStart, scheduledEnd, timezone, eventTitle, eventDescription, sculptFlowAppointmentLink. n8n must not reinterpret times; offsets are already correct.

8. Database

8.1 Conventions

• One Postgres schema per area (since 2026-10-07): core (clinics, procedures), identity (ASP.NET Identity, clinic_users), crm (leads, conversations, messages), scheduling (appointments, procedure bookings, availability, calendar sync), channels (channel integrations, WhatsApp templates and health, TikTok), marketing (campaigns), knowledge (knowledge base, scraping, benchmarks), activity (events, notifications), billing, config; the admin portal's own tables are in admin. public keeps only set_updated_at() and extensions. SQL always schema-qualifies table names and every ToTable names its schema. The top of schema.sql creates the schemas and moves any table still in public (databases created before the split) with alter table … set schema, which keeps data, indexes, constraints, triggers and foreign keys; on a current database it does nothing. Supabase's Data API only exposes public, so no app table is reachable through it.
• Database/schema.sql is the source of truth: idempotent, append-only blocks (create … if not exists, add column if not exists, guarded do $$ constraint blocks). New changes are appended at the end, never edited in place. ApplicationDbContext is updated by hand to match.
• Keys: uuid with gen_random_uuid(), except Identity (text ids) and knowledge_retrieval_benchmark_generations.id (app-assigned).
• created_at/updated_at on most tables; set_updated_at() trigger maintains updated_at.
• All instants are timestamptz; a guard block at the end converts any stray timestamp column. Only availability time/date columns are zone-less (clinic-local).
• Status columns are varchar with CHECK constraints, except deliberately free-text provider vocabularies (template status/category, health fields, campaign_type, leads.source, knowledge_documents.category).
• Tenant FK clinic_id … on delete cascade everywhere. Tokens (access_token, refresh_token) are plain text today.

8.2 Core relations

8.3 Table catalog (46 tables)

Tenancy and identity

CRM and scheduling

Messaging

Knowledge Base

External accounts

Settings — Postgres schema config (§9)

Subscriptions and billing — Postgres schema billing (§2.6)

8.4 Status vocabularies (CHECK-enforced)

9. Configuration

Names only. Non-secret defaults live in appsettings.json; secrets via user-secrets or Render env vars (__ for :).

Settings in the database (config.settings, read through IConfigManager). Tunable values (limits, defaults, thresholds) are read with IConfigManager.GetInt/GetDecimal/GetBool/GetString(section, key) (Business/Managers/ConfigManager, singleton): the value stored in config.settings for that section and key when there is one, otherwise the constant default declared in Common/Statics/ConfigDefaults. ConfigDefaults is also the list of settings that exist (type, range, description); only those can be stored, and only values that fit. The manager answers from an in-memory copy of the table, loaded by ConfigRefreshJob at startup and every 60 seconds and refreshed right after a change, so a change applies at once without a restart. A stored value that doesn't parse is ignored (default used, warning logged). If the database is unreachable the defaults apply. 56 settings are declared, in sections Availability, Billing (incl. Enabled, Currency, grace period, reservation timeout, maintenance interval, signup plan), Knowledge, WebScraping, Benchmark, Campaigns, Embeddings (base URL, model, dimensions, batch size, and the API key as a secret setting: stored, but the API and the Configuration page only show whether it is set, and it never reaches logs or the audit log), Integrations, Dashboard, Cache and Monitoring (API timings: Enabled, SlowRequestMs, RetentionDays) and ErrorLog (Enabled, MinimumLevel, WarningRetentionDays, ErrorRetentionDays, MaxPerFingerprintPerMinute, ExcludedCategories) and MessageTrace (Enabled, RetentionDays, FailedRetentionDays); each has a typed property on IConfigManager (full list in docs/configuration.md). Database/seed-config.sql stores every default (never overwriting a stored value). Those keys are no longer read from appsettings or environment variables; the table below lists what stays there. Managed from the admin portal's Configuration page through /api/platform-admin/settings (§4.3). Secrets and environment values (below) stay in appsettings / user-secrets / environment variables.

10. Known gaps and risks (from the code)

11. Doc drift notes

Places where older documentation disagrees with the code as of the baseline above. Trust the code.

• PROJECT_HANDOFF.md §9 lists six SignalR events; the code also sends AppointmentChanged and NotificationCreated.
• PROJECT_HANDOFF.md §8 tool table shows /api/ai/appointments/{id}/reschedule|cancel; the real routes are /api/ai/appointments/reschedule and /cancel, and the preferred tools are schedule, cancel, upcoming.
• PROJECT_HANDOFF.md §28 says Google sign-in is not pushed; it is on main (9cd4091, febef3c).
• PROJECT_HANDOFF.md and §20 mention N8n:CalendarConnectWebhookUrl; the code no longer reads it (SculptFlow owns calendar OAuth directly).
• The calendar_integrations comment in schema.sql still says n8n owns the OAuth tokens; a later block in the same file moved tokens into SculptFlow.
• The comment on channel_integrations says Settings → "Channels & Integrations"; the sidebar now says "Messaging Integrations".
• schema.sql is described as safe to re-run, but a second run stops at alter table campaign_recipients rename column error_message to failure_reason (the column no longer exists). Found while testing the Infobip change on a scratch database. Resolved since: on 2026-10-12 the full script ran twice in a row on a copy of the live schema without errors.
• Found 2026-10-14 (API timings change): §4.6 "Testing" still says only billing is covered (the suite has ~1,540 tests across services, controllers, repositories and pages), and §9 still says "56 settings are declared" (the Accounts, Cache and Monitoring sections were added since).

12. Keeping this document current

Change log