System Requirements Document

1. Introduction

This System Requirements Document defines the controlled first build stage of VocalQuery BI, a production-grade, WhatsApp-native AI Business Intelligence SaaS platform for non-technical business teams and frontline operators.

The scope of this stage is deliberately bounded and consists of two linked artifacts treated as one controlled build stage, not two unrelated instructions:

1. Master/Foundational Instruction (Prompt 01) — establishes what VocalQuery BI is, its architecture, its operating principles, the hierarchy of truth that governs all computation and explanation, and everything that must be preserved throughout the build.
2. Module 1 — Data Model — the immediately following build instruction that designs and implements the data foundation: canonical entities, relationships, fields, tenant structure, users, businesses, customers, transactions, products, branches, channels, and related structures.

The foundational instruction must be established first; the Data Model module immediately follows it and is implemented against the principles set by the foundational instruction. The two uploads are executed as one controlled build stage: Prompt 01 sets the architecture and operating principles, and Module 1 immediately follows to actually design and implement the data foundation.

The governing operating principle of the entire product is that SQL and deterministic business rules perform the calculations, detection, and anomaly identification, while the LLM explains results. The LLM is never the source of truth.

2. System Overview

VocalQuery BI is a multi-tenant SaaS platform that allows a business user to ask a business question in plain language over WhatsApp (text or voice) and receive a computed, validated, interpreted answer with a recommended action — without the user needing SQL, Power BI, or a desktop dashboard.

Core workflow (mandatory processing sequence):

BUSINESS QUESTION → WhatsApp text / voice → intent understanding → security and permissions → schema/context retrieval → SQL generation or deterministic analytics → database execution → validation → analytical interpretation → business recommendation → WhatsApp response.

Hierarchy of truth (mandatory):

1. Deterministic business rules
2. SQL / database calculations
3. Statistical calculations
4. Anomaly detection
5. LLM interpretation and explanation

The LLM may interpret a question, generate SQL under strict controls, explain results, and recommend actions. The LLM must never invent database results.

System structure: The platform is built as a modular, layered application (application layers, frontend/admin layers, and test layers) with a canonical, modular business data model sitting between heterogeneous customer raw data sources and the intelligence engines. Raw data is never destroyed or silently changed; it flows through validation, normalization, and the canonical data model before any analytics are performed, preserving full traceability from answer back to source data.

Deployment trajectory: The application must initially run on Replit and later migrate cleanly to Google Cloud. No Replit-specific architecture may be hard-coded into business logic; environment variables and provider abstractions are required. When an integration is not yet configured, a clean provider interface plus a development/mock implementation must be created — never a fake integration that pretends to work.

3. Functional Requirements

3.1 Conversational WhatsApp Interface

• As a business manager, I want to ask business questions over WhatsApp using text so that I can obtain answers without knowing SQL, Power BI, or a desktop dashboard.
• As a business manager, I want to ask business questions over WhatsApp using voice so that frontline operators can query the business hands-free and without typing.
• As a business user, I want the system to respond to my question on WhatsApp with the analytical answer so that the conversation itself is the interface to the business.
• As a business user, I want my question to pass through intent understanding, so that the platform determines what I am actually asking before retrieving data.
• As a business user, I want my question to pass through security and permissions checks, so that I only ever receive data I am authorized to see.
• As a business user, I want the platform to perform schema/context retrieval before answering, so that the correct tables, columns, and business definitions are used for my question.
• As a business user, I want the platform to generate SQL or execute deterministic analytics against the database, so that my answer is computed from real data rather than generated text.
• As a business user, I want the executed result to be validated before it is shown to me, so that I can trust the numbers I receive.
• As a business user, I want the validated result to be analytically interpreted, so that I receive meaning rather than a raw table.
• As a business user, I want to receive a business recommendation with the answer, so that I know what action to take.

3.2 Business Questions the Platform Must Answer

• As a business manager, I want to ask "How much did we sell yesterday?" and receive the correct computed figure.
• As a business manager, I want to ask "Which branch is underperforming?" and receive an identified branch with supporting evidence.
• As a business manager, I want to ask "Why did sales drop this month?" and receive a causal, data-grounded explanation.
• As a business manager, I want to ask "Which products are running out?" and receive the products at inventory risk.
• As a business manager, I want to ask "Which customers stopped buying?" and receive the lapsed customers identified from transaction history.
• As a business manager, I want to ask "Which channel brings the highest-value customers?" and receive a channel comparison based on customer value.
• As a business manager, I want to ask "Where are we losing revenue?" and receive identified revenue leakage points.
• As a business manager, I want to ask "Which salespeople are below target?" and receive the salespeople flagged against target.
• As a business manager, I want to ask "Show me our top 10 customers" and receive a ranked customer list.
• As a business manager, I want to ask "What changed compared with last month?" and receive a period-over-period change analysis.
• As a business manager, I want to ask "Which branches need attention today?" and receive a prioritized list of branches requiring intervention.

3.3 Canonical Business Data Model (Module 1)

• As a platform, I want a canonical VocalQuery business data model that sits between raw customer data and VocalQuery intelligence, so that heterogeneous customer data can be interpreted consistently.
• As a platform, I want to ingest customer data from Excel files, so that spreadsheet-based businesses can use the platform.
• As a platform, I want to ingest customer data from MySQL tables, so that MySQL-based businesses can use the platform.
• As a platform, I want to ingest customer data from PostgreSQL databases, so that PostgreSQL-based businesses can use the platform.
• As a platform, I want to ingest customer data from BigQuery datasets, so that warehouse-based businesses can use the platform.
• As a platform, I want to ingest customer data from CSV files, so that flat-file exports can be onboarded.
• As a platform, I want to ingest customer data from APIs, so that live source systems can be connected.
• As a platform, I want to recognize that differently named columns (e.g., cust_id, customer_code, client_number) can all represent the same business concept (Customer ID), so that customer-specific naming does not block analysis.
• As a platform, I want a modular data model in which not every customer must provide every entity, so that an FMCG distributor can supply Customer, Product, Sales, Inventory, Branch, Payment, and Delivery while a fintech can supply Customer, Transaction, Payment, Product/Service, and Channel.
• As a platform, I want a canonical CUSTOMER entity, so that customer-level intelligence is available across all tenants.
• As a platform, I want a canonical PRODUCT entity, so that product-level intelligence is available across all tenants.
• As a platform, I want a canonical TRANSACTION/SALES entity, so that sales and revenue intelligence is available across all tenants.
• As a platform, I want a canonical INVENTORY entity, so that stock intelligence is available across all tenants.
• As a platform, I want a canonical BRANCH/LOCATION entity, so that location intelligence is available across all tenants.
• As a platform, I want a canonical EMPLOYEE/USER entity, so that people-level and RBAC intelligence is available across all tenants.
• As a platform, I want a canonical CHANNEL entity, so that channel attribution intelligence is available across all tenants.
• As a platform, I want a canonical PAYMENT entity, so that payment and collections intelligence is available across all tenants.
• As a platform, I want a canonical ORDER entity, so that pre-sale and fulfilment intelligence is available across all tenants.
• As a platform, I want a canonical DELIVERY entity, so that logistics intelligence is available across all tenants.
• As a platform, I want a canonical RETURN entity, so that return-rate intelligence is available across all tenants.
• As a platform, I want a canonical SUPPLIER entity, so that supply-side intelligence is available across all tenants.
• As a platform, I want a canonical MARKETING entity, so that marketing-to-revenue intelligence is available across all tenants.

Customer entity specification

• As a platform, I want the canonical customer object to support customer_id, customer_name, customer_type, customer_segment, geography, region, city, branch, acquisition_date, acquisition_channel, status, created_at, and updated_at, so that customer analytics are consistent.
• As a platform, I want to map only the fields a customer actually provides, so that missing fields do not block onboarding.
• As a platform, I want mapping such as customer_no → customer_id, client_name → customer_name, state → geography, and customer_group → customer_type, so that customer-specific naming resolves to canonical meaning.

Product entity specification

• As a platform, I want the product entity to support product_id, product_name, category, subcategory, brand, unit_cost, selling_price, margin, supplier_id, and status, so that product analysis is possible.
• As an analyst, I want to ask which products are losing sales, so that declining products are surfaced.
• As an analyst, I want to ask which products generate the highest margin, so that profitable products are identified.
• As an analyst, I want to ask which products are selling slowly, so that slow-moving stock is identified.
• As an analyst, I want to ask which products have falling sales despite having sufficient inventory, so that demand problems are separated from supply problems.

Sales transaction entity specification

• As a platform, I want the sales transaction entity to support transaction_id, transaction_date, customer_id, product_id, branch_id, salesperson_id, channel_id, quantity, unit_price, gross_revenue, discount, net_revenue, cost, gross_profit, and payment_status, so that it becomes the foundation for sales calculations.
• As a platform, I want revenue computed from the sales transaction entity.
• As a platform, I want orders count computed from the sales transaction entity.
• As a platform, I want units sold computed from the sales transaction entity.
• As a platform, I want average order value computed from the sales transaction entity.
• As a platform, I want gross profit computed from the sales transaction entity.
• As a platform, I want margin computed from the sales transaction entity.
• As a platform, I want customer purchase frequency computed from the sales transaction entity.
• As a platform, I want product performance computed from the sales transaction entity.
• As a platform, I want branch performance computed from the sales transaction entity.
• As a platform, I want channel performance computed from the sales transaction entity.

Inventory entity specification

• As a platform, I want the inventory entity to support inventory_id, date, product_id, branch_id, quantity_on_hand, reserved_quantity, available_quantity, unit_cost, and inventory_value.
• As a platform, I want Days Inventory Outstanding / Days of Inventory derived as Inventory on hand ÷ Average daily sales.
• As a platform, I want the rules engine to raise a warning when inventory falls below 3 days.

Branch/location entity specification

• As a platform, I want the branch/location entity to support branch_id, branch_name, city, state, region, country, manager_id, and status.
• As a platform, I want geographical hierarchy to be modelled (e.g., Nigeria → South-South → Port Harcourt / Uyo / Calabar; South-West → Lagos / Ibadan; North → Kano), so that VocalQuery understands where data belongs.
• As a platform, I want the branch/location hierarchy to feed directly into RBAC so that access scope can be enforced by geography.

Channel entity specification

• As a platform, I want the channel entity to support channel_id, channel_name, and channel_type.
• As a platform, I want channels such as Direct Sales, Distributor, Retail, Website, Instagram, WhatsApp, Referral, Google, and Sales Team to be modelled.
• As a platform, I want to determine not only whether revenue fell but which channel caused the decline.

Payment entity specification

• As a platform, I want the payment entity to support payment_id, transaction_id, customer_id, payment_date, amount, payment_method, payment_status, and reference.
• As a platform, I want to investigate outstanding payments.
• As a platform, I want to investigate payment failures.
• As a platform, I want to investigate delayed payments.
• As a platform, I want to investigate collections.
• As a platform, I want to investigate customer payment behaviour.
• As a platform, I want to investigate unusual payment patterns.

Order entity specification

• As a platform, I want the order entity to support order_id, order_date, customer_id, branch_id, channel_id, order_value, order_status, and delivery_status.
• As a platform, I want orders to be distinguished from completed sales transactions, because an order is not a completed sale.
• As a platform, I want order lifecycle states modelled as Placed → Confirmed → Dispatched → Delivered → Paid.
• As a platform, I want the alternative order lifecycle Placed → Cancelled to be modelled.
• As a platform, I want the order-versus-sale distinction preserved for later operational intelligence.

Delivery entity specification

• As a platform, I want the delivery entity to support delivery_id, order_id, customer_id, driver_id, origin, destination, dispatch_time, delivery_time, expected_delivery_time, delivery_status, and return_status.
• As a platform, I want to detect that delivery delays increased (e.g., "Delivery delays increased 23%").
• As a platform, I want to detect location-specific delivery failure rates (e.g., Port Harcourt deliveries have a significantly higher failure rate).

Return entity specification

• As a platform, I want the return entity to support return_id, order_id, customer_id, product_id, return_date, quantity, return_reason, and return_value.
• As a platform, I want return-rate analysis.
• As a platform, I want cross-domain intelligence such as "Product A's sales are increasing, but its return rate is also increasing."

Supplier entity specification

• As a platform, I want the supplier entity to support supplier_id, supplier_name, category, location, lead_time, and status.
• As a platform, I want to explain repeated out-of-stock events through supplier intelligence (e.g., supplier lead time increased from 5 to 11 days).

Employee/User entity specification

• As a platform, I want the employee/user entity to support user_id, name, phone, email, role, branch_id, region_id, and status.
• As a platform, I want roles such as Driver, Salesperson, Branch Manager, Regional Manager, Executive, Administrator, and Analyst to be supported.
• As a platform, I want scope to be modelled in addition to role (e.g., Regional Manager, Region: South-South, Branches: Port Harcourt, Uyo, Calabar).
• As a platform, I want the security engine to enforce that a user can only query data belonging to their authorized region.

Marketing entity specification

• As a platform, I want the marketing entity to support campaign_id, campaign_name, date, channel, spend, impressions, clicks, leads, conversions, and revenue.
• As a platform, I want to connect Marketing → Customers → Sales → Revenue rather than treating marketing as an isolated dashboard.

3.4 Data Dictionary, Mapping and Normalization

• As a platform, I want a VocalQuery Data Dictionary that records what each field actually means, so that the system can work with messy real-world business data.
• As a platform, I want the data dictionary to hold raw customer field, VocalQuery meaning, and type (e.g., cust_no → customer_id → string; client → customer_name → string; trans_dt → transaction_date → date; amt → net_revenue → decimal; qty → quantity → integer; prod_code → product_id → string; loc → branch_id → string).
• As a platform, I want data normalization so that a source with Date, Cust_No, Prod, Amount, Location maps to transaction_date, customer_id, product_id, net_revenue, branch_id.
• As a platform, I want data normalization so that a source with TransactionDate, ClientID, SKU, NetSales, Branch maps to transaction_date, customer_id, product_id, net_revenue, branch_id.
• As a platform, I want the same intelligence engines to operate on both normalized outputs, so that VocalQuery is scalable across customers.

3.5 Data Quality Layer

• As a platform, I want to check completeness — whether important fields are missing — before the intelligence engines analyze anything.
• As a platform, I want to check validity — whether dates are actually dates.
• As a platform, I want to check duplicates — whether transactions are duplicated.
• As a platform, I want to check consistency — whether the same customer has multiple IDs.
• As a platform, I want to check referential integrity — whether every product_id in sales actually exists in the product table.
• As a platform, I want to check numerical integrity — whether there are negative quantities or impossible values.
• As a platform, I want to check temporal integrity — whether there are future-dated transactions.
• As a platform, I want to produce a Data Quality Score (e.g., 94%).
• As a platform, I want to identify data problems before allowing certain analyses to run.

3.6 Data Integrity, Traceability and Architecture

• As a platform, I want raw data never to be destroyed or silently changed.
• As a platform, I want the processing pipeline RAW DATA → VALIDATION → NORMALIZATION → CANONICAL DATA MODEL → ANALYTICS enforced so that traceability is preserved.
• As an executive, I want to ask "Where did this number come from?" and have VocalQuery trace Answer → calculation → normalized field → source data.
• As a platform, I want the ingestion and canonicalization architecture to accept Customer Data (Excel / MySQL / BigQuery) → Data Ingestion → Data Validation → Data Mapping → Canonical Data Model.
• As a platform, I want the canonical data model to expose Customers, Products, Sales, Inventory, Branches, Orders, Payments, Delivery, Returns, Channels, Suppliers, and Marketing into the VocalQuery SQL Engine.

3.7 Deterministic Intelligence, Rules and Alerts

• As a platform, I want a deterministic business-rule engine so that business logic and detection are computed by rules rather than by the LLM.
• As a platform, I want SQL-first analytics so that calculations and detection are performed in the database.
• As a platform, I want Python analytics where appropriate to supplement SQL-first computation.
• As a platform, I want statistical calculations performed deterministically.
• As a platform, I want anomaly detection performed deterministically.
• As a platform, I want the LLM to be restricted to interpreting the question, generating SQL under strict controls, explaining results, and recommending actions.
• As a platform, I want the LLM to be prevented from inventing database results.
• As a platform, I want business rules to be manageable by an organization administrator.
• As a platform, I want alerts and alert events to be generated from rule evaluation.

3.8 Multi-Tenancy and Security

• As a platform, I want every customer to be a tenant.
• As a platform, I want the database designed around Organization → Users → Roles → Data Sources → Locations/Branches → Business Rules → Queries → Alerts → Reports → Audit Logs.
• As a platform, I want every tenant-owned record to carry tenant isolation.
• As a platform, I want to never allow one organization's data to appear in another organization's queries or results.
• As a platform, I want an authentication foundation to work before later features are built.
• As a platform, I want a security engine that enforces role plus scope (e.g., region and branch) on every query.
• As a platform, I want secure secrets handling so that credentials are never hard-coded.
• As a platform, I want audit logging of platform activity.

3.9 Admin Foundation

• As an organization administrator, I want to create an organization.
• As an organization administrator, I want to invite users.
• As an organization administrator, I want to assign roles.
• As an organization administrator, I want to create locations.
• As an organization administrator, I want to register data sources.
• As an organization administrator, I want to view system status.
• As an organization administrator, I want to view query history.
• As an organization administrator, I want to view usage.
• As an organization administrator, I want to manage business rules.
• As an organization administrator, I want to view alerts.
• As a platform, I do not want to attempt to build every feature yet; the admin foundation is limited to the capabilities listed above.

3.10 Platform Engineering and Delivery Requirements

• As a platform, I want clean architecture.
• As a platform, I want modular services.
• As a platform, I want dependency injection where appropriate.
• As a platform, I want strong typing.
• As a platform, I want configuration through environment variables.
• As a platform, I want structured logging.
• As a platform, I want error handling.
• As a platform, I want database migrations.
• As a platform, I want automated tests.
• As a platform, I want API versioning.
• As a platform, I want audit logging.
• As a platform, I want secure secrets handling.
• As a platform, I do not want one giant Python file.
• As a platform, I do not want business logic placed inside API routes.
• As a platform, I do not want hard-coded credentials.
• As a platform, I do not want hard-coded customer-specific database schemas.
• As a platform, I do not want fake integrations that pretend to work; when an integration is not yet configured, I want a clean provider interface plus a development/mock implementation.
• As a platform, I want the application to start successfully.
• As a platform, I want database migrations to run.
• As a platform, I want a health endpoint to work.
• As a platform, I want environment configuration to work.
• As a platform, I want the authentication foundation to work.
• As a platform, I want basic tenant isolation to be tested.
• As a platform, I want automated tests to pass.
• As a platform, I want a README explaining architecture, folder structure, local setup, environment variables, database setup, testing, deployment, and future Google Cloud migration.
• As a platform, I want a concise implementation report listing files created, database tables, APIs created, tests created, remaining work, and known risks.
• As a platform, I do not want to proceed into advanced BI features until this foundation is stable.

3.11 Required Database Entities (Foundation)

• As a platform, I want migrations/models for organizations.
• As a platform, I want migrations/models for users.
• As a platform, I want migrations/models for roles.
• As a platform, I want migrations/models for permissions.
• As a platform, I want migrations/models for user_roles.
• As a platform, I want migrations/models for locations.
• As a platform, I want migrations/models for data_sources.
• As a platform, I want migrations/models for data_source_credentials.
• As a platform, I want migrations/models for schemas.
• As a platform, I want migrations/models for schema_tables.
• As a platform, I want migrations/models for schema_columns.
• As a platform, I want migrations/models for saved_queries.
• As a platform, I want migrations/models for query_history.
• As a platform, I want migrations/models for business_rules.
• As a platform, I want migrations/models for alerts.
• As a platform, I want migrations/models for alert_events.
• As a platform, I want migrations/models for scheduled_reports.
• As a platform, I want migrations/models for audit_logs.
• As a platform, I want migrations/models for subscriptions.
• As a platform, I want migrations/models for usage_records.

3.12 Required Application Layer Structure

• As a platform, I want the backend organized into /app, /api, /core, /config, /auth, /models, /schemas, /services, /repositories, /analytics, /business_rules, /ai, /whatsapp, /data_sources, /security, /scheduler, /alerts, /reports, and /utils.
• As a platform, I want the frontend organized into /frontend, /frontend/app, /frontend/components, /frontend/lib, and /frontend/types.
• As a platform, I want tests organized into /tests, /tests/unit, /tests/integration, /tests/security, /tests/analytics, and /tests/ai.

4. User Personas

Business Manager / Frontline Operator (WhatsApp user) Asks business questions in plain language by WhatsApp text or voice and receives computed answers, explanations, and recommendations. Requires no SQL, BI tool, or desktop dashboard. This is the primary conversational persona.

Organization Administrator Performs the administrative foundation workflows: creating the organization, inviting users, assigning roles, creating locations, registering data sources, viewing system status, viewing query history, viewing usage, managing business rules, and viewing alerts.

Executive Consumes headline business answers and, critically, requires provenance: can ask "Where did this number come from?" and receive a trace from answer → calculation → normalized field → source data.

Scoped Regional / Branch Manager A role-and-scope-bound user (e.g., Regional Manager for South-South with branches Port Harcourt, Uyo, Calabar). Distinct from the generic business user only by the enforced scope of data they may query; the security engine restricts their queries to their authorized region and branches.

Analyst Consumes structured analytical outputs: product margin, slow-moving product, product performance, branch performance, channel performance, return-rate, and cross-domain intelligence results.

System actors (not personas): customer source systems (Excel, MySQL, PostgreSQL, BigQuery, CSV, APIs), the Gemini AI capability, and the Meta WhatsApp Cloud API. These are integration and system actors, not product personas.

5. Core User Flows

Flow A — Ask a business question over WhatsApp

1. Business user sends a question by WhatsApp text or voice.
2. Platform performs intent understanding on the question.
3. Platform performs security and permission checks for the requesting user.
4. Platform performs schema/context retrieval against the canonical data model and data dictionary.
5. Platform generates SQL or selects deterministic analytics under strict controls.
6. Platform executes against the database.
7. Platform validates the result.
8. Platform produces analytical interpretation.
9. Platform produces a business recommendation.
10. Platform responds to the user on WhatsApp.

Flow B — Administrator sets up a tenant

1. Organization administrator creates the organization.
2. Administrator invites users.
3. Administrator assigns roles.
4. Administrator creates locations/branches (with geographical hierarchy).
5. Administrator registers data sources.
6. Administrator views system status, query history, and usage.
7. Administrator manages business rules and views alerts.

Flow C — Onboard an organization's raw data (Module 1)

1. Raw customer data is received from Excel, MySQL, PostgreSQL, BigQuery, CSV, or API sources.
2. Data ingestion occurs.
3. Data validation runs.
4. Data quality checks run (completeness, validity, duplicates, consistency, referential integrity, numerical integrity, temporal integrity) and a Data Quality Score is produced.
5. Problem areas are identified before analyses are permitted to run.
6. Data mapping resolves customer field names to VocalQuery meanings via the data dictionary.
7. Data normalization converts source-specific fields to canonical fields.
8. Records are written into the canonical data model.
9. The VocalQuery SQL engine operates on the canonical entities.
10. Raw data remains intact and unchanged throughout.

Flow D — Executive traceability

1. Executive receives a computed figure.
2. Executive asks where the number came from.
3. Platform traces answer → calculation → normalized field → source data.
4. Executive receives the provenance chain.

Flow E — Foundational build stage execution

1. Master/Foundational instruction (Prompt 01) establishes architecture and operating principles.
2. Module 1 (Data Model) immediately follows and implements the data foundation.
3. Application starts successfully; migrations run; health endpoint works; environment configuration works; authentication foundation works; basic tenant isolation is tested; automated tests pass.
4. README is produced covering architecture, folder structure, local setup, environment variables, database setup, testing, deployment, and future Google Cloud migration.
5. Implementation report is produced listing files created, database tables, APIs created, tests created, remaining work, and known risks.
6. No advanced BI features are started until this foundation is stable.

6. Visuals, Colors and Theme

Not specified by the source material; restrained domain-appropriate defaults derived from an enterprise BI platform for non-technical business teams and frontline operators, delivered primarily as a WhatsApp-native interface with a supporting responsive admin web interface.

• Primary palette: deep indigo / slate navy for structure and trust (dense analytical surfaces, admin screens).
• Accent: a single functional accent used only for the conversational channel identity and primary actions; used sparingly, never decoratively.
• Signal colors: reserved strictly for meaning — green for positive movement, amber for warning (e.g., the inventory-below-3-days rule), red for negative movement or failure (e.g., delivery failure-rate flags). Signal color must never be used as decoration.
• Neutral surfaces: near-white background, light grey dividers, high-contrast dark text for numeric readability.
• Typography: a single clean sans-serif family; tabular/monospaced figures for numbers, so that financial values align and read precisely.
• Tone: institutional, terse, and calm. The visual language must communicate computed trustworthiness rather than consumer-app vibrancy. The WhatsApp conversation is the primary surface; the web interface is secondary and administrative.
• Data density: results presented as compact answer blocks (headline figure, change indicator, short explanation, recommended action) rather than chart-first dashboards, because the primary surface is a messaging thread.

7. Signature Design Concept

"The conversation is the dashboard."

The defining design concept is that the entire BI experience collapses into a single, trusted answer in a messaging thread. Every answer is presented as a structured Answer Card with a consistent, learnable anatomy:

1. The computed number or finding — the deterministic result, visually dominant.
2. The change/comparison context — versus last month, versus target, versus prior period.
3. The interpretation — one or two sentences of plain-language explanation.
4. The recommended action — what the user should do about it.
5. A provenance affordance — the ability to trace the answer back to source data.

The second signature element is deterministic trust made visible: because SQL and business rules compute and the LLM only explains, the interface must never present an LLM-styled paragraph where a computation belongs. Numbers look like numbers; explanations look like explanations. The visual system enforces this hierarchy at all times.

The third element is scoped reality: every answer is rendered within the user's authorized scope, and scope is a visible, silent property of the answer — an answer never appears to be global when it is actually regional or branch-bound.

8. Interaction Model & Motion Direction

Interaction model

• Conversational-first: all primary interaction happens through WhatsApp text and voice. Questions are open-ended natural language; answers are structured Answer Cards.
• Voice-parity: a voice question must produce the same structured answer as an equivalent text question, because frontline operators are a primary audience.
• Pipeline transparency on demand: the full chain (intent → permissions → schema retrieval → SQL/deterministic analytics → execution → validation → interpretation → recommendation) runs on every query, but only surfaces detail when the user asks for it.
• Progressive disclosure: the answer card shows the headline first; supporting tables, breakdowns, and provenance are revealed on further request.
• Deterministic boundaries are visible: when a result is unavailable because the underlying data failed validation or quality thresholds, the platform says so rather than producing an interpretation.
• Admin interface: the web interface is task-oriented and administrative — setup, registration, viewing history/usage/status, rule and alert management — not a charting environment.

Motion direction

• Motion is minimal, functional, and reassuring, appropriate to an enterprise trust tool.
• Processing honesty: while the pipeline runs, the user sees a single calm progress state that reflects real stages (understanding → checking permissions → retrieving data → computing → validating), never a looping decorative animation.
• Answer arrival: the Answer Card resolves in as one composed unit — headline, context, interpretation, action — rather than animating element-by-element, so the user reads the result as a single trusted statement.
• Change indicators: movement is used only to convey direction and magnitude of change (up/down/flat), and is always paired with the actual value so motion never carries meaning alone.
• No decorative motion in the admin interface; transitions are short, direct, and state-change driven.

9. Non-Functional Requirements

Architecture and portability

• The system must initially run on Replit and later migrate cleanly to Google Cloud.
• No Replit-specific architecture may be hard-coded into business logic.
• All environment-specific behavior must be driven by environment variables and provider abstractions.
• Business logic must not live in API routes.
• The application must not be implemented as one giant Python file.

Truth and correctness

• Deterministic business rules, SQL/database calculations, statistical calculations, and anomaly detection must take precedence over LLM output at all times.
• The LLM must never invent database results.
• SQL generated by the LLM must be produced under strict controls.
• Raw data must never be destroyed or silently changed.
• End-to-end traceability must be preserved: answer → calculation → normalized field → source data.

Security and tenancy

• Every tenant-owned record must be isolated by tenant.
• One organization's data must never appear in another organization's queries or results.
• Role plus scope enforcement must apply to every query.
• Credentials must never be hard-coded; secrets must be handled securely.
• Authentication foundation and basic tenant isolation must be tested before later features are built.

Data quality

• Data quality checks (completeness, validity, duplicates, consistency, referential integrity, numerical integrity, temporal integrity) must run before intelligence engines analyze data.
• A Data Quality Score must be produced, and identified problems must be able to block certain analyses.

Engineering quality

• Strong typing must be used.
• Structured logging must be used.
• Error handling must be implemented.
• Database migrations must be managed.
• Automated tests must exist and pass.
• API versioning must be applied.
• Audit logging must be implemented.
• The application must start successfully and expose a working health endpoint.

Integrations

• When an integration is not yet configured, a clean provider interface and a development/mock implementation must exist instead of a fake working integration.

Documentation

• A README covering architecture, folder structure, local setup, environment variables, database setup, testing, deployment, and future Google Cloud migration must be produced.
• An implementation report listing files created, database tables, APIs created, tests created, remaining work, and known risks must be produced.

10. Tech Stack

Backend

• Python
• FastAPI
• Pydantic
• SQLAlchemy
• PostgreSQL
• BigQuery client
• Google Cloud SDKs where required

Frontend / Admin

• Next.js
• TypeScript
• Responsive web interface

Background Jobs

• A provider-independent job/scheduler abstraction
• Initially a simple local/reliable scheduler
• Architecture must later support Cloud Scheduler

AI

• Gemini, accessed through a dedicated AI service layer
• Gemini API calls must never be scattered throughout the application

Messaging

• Meta WhatsApp Cloud API, accessed through a dedicated WhatsApp service

Analytics

• SQL-first analytics
• Python analytics where appropriate
• Deterministic business-rule engine

Target Cloud Architecture (migration target)

• Cloud Run for application services
• BigQuery for analytical workloads
• Cloud SQL / PostgreSQL for application/control-plane data
• Cloud Storage for files
• Cloud Scheduler for scheduled jobs
• Gemini for AI capabilities
• Meta WhatsApp Cloud API for messaging

Application Layers

• /app, /api, /core, /config, /auth, /models, /schemas, /services, /repositories, /analytics, /business_rules, /ai, /whatsapp, /data_sources, /security, /scheduler, /alerts, /reports, /utils
• Frontend: /frontend, /frontend/app, /frontend/components, /frontend/lib, /frontend/types
• Tests: /tests, /tests/unit, /tests/integration, /tests/security, /tests/analytics, /tests/ai

Build Environment

• Initial development/build environment: Replit

11. Assumptions and Constraints

Constraints

• The Master/Foundational Instruction and Module 1 — Data Model are treated as one controlled build stage; Module 1 immediately follows the foundational instruction and implements against its principles.
• No advanced BI features may be started until the foundation is stable.
• The admin interface is a basic secure foundation only; not every feature is to be built yet.
• Integrations that are not configured must not be faked.
• Business logic must not be placed in API routes.
• The system must not be a single giant Python file.
• Credentials and customer-specific database schemas must never be hard-coded.
• No Replit-specific architecture may be embedded in business logic.
• The LLM must never be the source of truth and must never invent database results.
• Raw data must never be destroyed or silently changed.
• Tenant isolation is absolute: cross-tenant data exposure is prohibited.
• Certain analyses must be blocked when data quality problems are identified.

Assumptions

• Customers will provide heterogeneous source data (Excel, MySQL, PostgreSQL, BigQuery, CSV, APIs) with inconsistent column naming.
• Not every customer will provide every canonical entity; the model must be modular and tolerate missing entities.
• Roles alone are insufficient; scope (region, branch) is required for authorization.
• Business users are non-technical and frontline; they will not use SQL, Power BI, or desktop dashboards.
• Voice input is a first-class input mode alongside text.
• Delivery shape is a multi-tenant SaaS with a WhatsApp-native primary interface and a supporting responsive admin web interface.
• The Nigerian geographic hierarchy is a representative scope example for location modelling and RBAC.

12. Glossary

VocalQuery BI — A WhatsApp-native AI Business Intelligence SaaS platform for non-technical business teams and frontline operators.

Master/Foundational Instruction (Prompt 01) — The build instruction that establishes what VocalQuery BI is, its architecture, operating principles, and what must be preserved throughout the build.

Module 1 — Data Model — The build instruction that immediately follows the foundational instruction and designs and implements the data foundation: entities, relationships, fields, indexes, tenant structure, users, businesses, customers, transactions, products, branches, and channels. Together with Prompt 01 it forms one controlled build stage.

Hierarchy of Truth — The mandatory precedence order: deterministic business rules → SQL/database calculations → statistical calculations → anomaly detection → LLM interpretation and explanation.

Canonical Data Model — The standardized VocalQuery business data model that heterogeneous customer data is mapped into.

Data Dictionary — The VocalQuery reference that defines what each field actually means, expressed as raw customer field, VocalQuery meaning, and type.

Data Normalization — The process of mapping customer-specific field names into canonical VocalQuery fields, e.g., Date/Cust_No/Prod/Amount/Location and TransactionDate/ClientID/SKU/NetSales/Branch both mapping to transaction_date/customer_id/product_id/net_revenue/branch_id.

Data Quality Score — A computed score (e.g., 94%) expressing the health of an incoming dataset, produced before analyses are permitted.

Tenant — Every customer organization on the platform; all tenant-owned records are isolated by tenant.

Organization — The root scoping entity for a tenant, parent to Users, Roles, Data Sources, Locations/Branches, Business Rules, Queries, Alerts, Reports, and Audit Logs.

RBAC — Role-Based Access Control; in VocalQuery it combines role with scope (region, branch).

Scope — The region and branch boundaries attached to a user, enforced by the security engine so a user can only query data belonging to their authorized region.

Days Inventory Outstanding / Days of Inventory — Inventory on hand ÷ average daily sales; used by the rules engine (e.g., inventory below 3 days triggers a warning).

Answer Card — The structured response format delivered over WhatsApp containing the computed finding, comparison context, interpretation, recommended action, and provenance affordance.

Provenance / Traceability — The chain Answer → calculation → normalized field → source data, preserving enterprise trust.

Provider Abstraction — A clean integration interface with a development/mock implementation used when a real integration is not yet configured.

Cloud Run / BigQuery / Cloud SQL / Cloud Storage / Cloud Scheduler / Gemini / Meta WhatsApp Cloud API — The target Google Cloud migration components and messaging/AI providers established in the foundational instruction.