Skip to content

TripQlub Platform — Database-Driven API Access Management

Overview

This document describes the design for managing API access rules (which roles can access which APIs) and module release status through a database-driven system with an admin UI, replacing the previous property-file approach.

Goals

  • Eliminate configuration drift between environments — each environment's database holds its own access rules natively.
  • Prevent forgotten API registrations — new APIs are auto-discovered and registered at application startup.
  • Control module releases per environment — unreleased modules are invisible in production regardless of whether the code is deployed.
  • Provide auditability — all changes to access rules are logged with who changed what and when.
  • Enable runtime changes — role assignments can be updated without redeployment.

Database Schema

Tables

api_modules

Tracks each backend module and its release status per environment.

CREATE TABLE api_modules (
    module_name     VARCHAR(100) PRIMARY KEY,
    display_name    VARCHAR(255),
    description     TEXT,
    is_released     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

api_access_rules

Stores access rules for each individual API endpoint.

CREATE TABLE api_access_rules (
    api_name        VARCHAR(255) PRIMARY KEY,
    module_name     VARCHAR(100) NOT NULL REFERENCES api_modules(module_name),
    allowed_roles   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT FALSE,
    is_stale        BOOLEAN NOT NULL DEFAULT FALSE,
    default_roles   TEXT NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_api_access_module ON api_access_rules(module_name);
CREATE INDEX idx_api_access_active ON api_access_rules(is_active);

api_access_audit_log

Immutable log of all changes made to modules and API access rules.

CREATE TABLE api_access_audit_log (
    id              SERIAL PRIMARY KEY,
    api_name        VARCHAR(255),
    module_name     VARCHAR(100),
    field_changed   VARCHAR(50) NOT NULL,
    old_value       TEXT,
    new_value       TEXT,
    changed_by      VARCHAR(100) NOT NULL,
    changed_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_api ON api_access_audit_log(api_name);
CREATE INDEX idx_audit_module ON api_access_audit_log(module_name);
CREATE INDEX idx_audit_time ON api_access_audit_log(changed_at);

Column Reference

Table Column Purpose
api_modules is_released Controls whether the entire module's APIs are available in this environment
api_access_rules allowed_roles Comma-separated list of roles permitted to call this API
api_access_rules is_active Fine-grained control — allows disabling individual APIs within a released module
api_access_rules is_stale Flagged automatically when an API exists in the DB but is no longer found in code
api_access_rules default_roles The roles declared in code via annotation — stored for reference, not used at runtime

Flyway Migration Script

This schema should be added as a Flyway migration (e.g., V010__create_api_access_tables.sql):

-- V010__create_api_access_tables.sql

CREATE TABLE api_modules (
    module_name     VARCHAR(100) PRIMARY KEY,
    display_name    VARCHAR(255),
    description     TEXT,
    is_released     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE api_access_rules (
    api_name        VARCHAR(255) PRIMARY KEY,
    module_name     VARCHAR(100) NOT NULL REFERENCES api_modules(module_name),
    allowed_roles   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT FALSE,
    is_stale        BOOLEAN NOT NULL DEFAULT FALSE,
    default_roles   TEXT NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_api_access_module ON api_access_rules(module_name);
CREATE INDEX idx_api_access_active ON api_access_rules(is_active);

CREATE TABLE api_access_audit_log (
    id              SERIAL PRIMARY KEY,
    api_name        VARCHAR(255),
    module_name     VARCHAR(100),
    field_changed   VARCHAR(50) NOT NULL,
    old_value       TEXT,
    new_value       TEXT,
    changed_by      VARCHAR(100) NOT NULL,
    changed_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_api ON api_access_audit_log(api_name);
CREATE INDEX idx_audit_module ON api_access_audit_log(module_name);
CREATE INDEX idx_audit_time ON api_access_audit_log(changed_at);

API Annotation

Each API endpoint declares its module and default roles using an annotation. This keeps access metadata co-located with the code and visible during development.

Annotation Definition

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ApiAccess {
    /** The module this API belongs to */
    String module();

    /** Default roles allowed to access this API */
    String[] defaultRoles();

    /** Optional description for the admin UI */
    String description() default "";
}

Usage on Endpoints

@ApiAccess(module = "bookings", defaultRoles = {"AGENT", "ADMIN"})
public Response createBooking(Request req) { ... }

@ApiAccess(module = "bookings", defaultRoles = {"AGENT", "ADMIN", "CUSTOMER"})
public Response listBookings(Request req) { ... }

@ApiAccess(module = "bookings", defaultRoles = {"AGENT", "ADMIN"})
public Response cancelBooking(Request req) { ... }

@ApiAccess(module = "ai-planner", defaultRoles = {"AGENT", "ADMIN"})
public Response suggestItinerary(Request req) { ... }

@ApiAccess(module = "ai-planner", defaultRoles = {"AGENT", "ADMIN"})
public Response generateTrip(Request req) { ... }

@ApiAccess(module = "ai-planner", defaultRoles = {"AGENT", "ADMIN", "CUSTOMER"})
public Response submitFeedback(Request req) { ... }

Auto-Registration at Startup

On application startup, the system scans all annotated API endpoints and synchronizes them with the database. This ensures new APIs are always registered without manual intervention, while never overwriting existing configuration.

Startup Sync Logic

@OnStartup
public class ApiRegistrySync {

    private final ApiAccessRuleRepository accessRuleRepo;
    private final ApiModuleRepository moduleRepo;
    private final ApiEndpointScanner scanner;
    private final AppConfig config;

    public void synchronize() {
        Set<ApiEndpoint> declaredApis = scanner.scanAnnotatedEndpoints();

        // 1. Ensure all modules exist
        Set<String> declaredModules = declaredApis.stream()
            .map(ApiEndpoint::getModule)
            .collect(Collectors.toSet());

        for (String moduleName : declaredModules) {
            if (!moduleRepo.exists(moduleName)) {
                moduleRepo.create(moduleName, config.isAutoActivate());
                log.info("Registered new module: {} (released={})",
                         moduleName, config.isAutoActivate());
            }
        }

        // 2. Register new APIs
        for (ApiEndpoint api : declaredApis) {
            if (!accessRuleRepo.exists(api.getName())) {
                boolean activate = config.isAutoActivate();
                accessRuleRepo.create(
                    api.getName(),
                    api.getModule(),
                    String.join(", ", api.getDefaultRoles()),  // allowed_roles
                    String.join(", ", api.getDefaultRoles()),  // default_roles
                    activate                                    // is_active
                );
                log.info("Registered new API: {} [module={}, active={}]",
                         api.getName(), api.getModule(), activate);
            }
        }

        // 3. Detect stale APIs (in DB but no longer in code)
        Set<String> codeApiNames = declaredApis.stream()
            .map(ApiEndpoint::getName)
            .collect(Collectors.toSet());
        Set<String> dbApiNames = accessRuleRepo.getAllApiNames();

        Set<String> staleApis = new HashSet<>(dbApiNames);
        staleApis.removeAll(codeApiNames);

        if (!staleApis.isEmpty()) {
            accessRuleRepo.markStale(staleApis);
            log.warn("Stale APIs detected (in DB but not in code): {}", staleApis);
        }

        // 4. Clear stale flag for APIs that are back in code
        Set<String> restoredApis = new HashSet<>(codeApiNames);
        restoredApis.retainAll(dbApiNames);
        accessRuleRepo.clearStaleFlag(restoredApis);
    }
}

Environment-Specific Behavior

Controlled by a single application property:

# Development environments: auto-activate new APIs and modules
api.access.auto-activate = true

# Production / staging: register but leave inactive pending manual activation
api.access.auto-activate = false
Environment auto-activate New Module New API
Dev (laptops, home lab) true Released automatically Active automatically
Test / Stage false Registered, not released Registered, not active
Production false Registered, not released Registered, not active

Runtime Access Check

Access Rule Enforcement

The access check runs on every API call. Since access rules are checked frequently, they are cached in memory and refreshed periodically.

public class ApiAccessFilter {

    private volatile Map<String, Set<String>> cachedRules = new ConcurrentHashMap<>();
    private volatile Set<String> releasedModules = ConcurrentHashMap.newKeySet();

    /**
     * Checks whether a given role is allowed to access the specified API.
     * Returns true if access is granted, false otherwise.
     */
    public boolean isAllowed(String apiName, String userRole) {
        // 1. Extract module from API name: "api.bookings.create" → "bookings"
        String module = extractModule(apiName);

        // 2. Check if the module is released
        if (!releasedModules.contains(module)) {
            return false;
        }

        // 3. Check if the API is active and the role is allowed
        Set<String> allowedRoles = cachedRules.get(apiName);
        if (allowedRoles == null) {
            return false;  // API not registered or not active
        }

        return allowedRoles.contains(userRole);
    }

    private String extractModule(String apiName) {
        // "api.bookings.create" → "bookings"
        String[] parts = apiName.split("\\.", 3);
        return parts.length >= 2 ? parts[1] : "";
    }
}

Cache Refresh

/**
 * Refreshes the in-memory cache from the database.
 * Runs on a scheduled interval and can be triggered on demand
 * via the admin API.
 */
@Scheduled(fixedRate = 60_000)  // every 60 seconds
public void refreshCache() {
    // Load only active APIs in released modules
    Map<String, Set<String>> newRules = accessRuleRepo.loadActiveRulesInReleasedModules();
    Set<String> newModules = moduleRepo.loadReleasedModuleNames();

    this.cachedRules = newRules;
    this.releasedModules = newModules;

    log.debug("Access rules cache refreshed: {} APIs in {} modules",
              newRules.size(), newModules.size());
}

The 60-second refresh means that changes made through the admin UI take effect within one minute across all backend instances, without any restart or redeployment.

Force Refresh Endpoint

For immediate effect after admin changes:

@ApiAccess(module = "admin", defaultRoles = {"ADMIN"})
public Response forceRefreshAccessRules(Request req) {
    refreshCache();
    return Response.ok("Cache refreshed: " + cachedRules.size() + " active rules");
}

Admin UI

The admin interface provides two main screens for managing modules and API access rules, plus an audit log viewer.

Module Management Screen

Displays all registered modules with their release status.

Module Display Name Released APIs (Active / Total) Actions
bookings Bookings ✅ Yes 12 / 12 Toggle, Edit
itineraries Itineraries ✅ Yes 8 / 8 Toggle, Edit
ai-planner AI Trip Planner ❌ No 0 / 6 Toggle, Edit
b2b-connect B2B Connect ❌ No 0 / 4 Toggle, Edit

Actions:

  • Toggle release — flips is_released. When releasing a module for the first time, prompts whether to also activate all its APIs or review them individually.
  • Edit — update display name and description.

API Access Rules Screen

Filterable by module, with indicators for status.

API Name Module Allowed Roles Active Stale Actions
api.bookings.create bookings AGENT, ADMIN ✅ — Edit
api.bookings.list bookings AGENT, ADMIN, CUSTOMER ✅ — Edit
api.ai-planner.suggest ai-planner AGENT, ADMIN ❌ — Edit
api.legacy.old-endpoint legacy ADMIN ✅ ⚠️ Edit, Remove

Actions:

  • Edit roles — modify the allowed_roles list. Shows the default_roles for reference so the admin can see what the code originally declared.
  • Toggle active — enable/disable an individual API within a released module.
  • Remove stale — delete APIs that no longer exist in code (flagged with ⚠️).

Audit Log Screen

Searchable and filterable log of all changes.

Timestamp Changed By API / Module Field Old Value New Value
2025-02-07 14:30 admin@agency api.bookings.create allowed_roles AGENT, ADMIN SENIOR_AGENT, ADMIN
2025-02-07 14:28 admin@agency module: ai-planner is_released false true
2025-02-07 10:15 SYSTEM (startup) api.ai-planner.feedback — — Created (inactive)

Workflow Scenarios

Scenario 1: New API Added to an Existing Released Module

  1. Developer adds a new endpoint with @ApiAccess(module = "bookings", ...) annotation.
  2. Code is committed and deployed through the TeamCity pipeline.
  3. On application startup, the sync process detects the new API.
  4. In dev/home lab (auto-activate = true): The API is registered and immediately active with default roles. Developer can use it right away.
  5. In production (auto-activate = false): The API is registered but inactive. It appears in the admin UI. An admin reviews it, adjusts roles if needed, and activates it.

Scenario 2: New Module Under Development

  1. Developer creates a new module (e.g., ai-planner) with multiple endpoints, all annotated.
  2. Code is deployed to all environments.
  3. In dev: Module is auto-released, all APIs auto-activated. Full functionality available for testing.
  4. In production: Module is registered but not released. None of its APIs are accessible, regardless of their individual is_active status. The module is invisible to production users.
  5. When ready to launch: Admin opens the admin UI, releases the module, reviews and activates its APIs with appropriate production roles.

Scenario 3: Restricting an Existing API in Production

  1. Admin opens the API Access Rules screen in the production admin UI.
  2. Finds the API (e.g., api.bookings.create), clicks Edit.
  3. Changes allowed_roles from AGENT, ADMIN to SENIOR_AGENT, ADMIN.
  4. Saves. The change is logged in the audit trail.
  5. Within 60 seconds (or immediately via force refresh), the new roles take effect.
  6. No redeployment needed.

Scenario 4: API Removed from Code

  1. Developer removes an endpoint from the codebase.
  2. On next deployment, the startup sync flags the API as stale (is_stale = true).
  3. The API appears with a ⚠️ warning in the admin UI.
  4. Admin reviews and either removes it from the database or leaves it (it's already inactive if it's not in code).

Scenario 5: Role Requirements Differ Between Environments

This is handled naturally — each environment's database has its own allowed_roles values. When a new API is registered with default roles via auto-activate, those defaults serve as a starting point. Production admins can adjust roles at any time through the admin UI without affecting other environments.

Access Resolution Flow

   Incoming API Request
         │
         ▼
   ┌─────────────┐
   │ Extract API  │  e.g., "api.bookings.create"
   │ name & role  │  e.g., role = "AGENT"
   └──────┬──────┘
          │
          ▼
   ┌─────────────────┐     No
   │ Module released? │──────────▶ 403 Forbidden
   │ (in-memory cache)│
   └──────┬──────────┘
          │ Yes
          ▼
   ┌─────────────────┐     No
   │  API is active?  │──────────▶ 403 Forbidden
   │ (in-memory cache)│
   └──────┬──────────┘
          │ Yes
          ▼
   ┌─────────────────┐     No
   │ Role in allowed  │──────────▶ 403 Forbidden
   │    roles list?   │
   └──────┬──────────┘
          │ Yes
          ▼
      200 OK / Proceed

Configuration Summary

Application Properties

# Environment identifier
app.environment = prod

# Auto-activation behavior
# true  = new modules released, new APIs activated on startup (dev)
# false = new modules/APIs registered but inactive on startup (prod, stage)
api.access.auto-activate = false

# Cache refresh interval in milliseconds
api.access.cache-refresh-ms = 60000

Per-Environment Settings

Property Dev (laptops, home lab) Test / Stage Production
app.environment dev stage prod
api.access.auto-activate true false false
api.access.cache-refresh-ms 10000 60000 60000

Integration with TeamCity Build Pipeline

The API access management system requires no special build steps — it is fully automated at application startup. However, the TeamCity pipeline can include an optional verification step:

Post-Deployment Verification (Optional)

After deploying the backend, add a build step that queries the admin API to report on unactivated APIs:

#!/bin/bash
# Check for new APIs pending activation in the target environment
RESPONSE=$(curl -s -H "Authorization: Bearer ${ADMIN_TOKEN}" \
    "https://${BACKEND_HOST}/admin/api-access/pending")

PENDING_COUNT=$(echo "$RESPONSE" | jq '.pending | length')

if [ "$PENDING_COUNT" -gt 0 ]; then
    echo "##teamcity[message text='${PENDING_COUNT} new APIs pending activation' status='WARNING']"
    echo "$RESPONSE" | jq -r '.pending[] | "  - \(.api_name) [\(.module_name)]"'
fi

This provides visibility in TeamCity when new APIs have been deployed but not yet activated in the target environment.

Security Considerations

  • The admin UI must be protected by the existing Keycloak/OAuth2 authentication with an ADMIN or SUPER_ADMIN role requirement.
  • The audit log table should be append-only — no UPDATE or DELETE permissions for the application user; use a separate DB role for audit log maintenance if cleanup is ever needed.
  • The force-refresh endpoint should be restricted to admin roles to prevent cache manipulation.
  • Database credentials for the access rules tables should follow the same security practices as other sensitive configuration (TeamCity password parameters, environment-specific secrets).