Data Model

How NovaBuildBot stores identity, businesses, teams, and per-business data — the tables behind the scenes and the rules that connect them.

Three layers: identity, business + access, per-business data. The same shape applies whether you signed up via Telegram or WhatsApp.

Identity layer (who is this human?)

accounts                          account_channels
─────────                         ─────────────────
id  (PK)                          account_id  (FK → accounts.id)
created_at                        channel     ('telegram' | 'whatsapp')
                                  channel_id  (TG user id / WA phone)
                                  is_active
                                  UNIQUE (channel, channel_id)

One account, many channels. When you run /linkwhatsapp +91… from Telegram, a second row is added to account_channels pointing at the same account_id.
A (channel, channel_id) the bot has never seen → new accounts row + new account_channels row. No automatic merging by phone number or name.
Everything downstream keys on account_id, not on channel id. Channel rows are just transports.

Business + access layer (who can manage which businesses?)

businesses                         business_members                    roles
──────────                         ─────────────────                   ─────
id  (PK)                           id (PK)                             id (PK)
business_name                      business_id (FK → businesses.id)    name
business_type                      account_id  (FK → accounts.id)      description
phone, email, address              user_phone (legacy, pre-accounts)   permissions text[]
plan                               role       (text — 'owner' | …)
site_repo, site_url                role_id    (FK → roles.id)
owner_account_id (FK → accounts)   is_owner   bool
whatsapp_phone_number_id           is_active  bool
features (subscriptions)           takes_appointments
disabled_at                        UNIQUE (business_id, account_id)
deleted_at                         UNIQUE (business_id, user_phone)

business_member_invitations
────────────────────────────
id, business_id (FK)
invited_by_account_id (FK → accounts)
invitee_channel ('telegram' | 'whatsapp')
invitee_channel_id (TG handle or WA phone)
role_id, is_owner
token (UUID), status, expires_at

businesses is the canonical record of a business. owner_account_id points at the founding owner; business_members is the modern N:M view for teams.
Many accounts can manage one business via business_members. Rows with is_owner=true have full rights; others are scoped by role.permissions.
One account can manage many businesses — every membership row is independent. The Nova Hub business switcher lists all of them.
/invite writes to business_member_invitations. On accept (by token), a business_members row is created with is_owner copied from the invitation.

Per-business data layer (everything else is scoped to `business_id`)

Table	What it holds	Keyed on
`bot_messages`	Owner ↔ bot DMs	`business_id`
`business_memories`	KV store the bot maintains about the business	`business_id, memory_key`
`business_subscriptions`	Active feature subscriptions	`business_id, product`
`support_tickets`	Owner-submitted tickets (`/support`)	`business_id`
`social_connections`	Instagram + Facebook tokens + page IDs	`business_id, platform`
`social_posts`	Posts published via the bot	`business_id`
`agent_runs`, `agent_schedules`, `agent_pending_approvals`, `agent_activity`	Autonomous-agent infrastructure	`business_id`
`conversations`	End-customer ↔ business chats (different from bot_messages!)	`business_id, customer_id`
`customers`	The business's own customers (patients, clients, etc.)	`business_id`
`appointments`	Booked slots between a customer and the business	`business_id, customer_id`
`availability_slots`	What hours are bookable	`business_id, date`
`campaigns`, `message_templates`	WhatsApp bulk-send infrastructure	`business_id`
`pending_signups`	Mid-signup state for not-yet-businesses	`(telegram_user_id, status)` or `(whatsapp_number, status)`

Two chat surfaces — don't confuse them

Owner ↔ bot	End-customer ↔ business
`bot_messages` table	`conversations` table
The owner managing their site/marketing	The business's customers booking, asking
Keyed on the owner's `account_id` + `business_id`	Keyed on `customer_id` + `business_id`
Drives the agent loop (Telegram/WhatsApp DMs to the bot)	Drives the NovaChat side (WhatsApp from external numbers to the business)

Rules the schema enforces / implies

Soft delete is the default. businesses.deleted_at filters every customer-facing query; admin can still see deleted rows for recovery.
An account can be an owner of multiple businesses — by inserting additional business_members rows with is_owner=true.
A business can have multiple owners — same shape, just multiple is_owner=true rows pointing at different accounts.
All admin authority sits on the identity layer (ADMIN_TELEGRAM_USER_IDS env var matches against account_channels.channel_id where channel='telegram'). It does NOT live in the schema today — there's a platform_admins table that's unused.
businesses.owner_account_id is the historic "primary owner" but the source of truth for team membership is business_members. Both should agree; queries that filter by ownership generally use business_members.is_owner=true.

Visual

                                    accounts ──┬─< account_channels   (TG, WA)
                                               │
                                               ├─< business_members ─> businesses ─< [per-biz data]
                                               │     (role, is_owner)
                                               │
                                               └─< business_member_invitations
                                                   (pending invites you sent)