Skip to main content

Phase 8: Reports & Advanced

Depends on: Phase 5 (Transactions & Returns), Phase 4 (Inventory & Serials) New Endpoints: 7 New Files: 14

Goal​

Implement reporting dashboards (sales, inventory, members), the natural-language query interface (backed by the symbolic logic sandbox), and the mKonnekt SKAN data integration. After this phase, store managers have actionable analytics and the exploratory query feature is available.

Endpoints​

Store-Scoped — Store Manager+ (Read)​

MethodPathHandlerDescription
GET/stores/:store_id/reports/dashboardReportController.dashboardDashboard summary stats
GET/stores/:store_id/reports/salesReportController.salesSales report (date range)
GET/stores/:store_id/reports/inventoryReportController.inventoryInventory report
GET/stores/:store_id/reports/membersReportController.membersMembers/labor report
POST/stores/:store_id/queryQueryController.queryNatural language query
GET/stores/:store_id/query/schemaQueryController.schemaGet queryable schema

Store-Scoped — All Store Members​

MethodPathHandlerDescription
POST/stores/:store_id/mkonnekt/skandataMkonnektController.getSkanDataGet mKonnekt SKAN data

Report Specifications​

Dashboard Report​

Aggregates key metrics for a store's at-a-glance view.

Query Params: ?period=today|week|month|custom&start_date=&end_date=

Response shape:

{
  "total_sales": 15234.5,
  "transaction_count": 342,
  "average_transaction": 44.54,
  "top_products": [
    {
      "product_id": "...",
      "name": "Marlboro Gold",
      "quantity_sold": 89,
      "revenue": 1246.11
    }
  ],
  "sales_by_hour": [{ "hour": 8, "total": 432.0, "count": 12 }],
  "returns_count": 5,
  "returns_total": 67.5,
  "active_discounts": 3,
  "low_stock_alerts": 7,
  "compliance_checks": { "total": 15, "passes": 14, "fails": 1 }
}

Sales Report​

Detailed sales breakdown for a date range.

Query Params: ?start_date=&end_date=&group_by=day|week|month

Response shape:

{
  "periods": [
    {
      "period_start": "2026-02-01",
      "period_end": "2026-02-07",
      "total_sales": 5230.00,
      "transaction_count": 120,
      "tax_collected": 418.40,
      "discounts_applied": 156.90,
      "net_sales": 4654.70,
      "payment_breakdown": {
        "cash": 2100.00,
        "card": 3130.00
      }
    }
  ],
  "totals": { ... }
}

Inventory Report​

Stock status across all products.

Query Params: ?sort_by=quantity|name|value&include_inactive=false

Response shape:

{
  "total_products": 450,
  "total_stock_value": 23456.78,
  "low_stock_items": [{ "product_id": "...", "name": "...", "quantity": 3, "reorder_point": 10 }],
  "out_of_stock_count": 12,
  "categories": [{ "category": "Tobacco", "product_count": 45, "stock_value": 8900.0 }]
}

Members Report​

Labor/staffing summary.

Response shape:

{
  "total_members": 8,
  "by_role": { "store_admin": 1, "manager": 2, "cashier": 4, "stocker": 1 },
  "shift_summary": {
    "total_hours_scheduled": 320,
    "total_hours_worked": 298,
    "no_shows": 2
  },
  "top_performers": [
    {
      "user_id": "...",
      "name": "...",
      "transactions_processed": 156,
      "total_sales": 6890.0
    }
  ]
}

Natural Language Query​

How It Works​

The query endpoint connects to the symbolic logic engine (prolog-agent sandbox) to translate natural language questions into database queries.

Request:

{
  "question": "What were our top 5 selling products last week?",
  "history": [
    { "role": "user", "content": "Show me sales trends" },
    { "role": "assistant", "content": "Here are the sales trends..." }
  ]
}

Response:

{
  "success": true,
  "data": {
    "answer": "Your top 5 selling products last week were...",
    "query_executed": "SELECT p.name, SUM(ti.quantity) ... GROUP BY ... ORDER BY ... LIMIT 5",
    "results": [ ... ],
    "visualization_hint": "bar_chart"
  }
}

Schema Endpoint​

Returns the queryable tables/columns so the UI can show autocomplete hints.

Response:

{
  "tables": [
    {
      "name": "transactions",
      "description": "Sales transactions",
      "columns": [
        {
          "name": "total_spent",
          "type": "NUMERIC",
          "description": "Transaction total"
        }
      ]
    }
  ]
}

mKonnekt Integration​

POST /stores/:store_id/mkonnekt/skandata​

mKonnekt SKAN (Store Key Account Number) data retrieval. This is an external integration point for payment processing analytics.

Request:

{
  "store_key": "SK-12345",
  "date_range": { "start": "2026-02-01", "end": "2026-02-08" }
}

Response: Proxied from mKonnekt API (schema TBD based on mKonnekt documentation).

Files to Create​

Controllers (3)​

controller/store/ReportController.java
  @RequestMapping("/stores/{storeId}/reports")
  GET /dashboard -> reportService.getDashboard(storeId, period, startDate, endDate)
  GET /sales     -> reportService.getSalesReport(storeId, startDate, endDate, groupBy)
  GET /inventory -> reportService.getInventoryReport(storeId, sortBy, includeInactive)
  GET /members   -> reportService.getMembersReport(storeId)

controller/store/QueryController.java
  @RequestMapping("/stores/{storeId}/query")
  POST /        -> queryService.query(storeId, request)
  GET  /schema  -> queryService.getSchema()

controller/store/MkonnektController.java
  @RequestMapping("/stores/{storeId}/mkonnekt")
  POST /skandata -> mkonnektService.getSkanData(storeId, request)

Services (3)​

service/ReportService.java
  - getDashboard: aggregates transactions, returns, discounts, inventory, compliance
  - getSalesReport: groups transactions by period with payment breakdown
  - getInventoryReport: joins inventory_levels with products for stock analysis
  - getMembersReport: joins store_memberships with shifts_actual for labor stats
  - All use raw SQLDelight aggregate queries (no new tables needed)

service/QueryService.java
  - query: sends natural language to prolog-agent, executes resulting SQL
  - getSchema: returns static schema metadata for the store's queryable tables
  - NOTE: May need HTTP client to call prolog-agent service (or in-process if colocated)

service/MkonnektService.java
  - getSkanData: proxies request to external mKonnekt API
  - Requires external HTTP client (RestTemplate or WebClient)
  - API credentials stored in Secret Manager

Request DTOs (2)​

dto/request/QueryRequest.java
  { question: @NotBlank String, history: List<QueryHistoryEntry> }
  (inner: { role: String, content: String })

dto/request/SkanDataRequest.java
  { storeKey: @NotBlank String, dateRange: DateRange }
  (inner: { start: LocalDate, end: LocalDate })

Response DTOs (6)​

dto/response/DashboardReportResponse.java
dto/response/SalesReportResponse.java
dto/response/InventoryReportResponse.java
dto/response/MembersReportResponse.java
dto/response/QueryResponse.java
dto/response/QuerySchemaResponse.java
dto/response/SkanDataResponse.java

Technical Notes​

  1. Reports use aggregate queries — no new tables, just complex SELECT with GROUP BY
  2. Dashboard caches could be added later but start without caching
  3. Query service is the most complex — depends on prolog-agent being deployed/accessible
  4. mKonnekt is a stub initially — actual integration depends on mKonnekt API access
  5. Report date ranges default to last 30 days if not specified

Acceptance Criteria​

  1. Dashboard returns correct aggregate stats for the specified period
  2. Sales report groups correctly by day/week/month
  3. Inventory report identifies low-stock and out-of-stock items
  4. Members report shows shift hours and transaction counts
  5. Query endpoint accepts natural language and returns structured results
  6. Schema endpoint returns queryable table metadata
  7. mKonnekt endpoint proxies to external API (or returns stub response)
  8. All report endpoints accept date range parameters
  9. Bazel build passes
  10. Unit tests for ReportService (with mocked repository data)