Shop floor control API vs direct database access for real-time production data

MES DELMIA Apriso MES
, , , , , , , ,
1

We’re debating architecture approaches for a real-time dashboard that displays production counts, machine status, and quality metrics from Apriso shop floor control.

Option A: Use the REST API exclusively


GET /api/v1/shopfloor/production-status?refresh=5s

Option B: Direct database queries for read-only data

SELECT machine_id, status, count
FROM PRODUCTION_STATUS
WHERE timestamp > NOW() - INTERVAL 5 SECOND

The API feels cleaner from a security perspective and provides better auditability, but we’re concerned about data freshness since it might cache responses. Direct DB access gives us sub-second data but bypasses Apriso’s business logic. What’s the community consensus on API security vs direct DB risks for read-only real-time scenarios?

2

There’s a middle ground: use database views that Apriso provides specifically for reporting. These views abstract the underlying schema complexity and are officially supported. You get the performance of direct DB access with the stability guarantee that the view structure won’t change between versions. Check the documentation for PRODUCTION_STATUS_VIEW and similar reporting views.

3

Consider the operational impact beyond just technical performance. Direct DB access means your dashboard can show data that doesn’t match what operators see in the Apriso UI if there’s any business logic applied. This creates confusion and erodes trust in the system. API security isn’t just about authentication - it’s about ensuring data consistency across all consumers.

4

We use direct DB access for dashboards and it works great. The API has too much overhead for high-frequency polling. Just make sure you create a read-only database user and never write directly. The auditability concern is overblown - you can log all your queries if needed. API security is important for writes, but for reads, direct DB is faster and more reliable.

5

Having implemented both approaches across multiple Apriso deployments, I can provide some balanced perspective on this architectural decision.

API Security & Business Logic

The REST API isn’t just about authentication - it enforces critical business rules that direct DB access bypasses:

  • Status calculations that combine multiple tables
  • Timezone conversions for global operations
  • Unit of measure conversions
  • Security filtering based on plant/area access
  • Audit trail generation for data access

When you query the database directly, you’re seeing raw data that may not represent the actual business state. For example, a machine status might be ‘RUNNING’ in the MACHINE_STATUS table but the API returns ‘STARVED’ because it checks material availability from related tables.

Direct DB Risks & Upgrade Impact

Direct database access creates technical debt:

  • Schema changes between versions break your queries
  • Calculated fields require you to duplicate Apriso’s business logic
  • No protection against reading inconsistent data during transactions
  • Database maintenance operations (reindexing, partitioning) can impact your queries

I’ve seen companies spend weeks refactoring dashboard queries after an Apriso upgrade because table structures changed.

Data Freshness Reality

The API cache concern is valid but manageable:

  • Use Cache-Control: no-cache header for critical endpoints
  • Configure API gateway cache TTL per endpoint
  • For true real-time needs, use WebSocket subscriptions:
ws://apriso-server/api/v1/shopfloor/events
Subscribe to: machine.status.changed

WebSockets push updates immediately (sub-second) without polling overhead. This is far superior to either REST polling or DB polling.

Auditability & Compliance

In regulated industries (pharma, aerospace), direct DB access can violate compliance requirements:

  • No audit trail of who accessed what data
  • Bypasses data retention policies
  • Makes it harder to demonstrate data integrity for audits

The API automatically logs all access, which is critical for 21 CFR Part 11 compliance.

Recommended Hybrid Approach

  1. Primary path: Use REST API for all operational dashboards
  2. Real-time events: Implement WebSocket subscriptions for sub-second updates
  3. Analytics/BI: Use Apriso’s official reporting views for data warehouse ETL
  4. Emergency only: Direct DB for troubleshooting, never in production code

Performance Optimization

If API performance is still a concern:

  • Implement client-side caching with smart invalidation
  • Use GraphQL-style field selection to reduce payload size
  • Batch multiple requests into single API call where supported
  • Deploy API gateway closer to shop floor (edge computing)

The slight performance cost of the API is insurance against data inconsistency, upgrade breaks, and compliance issues. In my experience, the teams that chose direct DB access all eventually migrated to the API after encountering these problems - usually during a critical upgrade window.

Bottom Line: Use the API. If performance is insufficient, optimize the API infrastructure rather than bypassing it. The architectural integrity is worth more than the marginal performance gain from direct DB access.

6

Always use the API. Direct database access breaks your support contract with Dassault and you’ll be on your own if something goes wrong. The API is designed for exactly this use case and handles caching intelligently. If you need sub-second data, look into the WebSocket endpoints for shop floor events instead of polling REST.

7

The data freshness concern with the API is real. In 2022, the default cache TTL is 10 seconds for production status endpoints, which might not meet your real-time requirements. However, you can configure this in the API gateway settings or use the cache-control: no-cache header to force fresh data. Direct DB risks include missing calculated fields, incorrect status interpretations, and breaking changes during upgrades.