Q01 Core APIs - Architecture Overview

System Architecture

Q01 Core APIs implement a three-tier architecture with clear separation of concerns:

┌──────────────────────────────────────────────────────────────┐
│                     CLIENT LAYER                             │
│  ┌────────────┐  ┌────────────┐  ┌────────────┐            │
│  │   Web App  │  │ Mobile App │  │Application │            │
│  │            │  │            │  │Microservice│            │
│  └──────┬─────┘  └──────┬─────┘  └──────┬─────┘            │
│         │                │                │                  │
│         └────────────────┼────────────────┘                  │
│                          │                                   │
│                          │ HTTPS / JWT                       │
│                          v                                   │
├──────────────────────────────────────────────────────────────┤
│                   API GATEWAY LAYER                          │
│                ┌─────────────────────┐                       │
│                │    CoreService      │  PUBLIC-FACING API   │
│                │   (Go / Buffalo)    │                       │
│                │                     │                       │
│                │  Port: 8080         │                       │
│                └──────────┬──────────┘                       │
│                           │                                  │
│        ┌──────────────────┼──────────────────┐              │
│        │ Routes by HTTP method & route name  │              │
│        │                                      │              │
│        v                                      v              │
│  ┌─────────────────┐              ┌─────────────────┐      │
│  │   GET Request   │              │ POST/PUT/PATCH  │      │
│  │                 │              │ DELETE Request  │      │
│  └────────┬────────┘              └────────┬────────┘      │
│           │                                 │                │
├───────────┼─────────────────────────────────┼───────────────┤
│  BACKEND SERVICES LAYER (Internal - Not Directly Accessible)│
│           │                                 │                │
│           v                                 v                │
│  ┌─────────────────┐              ┌─────────────────┐      │
│  │   CoreQuery     │              │   CoreWrite     │      │
│  │ (PHP/Symfony)   │              │ (PHP/Symfony)   │      │
│  │                 │              │                 │      │
│  │ Read-optimized  │              │ Write-optimized │      │
│  │ - Field metadata│              │ - CRUD ops      │      │
│  │ - Query build   │              │ - Validation    │      │
│  │ - Filtering     │              │ - Cascades      │      │
│  │ - Permissions   │              │ - Outbox        │      │
│  └────────┬────────┘              └────────┬────────┘      │
│           │                                 │                │
│           └─────────────┬───────────────────┘                │
│                         │                                    │
├─────────────────────────┼────────────────────────────────────┤
│                   DATABASE LAYER                             │
│                         │                                    │
│        ┌────────────────┼────────────────┐                  │
│        v                v                v                   │
│  ┌──────────┐    ┌──────────┐    ┌──────────┐             │
│  │ Metadata │    │ Tenant 1 │    │ Tenant N │             │
│  │    DB    │    │   Data   │    │   Data   │             │
│  │          │    │   DB     │    │   DB     │             │
│  │TB_DIM    │    │TB_ANAG_*│    │TB_ANAG_*│             │
│  │TB_COST   │    │          │    │          │             │
│  │TB_TYPE_FIELDS│  │          │    │          │             │
│  │TB_MENU   │    │          │    │          │             │
│  │TB_OBJECT │    │          │    │          │             │
│  │TB_COUNTER│    │          │    │          │             │
│  └──────────┘    └──────────┘    └──────────┘             │
│                                                              │
│  ┌──────────────────────────────────────────┐              │
│  │        Message Queue (RabbitMQ)          │              │
│  │  Outbox events for distributed systems   │              │
│  └──────────────────────────────────────────┘              │
└──────────────────────────────────────────────────────────────┘

Components Deep Dive

1. CoreService (API Gateway)

Responsibilities:

Single public entry point for all Core API operations
Request routing based on HTTP method and named routes
CQRS dispatch - GET → CoreQuery, POST/PUT/PATCH/DELETE → CoreWrite
Cross-cutting concerns:
- CORS handling
- SSL/TLS termination and HTTPS redirect
- Request/response logging
- Error standardization
- Parameter validation middleware

Routing Logic:

The gateway analyzes incoming requests and routes them to the appropriate backend service:

Request Analysis:
├─ HTTP Method = GET
│  └─> Route to CoreQuery (Read Service)
│
└─ HTTP Method = POST/PUT/PATCH/DELETE
   └─> Route to CoreWrite (Write Service)

Examples:
- GET /api/v4/core/PRD        → CoreQuery
- POST /api/v4/core/PRD       → CoreWrite
- PUT /api/v4/core/PRD/123    → CoreWrite
- PATCH /api/v4/core/PRD/123  → CoreWrite
- DELETE /api/v4/core/PRD/123 → CoreWrite

2. CoreQuery (Read Service)

Responsibilities:

Read-only operations - all GET requests
Metadata interpretation for query construction
Dynamic SQL generation from TB_COST, TB_OBJECT
Field visibility filtering via COD_ON_OFF and center_dett
Permission enforcement via peso, source, ambiente
Query optimization - projections, filtering, pagination
Relational queries - joins via TB_OBJECT relationships
Document queries - hierarchical data retrieval

Query Flow:

Extract dimension ({dim}) and context (source, center_dett, peso, ambiente)
Load metadata from TB_DIM, TB_COST
Filter fields by COD_ON_OFF matching center_dett
Filter fields by peso (user weight)
Build SELECT clause from visible fields
Apply WHERE conditions from $filter parameter
Add ambiente filtering automatically
Execute query
Return JSON response

3. CoreWrite (Write Service)

Responsibilities:

Write operations - POST, PUT, PATCH, DELETE
Multi-layer validation:
- Token validation (JWT)
- Grant validation (permission level)
- Required field validation (COD_ON_OFF)
- Field type validation (TIPO_CAMPO)
Pre-insert functions - uuid, md5, counter, sequence, slugify
Counter generation via TB_COUNTER
Cascade operations - INSERT_CASCADE, UPDATE_CASCADE, DELETE_CASCADE
Transaction management - ACID guarantees
Outbox pattern - write-ahead log for event sourcing
RabbitMQ publishing - distribute change events
Audit trail - OWNER, LOWNER, CDATA, LDATA, TIMESTAMP

Write Flow:

Validate JWT token → Extract tenant, user, peso
Validate grant (level 4 for create, level 2 for update)
Load metadata from TB_DIM, TB_COST
Validate required fields (COD_ON_OFF 'N' for create, 'M' for update)
Execute pre-insert functions (counters, UUIDs)
Begin transaction
Execute INSERT/UPDATE/DELETE
Execute cascades if metadata defines them
Write outbox record (TB_ANAG_OUTBOX00)
Commit transaction
Publish outbox to RabbitMQ
Return success response

4. Metadata Database

Purpose: Store schema, validation rules, permissions

Key Tables:

Table	Purpose	Row Count (Typical)
TB_DIM	Dimension registry	100-500
TB_COST	Field definitions	5,000-20,000
TB_TYPE_FIELDS	Field type definitions	20-50
TB_MENU	Security areas (nested set)	500-2,000
TB_OBJECT	Object hierarchies and views (TYPE_OBJ=1/2)	100-500
TB_COUNTER	Counter configurations	100-500

Shared across all tenants - one metadata database serves all.

5. Tenant Data Databases

Purpose: Store actual business data for each tenant

Isolation: Each tenant has separate database (or schema)

Naming Convention: TB_ANAG_{DIM}00

Example:

TB_ANAG_PRD00 - Products
TB_ANAG_CLI00 - Customers
TB_ANAG_ORD00 - Orders

Common Fields (auto-added to all dimensions):

{DIM}_ID - Primary key
OWNER - Creating user
LOWNER - Last modifying user
CDATA - Creation timestamp
LDATA - Last modification timestamp
TREC - Record state (N=new, M=modified, C=cancelled)
TIMESTAMP - Microsecond timestamp
X{DIM}01 - Ambiente (environment filter)
X{DIM}02 - Ambiente_rif (reference environment)

6. RabbitMQ (Message Queue)

Purpose: Distribute change events for eventual consistency

Outbox Pattern:

Write to database and outbox table atomically in transaction
After commit, publish outbox to RabbitMQ
If publish fails, outbox record remains for retry
Guarantees at-least-once delivery

Exchange Naming: hash(tenantId).hash(environment).sync.exchange

Exchange Type: Fanout (broadcast to all subscribers)

Message Format:

{
  "XOUTBOX03": "sha1-hash",
  "XOUTBOX04": "PRD",  // Dimension
  "XOUTBOX05": "create",  // Operation
  "XOUTBOX06": {/* full record data */},
  "XOUTBOX09": "00000Q01-0000-0001-0000-000CUSTOMER",  // tenantId
  "XOUTBOX11": "mscoreservice",  // microservice
  "XOUTBOX14": "production"  // environment
}

Data Flow Examples

Example 1: Creating a Product

1. Client POSTs to CoreService
   POST https://coreservice:8080/api/v4/core/PRD
   Headers: {Authorization: Bearer eyJ...}
   Body: {
     source: "productManagement",
     XPRD01: "Widget Pro",
     XPRD02: 49.99
   }

2. CoreService routes to CoreWrite
   - Matches route name "coreCreatePath"
   - Calls routingPost(c, resource, "WRITE_API")
   - Forwards to CoreWrite backend

3. CoreWrite processes request
   - RequestValidatorSubscriber validates JWT
   - Extracts tenantId, userId, peso from token
   - Validates grant for source "productManagement" (requires level 4)
   - Loads TB_COST metadata for DIM='PRD'
   - Validates XPRD01, XPRD02 have COD_ON_OFF containing 'N'
   - Executes pre-insert: generates counter for XPRD03 (product code)
   - Begins transaction
   - Executes INSERT:
     INSERT INTO TB_ANAG_PRD00 (
       XPRD01, XPRD02, XPRD03,
       OWNER, CDATA, TREC, XPRD01_AMBIENTE
     ) VALUES (
       'Widget Pro', 49.99, 'PRD-2025-00123',
       'user123', '20251219153042', 'N', '1,2,3'
     )
   - Captures PRD_ID from lastInsertId()
   - Writes outbox record to TB_ANAG_OUTBOX00
   - Commits transaction
   - Publishes outbox to RabbitMQ
   - Deletes outbox record

4. CoreWrite responds to CoreService
   {status: 200, data: {PRD_ID: 456}}

5. CoreService responds to client
   {PRD_ID: 456, message: "Record created successfully"}

Example 2: Querying Products

1. Client GETs from CoreService
   GET https://coreservice:8080/api/v4/core/PRD?source=productList&center_dett=visualizza&$filter=XPRD02 gt 40

2. CoreService routes to CoreQuery
   - Matches route name "coreIndexPath"
   - Calls routingGet(c, resource, "QUERY_API")
   - Forwards to CoreQuery backend

3. CoreQuery processes request
   - RequestValidatorSubscriber validates JWT
   - Extracts peso, ambiente from token
   - Loads TB_COST metadata for DIM='PRD'
   - Filters fields: contains(COD_ON_OFF,'L') (visualizza = list view)
   - Filters fields: COD_UTENTE <= peso
   - Builds SELECT: SELECT PRD_ID, XPRD01, XPRD02 FROM TB_ANAG_PRD00
   - Adds WHERE: WHERE XPRD02 > 40 AND FIND_IN_SET(ambiente, XPRD01_AMBIENTE)
   - Executes query
   - Returns JSON array

4. CoreQuery responds to CoreService
   [{PRD_ID: 456, XPRD01: "Widget Pro", XPRD02: 49.99}, ...]

5. CoreService responds to client
   (Same response - pass-through)

Security Layers

Security enforced at multiple independent layers:

Layer 1: API Gateway (CoreService)

SSL/TLS termination
CORS validation
Rate limiting (can be added)
Request logging

Layer 2: Token Validation

JWT signature verification
Token expiration check
Claim extraction (tenantId, userId, peso, profile)

Layer 3: Grant Validation

Source (COD_MENU) permission check
Grant level verification (level 4 for create, level 2 for update)

Layer 4: Field-Level Access

COD_ON_OFF filtering by center_dett
COD_UTENTE filtering by peso (user weight)

Layer 5: Data Isolation

Ambiente filtering (tenant environment)
Automatic WHERE clause injection

Layer 6: Audit Trail

All changes logged via outbox
OWNER, LOWNER tracked automatically

No single point of failure - each layer independently enforces security.

Scalability Patterns

Horizontal Scaling

CoreService:

Stateless - can run multiple instances behind load balancer
Session data in JWT - no server-side session store

CoreQuery:

Read replicas for query distribution
Caching layer (Redis) can be added
Stateless - horizontal scaling easy

CoreWrite:

Can scale horizontally with database connection pooling
Outbox pattern ensures consistency even with multiple writers

Vertical Scaling

Metadata DB:

Small dataset (MB to GB range)
Can be heavily cached
Rarely changes - perfect for caching

Tenant DBs:

Isolated per tenant
Can be sharded by tenant
Large tenants can have dedicated database servers

Monitoring and Observability

Logging:

CoreService logs all requests via c.Logger()
Request/response bodies logged for debugging
Errors logged with stack traces

Metrics (can be added):

Request rate, latency per endpoint
Database query times
RabbitMQ publish success/failure rate
Outbox backlog size

Health Checks:

CoreService: GET /health
CoreQuery: Database connectivity
CoreWrite: Database + RabbitMQ connectivity

Summary

Q01 Core APIs implement a clean three-tier architecture:

CoreService - Public API gateway with CQRS routing
CoreQuery / CoreWrite - Internal backend services (transparent)
Metadata + Tenant databases - Separated concerns

Key architectural decisions:

✅ CQRS - read/write optimization
✅ Metadata-driven - runtime schema interpretation
✅ Multi-tenant - isolated data, shared metadata
✅ Event-sourced - outbox pattern for consistency
✅ Multi-layered security - defense in depth
✅ Horizontally scalable - stateless services

Next Steps

Learn about the CQRS Pattern in detail
Try the Quick Start guide
Understand Core Concepts that power this architecture

Q01 Core APIs - Architecture Overview

System Architecture​

Components Deep Dive​

1. CoreService (API Gateway)​

2. CoreQuery (Read Service)​

3. CoreWrite (Write Service)​

4. Metadata Database​

5. Tenant Data Databases​

6. RabbitMQ (Message Queue)​

Data Flow Examples​

Example 1: Creating a Product​

Example 2: Querying Products​

Security Layers​

Layer 1: API Gateway (CoreService)​

Layer 2: Token Validation​

Layer 3: Grant Validation​

Layer 4: Field-Level Access​

Layer 5: Data Isolation​

Layer 6: Audit Trail​

Scalability Patterns​

Horizontal Scaling​

Vertical Scaling​

Monitoring and Observability​

Summary​

Next Steps​

System Architecture

Components Deep Dive

1. CoreService (API Gateway)

2. CoreQuery (Read Service)

3. CoreWrite (Write Service)

4. Metadata Database

5. Tenant Data Databases

6. RabbitMQ (Message Queue)

Data Flow Examples

Example 1: Creating a Product

Example 2: Querying Products

Security Layers

Layer 1: API Gateway (CoreService)

Layer 2: Token Validation

Layer 3: Grant Validation

Layer 4: Field-Level Access

Layer 5: Data Isolation

Layer 6: Audit Trail

Scalability Patterns

Horizontal Scaling

Vertical Scaling

Monitoring and Observability

Summary

Next Steps