Service Entity Management and Persistence

Overview

The Service Repository is a data persistence component responsible for all database operations related to service entities. It provides a programmatic interface for creating, reading, updating, and deleting services and their associated configurations. This component sits between the administrative API logic and the underlying database, encapsulating all SQL queries and transaction management for service objects.

The repository manages the core s2r_service table and interacts with related tables for service versions, runtime routes, and backend profiles. Its primary role is to ensure data integrity and to abstract the details of the database schema from the rest of the application. It is called by higher-level administrative services to manage the complete lifecycle of an API service definition on the platform.

Key responsibilities include:

• Persisting new service definitions with appropriate defaults.
• Retrieving individual services or paginated lists with filtering and sorting.
• Updating service properties, including status, backend configuration, and feature toggles.
• Managing runtime route state in response to service status changes.
• Executing a robust, multi-step hard-delete process for services that are no longer active.
• Handling multi-tenancy by scoping all database operations to the currently bound tenant.

How it Works

The Service Repository uses a JdbcTemplate to execute raw SQL queries against the database. All operations are tenant-scoped, ensuring that one tenant cannot access or modify another tenant's service data.

Data Model and Retrieval

A service is represented by the ServiceRecord object, which aggregates data from several database tables. The primary table is s2r_service.

When retrieving a service, the repository joins s2r_service with two key subqueries:

1. Version State (VERSION_STATE_JOIN_SQL): A subquery on s2r_service_version that calculates the latest active version number (active_version_no) and the latest draft version number (draft_version_no) for each service.
2. Usage Data (USAGE_JOIN_SQL): A subquery on s2r_runtime_agg_service_hourly that finds the most recent timestamp (last_api_usage_at) a service was used in a given environment.

This composition provides a complete view of a service's definition, versioning state, and recent activity in a single query.

Service Creation

When a new service is created via the create method, a new record is inserted into the s2r_service table.

• A new UUID is generated for the service ID.
• The service status defaults to draft.
• The request schema enforcement_mode defaults to enforce to provide strict validation for new services out of the box.
• If the direction is not specified, it defaults to PUBLISH.

Service Updates and State Synchronization

The update method modifies an existing service record. It coalesces new values with existing ones, allowing for partial updates.

A critical function is syncRuntimeRouteState, which is triggered after a service's status is changed via update or deprecate. This method ensures the runtime routing table (s2r_runtime_route) reflects the service's current state:

1. Disabled/Deprecated: If the new status is disabled or deprecated, all runtime routes for that service are immediately disabled (enabled = false).
2. Other Statuses (e.g., 'active'): a. All existing routes for the service are first disabled. b. The repository queries for all enabled operations (o.enabled = true) on the service's active version (sv.is_active = true). c. For each enabled operation, a RouteSeed is created. d. A RouteCollisionGuard checks if any of the new route paths are already in use by a different service. If a collision is detected, the operation fails. e. If no collisions exist, the repository "upserts" the routes into s2r_runtime_route. An INSERT ... ON CONFLICT statement claims the route path for this service, setting enabled = true.

Special Handling for GATEWAY Services

Services with direction set to GATEWAY have special handling for both updates and route synchronization:

• Advisory Locking: Before an update or deprecation, the repository acquires a tenant- and environment-scoped advisory lock (lockEnvironmentIfGateway). This prevents race conditions with other processes that might modify routing declarations for the same environment.
• Route Collapsing: GATEWAY services expose a single endpoint (POST /{environment}/{serviceKey}) regardless of how many operations are defined. The collapseGatewaySeeds method transforms the list of per-operation RouteSeed objects into a single seed for the service's canonical gateway endpoint. The operation_id from the first enabled operation (ordered by operation_key) is used to satisfy schema constraints, but it does not affect routing.

Hard Deletion

The hardDelete method permanently removes a service and its dependent configuration from the database. It is designed to be a safe and bounded operation.

The deletion process follows these steps:

1. Pre-computation: It first counts the number of associated child records (routes, operations, versions, etc.) that will be deleted.
2. Pre-cleanup: It performs several UPDATE and DELETE operations to remove dependencies that could cause foreign key violations or long-running transactions. This includes:
   • Nullifying service_id in s2r_alert_mail_history.
   • Deleting from s2r_alert_state, s2r_service_alert_config, and s2r_service_synthetic_monitor.
   • Nullifying default_backend_profile_id on the s2r_service itself.
   • Deleting associated s2r_consumer_ip_grant records.
3. Cascading Deletes: It deletes records from child tables in order, such as s2r_runtime_route, s2r_openapi_artifact, s2r_backend_profile, s2r_operation, and s2r_service_version.
4. Final Deletion: It deletes the master record from the s2r_service table.

The entire deletion sequence is wrapped in a retry loop that can execute up to HARD_DELETE_MAX_ATTEMPTS (5) times. This makes the operation resilient to transient database lock contention (PessimisticLockingFailureException).

flowchart TD
    subgraph hardDelete(serviceId)
        A[Find service by ID] --> B{Service 'active'?};
        B -- Yes --> C[Throw IllegalStateException];
        B -- No --> D[Start retry loop, attempt=1];
        D --> E[Execute cascade of DELETE statements];
        E --> F{PessimisticLockingFailureException?};
        F -- No --> G[Return HardDeleteResult];
        F -- Yes --> H{Attempts < 5?};
        H -- Yes --> I[Log, backoff, increment attempt];
        I --> E;
        H -- No --> J[Log final error, re-throw exception];
    end

Method Reference

The following public methods are available on the ServiceRepository class.

create Method

Creates a new service record.

Parameters:

Returns: The full ServiceRecord of the newly created service.

Example SQL Execution: The method executes a SQL statement similar to the following:

insert into s2r_service (id, service_key, service_name, environment, status, direction, debug_enabled, description, enforcement_mode, created_at, updated_at, created_by, updated_by)
values (?, ?, ?, ?, 'draft', ?, false, null, 'enforce', now(), now(), ?, ?)

hardDelete Method

Permanently deletes a service.

Parameters:

Returns: A HardDeleteResult record containing the ID and key of the deleted service, a boolean deleted flag, and counts of associated records that were removed.

HardDeleteResult Fields:

Service Properties Reference

The following table describes the properties of a service, which correspond to columns in the s2r_service table and joined data.

Troubleshooting

PessimisticLockingFailureException on hardDelete

• Cause: This exception indicates that the hardDelete operation encountered a database lock on one of the tables it needs to modify, and the lock could not be acquired. This is often due to concurrent operations, such as high-volume traffic logging or another administrative action.
• Resolution: The system automatically retries the operation. The hardDelete method will attempt the deletion up to 5 times with an increasing backoff period. If all attempts fail, the exception is re-thrown, and the final failure is logged. The user may need to retry the operation later during a period of lower system load.

IllegalArgumentException

This exception is thrown when input validation fails. Common causes include:

• Providing an unsupported value for status, enforcement_mode, response_enforcement_mode, backend_routing_mode, or backend_health_check_mode.
• Providing an out-of-range value for backend_health_check_interval_sec (must be 1-3600) or backend_health_check_timeout_ms (must be 100-120000).
• Providing an out-of-range value for traffic_capture_remaining (must be 0-50).
• Attempting to find or update a service with an ID that does not exist for the current tenant.

IllegalStateException

This exception indicates an invalid state for an operation.

• "active service cannot be hard deleted": Thrown by hardDelete if the target service has a status of active. The service must be deactivated first.
• "service vanished mid-update": Thrown by an update method if the service record is deleted by another process between the initial read and the final read-back. This is a rare race condition.

Route Collision

The syncRuntimeRouteState method internally uses a RouteCollisionGuard. If an attempt to activate a service version would create a route (e.g., POST /my/path) that is already active for a different service in the same environment, the operation will fail to prevent ambiguity in routing. The conflicting operation on the new service version must be disabled or its path changed.

See Also

• Auditing: All write operations (create, update, deprecate, etc.) are scoped by tenant using SQL fragments from the AuditRepository.
• Trusted Proxy Declarations: For GATEWAY services, the advisory lock taken during updates serializes operations against the archival process for trusted proxy declarations, managed by the TrustedProxyDeclarationAdminRepository.

All SOAP-to-REST docs