Microservice architecture

[alexeysp11]

Description

Services

The new microservice architecture will look like this:

• API Gateway
• Metadata Service (accesses the database, no metadata cache)
• Query Execution Service (accesses the database, caches results in Redis with a short TTL, uses pagination)
• ETL Service (asynchronous, uses batching/pagination when reading from the database, caches execution statuses in Redis)
• Security Service
• Redis Cache Server (for ETL statuses, query results, tokens)
• Message Broker (Kafka/RabbitMQ) (for asynchronous communication between WPF and the ETL Service)
• WPF Client (communicates with everyone via API Gateway, uses an HTTP client)

Databases

A hybrid approach to data storage, as we work with two different types of data:

• User data: the database the user interacts with (from where metadata is read, where queries are executed, and where ETL comes from and goes). This is an external resource managed by the user.
• System data: databases your services need for their internal operation (settings, logs, statuses, tokens).

1. Metadata Service

• System DB (PostgreSQL/SQLite):
  • Global service settings
  • Error logs.
• Interaction with the user DB: receives a connection string from API Gateway (or from Redis/Broker) and executes READ queries against system tables (INFORMATION_SCHEMA) to obtain the DB schema.

2. Query Execution Service

• System DB (PostgreSQL/SQLite):
  • A log of executed user queries (query history)
  • Security settings (e.g., SQL command blacklists)
  • User saved queries.
• Interaction with the user DB: receives a connection string and executes READ/WRITE/DELETE queries (depending on user permissions) upon direct instruction from the WPF client.

3. ETL Service

• System DB (PostgreSQL/SQLite):
  • ETL task definitions (Source/Destination connection strings, field mapping)
  • Task schedule (cron)
  • Run history
  • Detailed execution logs (number of rows migrated, errors, start/finish times)
  • Batch status information.
• Interaction with the user DB: connects to the Source DB and Destination DB (two different user target databases).

4. Security Service (proposed authentication/authorization service)

• System DB (PostgreSQL):
  • Password hashes of your application's users
  • Roles
  • Permissions
  • Profile data
  • Refresh tokens.

5. Redis Cache

• Redis does not replace the service database, but rather complements it.
• Redis stores temporary, rapidly changing data: access tokens (TTL 15 minutes), statuses of current ETL tasks, and the SQL result cache.

Issues

• Microservice architecture / Preparing ORM #11
• Microservice architecture / API Gateway + Monolithic API #5
• Microservice architecture / Observability (Elasticsearch/Prometheus) #17
• Microservice architecture / Update WPF app #12
• Microservice architecture / Use Redis for caching (Security Data, User Preferences) #13
• Microservice architecture / Services (Metadata, Query Execution) #14
• Microservice architecture / Services (ETL) #15
• Microservice architecture / Services (Security)
• Microservice architecture / Update WPF application (ETL UI) #16