cameleer-server/docs/superpowers/plans/2026-04-16-persistent-route-catalog.md

Persistent Route Catalog Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Persist the route catalog in ClickHouse so routes (including sub-routes with zero executions) survive server restarts without requiring agent reconnection, and historical routes remain visible when the selected time window overlaps their lifecycle.

Architecture: A new route_catalog ClickHouse table (ReplacingMergeTree) keyed on (tenant_id, environment, application_id, route_id) with first_seen/last_seen timestamps. Written on agent registration and heartbeat. Read as a third source in both catalog controllers, merged into the existing routesByApp map. Cache-backed first_seen preservation follows the ClickHouseDiagramStore pattern.

Tech Stack: Java 17, Spring Boot, ClickHouse (JDBC), ConcurrentHashMap cache

Spec: docs/superpowers/specs/2026-04-16-persistent-route-catalog-design.md

---

Task 1: ClickHouse Schema

Files:

• Modify: cameleer-server-app/src/main/resources/clickhouse/init.sql

• Step 1: Add the route_catalog table to init.sql

Append before the closing of the file (after the usage_events table):

-- ── Route Catalog ──────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS route_catalog (
    tenant_id        LowCardinality(String) DEFAULT 'default',
    environment      LowCardinality(String) DEFAULT 'default',
    application_id   LowCardinality(String),
    route_id         LowCardinality(String),
    first_seen       DateTime64(3),
    last_seen        DateTime64(3)
)
ENGINE = ReplacingMergeTree(last_seen)
ORDER BY (tenant_id, environment, application_id, route_id);

• Step 2: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 3: Commit

git add cameleer-server-app/src/main/resources/clickhouse/init.sql
git commit -m "feat: add route_catalog table to ClickHouse schema"

---

Task 2: Core Interface and Record

Files:

• Create: cameleer-server-core/src/main/java/com/cameleer/server/core/storage/RouteCatalogEntry.java

• Create: cameleer-server-core/src/main/java/com/cameleer/server/core/storage/RouteCatalogStore.java

• Step 1: Create the RouteCatalogEntry record

package com.cameleer.server.core.storage;

import java.time.Instant;

public record RouteCatalogEntry(
        String applicationId,
        String routeId,
        String environment,
        Instant firstSeen,
        Instant lastSeen) {}

• Step 2: Create the RouteCatalogStore interface

package com.cameleer.server.core.storage;

import java.time.Instant;
import java.util.Collection;
import java.util.List;

public interface RouteCatalogStore {

    void upsert(String applicationId, String environment, Collection<String> routeIds);

    List<RouteCatalogEntry> findByEnvironment(String environment, Instant from, Instant to);

    List<RouteCatalogEntry> findAll(Instant from, Instant to);

    void deleteByApplication(String applicationId);
}

• Step 3: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 4: Commit

git add cameleer-server-core/src/main/java/com/cameleer/server/core/storage/RouteCatalogEntry.java \
       cameleer-server-core/src/main/java/com/cameleer/server/core/storage/RouteCatalogStore.java
git commit -m "feat: add RouteCatalogStore interface and RouteCatalogEntry record"

---

Task 3: ClickHouse Implementation

Files:

• Create: cameleer-server-app/src/main/java/com/cameleer/server/app/storage/ClickHouseRouteCatalogStore.java

• Step 1: Create the ClickHouseRouteCatalogStore class

Follow the ClickHouseDiagramStore pattern: constructor takes tenantId + JdbcTemplate, warm-loads a firstSeenCache on construction, provides upsert, findByEnvironment, findAll, deleteByApplication.

package com.cameleer.server.app.storage;

import com.cameleer.server.core.storage.RouteCatalogEntry;
import com.cameleer.server.core.storage.RouteCatalogStore;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Timestamp;
import java.time.Instant;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

public class ClickHouseRouteCatalogStore implements RouteCatalogStore {

    private static final Logger log = LoggerFactory.getLogger(ClickHouseRouteCatalogStore.class);

    private final String tenantId;
    private final JdbcTemplate jdbc;

    // key: environment + "\0" + application_id + "\0" + route_id -> first_seen
    private final ConcurrentHashMap<String, Instant> firstSeenCache = new ConcurrentHashMap<>();

    public ClickHouseRouteCatalogStore(String tenantId, JdbcTemplate jdbc) {
        this.tenantId = tenantId;
        this.jdbc = jdbc;
        warmLoadFirstSeenCache();
    }

    private void warmLoadFirstSeenCache() {
        try {
            jdbc.query(
                    "SELECT environment, application_id, route_id, first_seen " +
                            "FROM route_catalog FINAL WHERE tenant_id = ?",
                    rs -> {
                        String key = cacheKey(
                                rs.getString("environment"),
                                rs.getString("application_id"),
                                rs.getString("route_id"));
                        Timestamp ts = rs.getTimestamp("first_seen");
                        if (ts != null) {
                            firstSeenCache.put(key, ts.toInstant());
                        }
                    },
                    tenantId);
            log.info("Route catalog cache warmed: {} entries", firstSeenCache.size());
        } catch (Exception e) {
            log.warn("Failed to warm route catalog cache — first_seen values will default to now: {}", e.getMessage());
        }
    }

    private static String cacheKey(String environment, String applicationId, String routeId) {
        return environment + "\0" + applicationId + "\0" + routeId;
    }

    @Override
    public void upsert(String applicationId, String environment, Collection<String> routeIds) {
        if (routeIds == null || routeIds.isEmpty()) {
            return;
        }

        Instant now = Instant.now();
        Timestamp nowTs = Timestamp.from(now);

        for (String routeId : routeIds) {
            String key = cacheKey(environment, applicationId, routeId);
            Instant firstSeen = firstSeenCache.computeIfAbsent(key, k -> now);
            Timestamp firstSeenTs = Timestamp.from(firstSeen);

            try {
                jdbc.update(
                        "INSERT INTO route_catalog " +
                                "(tenant_id, environment, application_id, route_id, first_seen, last_seen) " +
                                "VALUES (?, ?, ?, ?, ?, ?)",
                        tenantId, environment, applicationId, routeId, firstSeenTs, nowTs);
            } catch (Exception e) {
                log.warn("Failed to upsert route catalog entry {}/{}: {}",
                        applicationId, routeId, e.getMessage());
            }
        }
    }

    @Override
    public List<RouteCatalogEntry> findByEnvironment(String environment, Instant from, Instant to) {
        return jdbc.query(
                "SELECT application_id, route_id, first_seen, last_seen " +
                        "FROM route_catalog FINAL " +
                        "WHERE tenant_id = ? AND environment = ? " +
                        "AND first_seen <= ? AND last_seen >= ?",
                (rs, rowNum) -> new RouteCatalogEntry(
                        rs.getString("application_id"),
                        rs.getString("route_id"),
                        environment,
                        rs.getTimestamp("first_seen").toInstant(),
                        rs.getTimestamp("last_seen").toInstant()),
                tenantId, environment,
                Timestamp.from(to), Timestamp.from(from));
    }

    @Override
    public List<RouteCatalogEntry> findAll(Instant from, Instant to) {
        return jdbc.query(
                "SELECT application_id, route_id, environment, first_seen, last_seen " +
                        "FROM route_catalog FINAL " +
                        "WHERE tenant_id = ? " +
                        "AND first_seen <= ? AND last_seen >= ?",
                (rs, rowNum) -> new RouteCatalogEntry(
                        rs.getString("application_id"),
                        rs.getString("route_id"),
                        rs.getString("environment"),
                        rs.getTimestamp("first_seen").toInstant(),
                        rs.getTimestamp("last_seen").toInstant()),
                tenantId,
                Timestamp.from(to), Timestamp.from(from));
    }

    @Override
    public void deleteByApplication(String applicationId) {
        // Remove from cache
        firstSeenCache.entrySet().removeIf(e -> {
            String[] parts = e.getKey().split("\0", 3);
            return parts.length == 3 && parts[1].equals(applicationId);
        });
    }
}

• Step 2: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 3: Commit

git add cameleer-server-app/src/main/java/com/cameleer/server/app/storage/ClickHouseRouteCatalogStore.java
git commit -m "feat: implement ClickHouseRouteCatalogStore with first_seen cache"

---

Task 4: Wire the Bean

Files:

• Modify: cameleer-server-app/src/main/java/com/cameleer/server/app/config/StorageBeanConfig.java

• Step 1: Add the import and bean method

Add import at the top of StorageBeanConfig.java:

import com.cameleer.server.app.storage.ClickHouseRouteCatalogStore;
import com.cameleer.server.core.storage.RouteCatalogStore;

Add a new bean method after the clickHouseDiagramStore bean (after line 146):

    // ── ClickHouse Route Catalog Store ───────────────────────────────

    @Bean
    public RouteCatalogStore clickHouseRouteCatalogStore(
            TenantProperties tenantProperties,
            @Qualifier("clickHouseJdbcTemplate") JdbcTemplate clickHouseJdbc) {
        return new ClickHouseRouteCatalogStore(tenantProperties.getId(), clickHouseJdbc);
    }

• Step 2: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 3: Commit

git add cameleer-server-app/src/main/java/com/cameleer/server/app/config/StorageBeanConfig.java
git commit -m "feat: wire ClickHouseRouteCatalogStore bean"

---

Task 5: Write Path — AgentRegistrationController

Files:

• Modify: cameleer-server-app/src/main/java/com/cameleer/server/app/controller/AgentRegistrationController.java

• Step 1: Add the RouteCatalogStore field and constructor parameter

Add import:

import com.cameleer.server.core.storage.RouteCatalogStore;

Add a field after the existing routeStateRegistry field (after line 70):

    private final RouteCatalogStore routeCatalogStore;

Add RouteCatalogStore routeCatalogStore as the last constructor parameter and assign it in the body:

    public AgentRegistrationController(AgentRegistryService registryService,
                                        AgentRegistryConfig config,
                                        BootstrapTokenValidator bootstrapTokenValidator,
                                        JwtService jwtService,
                                        Ed25519SigningService ed25519SigningService,
                                        AgentEventService agentEventService,
                                        AuditService auditService,
                                        @org.springframework.beans.factory.annotation.Qualifier("clickHouseJdbcTemplate") JdbcTemplate jdbc,
                                        RouteStateRegistry routeStateRegistry,
                                        RouteCatalogStore routeCatalogStore) {
        this.registryService = registryService;
        this.config = config;
        this.bootstrapTokenValidator = bootstrapTokenValidator;
        this.jwtService = jwtService;
        this.ed25519SigningService = ed25519SigningService;
        this.agentEventService = agentEventService;
        this.auditService = auditService;
        this.jdbc = jdbc;
        this.routeStateRegistry = routeStateRegistry;
        this.routeCatalogStore = routeCatalogStore;
    }

• Step 2: Add upsert call in the register method

After the registryService.register(...) call (after line 126), before the re-registration log, add:

        // Persist routes in catalog for server-restart recovery
        if (!routeIds.isEmpty()) {
            routeCatalogStore.upsert(application, environmentId, routeIds);
        }

• Step 3: Add upsert call in the heartbeat method

In the heartbeat method, after the routeStateRegistry update block (after line 261, before the final return), add:

        // Persist routes in catalog for server-restart recovery
        if (routeIds != null && !routeIds.isEmpty()) {
            AgentInfo agentForCatalog = registryService.findById(id);
            if (agentForCatalog != null) {
                String catalogEnv = agentForCatalog.environmentId();
                routeCatalogStore.upsert(agentForCatalog.applicationId(), catalogEnv, routeIds);
            }
        }

This handles both the normal heartbeat path and the auto-heal path (lines 230-248), since routeIds is extracted from routeStates at line 227-228 before the branch.

• Step 4: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 5: Commit

git add cameleer-server-app/src/main/java/com/cameleer/server/app/controller/AgentRegistrationController.java
git commit -m "feat: persist route catalog on agent register and heartbeat"

---

Task 6: Read Path — CatalogController

Files:

• Modify: cameleer-server-app/src/main/java/com/cameleer/server/app/controller/CatalogController.java

• Step 1: Add the RouteCatalogStore field and constructor parameter

Add imports:

import com.cameleer.server.core.storage.RouteCatalogStore;
import com.cameleer.server.core.storage.RouteCatalogEntry;

Add a field after tenantProperties (after line 49):

    private final RouteCatalogStore routeCatalogStore;

Add RouteCatalogStore routeCatalogStore as the last constructor parameter and assign it:

    public CatalogController(AgentRegistryService registryService,
                             DiagramStore diagramStore,
                             @org.springframework.beans.factory.annotation.Qualifier("clickHouseJdbcTemplate") JdbcTemplate jdbc,
                             RouteStateRegistry routeStateRegistry,
                             AppService appService,
                             EnvironmentService envService,
                             DeploymentRepository deploymentRepo,
                             TenantProperties tenantProperties,
                             RouteCatalogStore routeCatalogStore) {
        this.registryService = registryService;
        this.diagramStore = diagramStore;
        this.jdbc = jdbc;
        this.routeStateRegistry = routeStateRegistry;
        this.appService = appService;
        this.envService = envService;
        this.deploymentRepo = deploymentRepo;
        this.tenantProperties = tenantProperties;
        this.routeCatalogStore = routeCatalogStore;
    }

• Step 2: Add persistent catalog merge in getCatalog()

After the existing ClickHouse stats merge block (after line 155, // Merge ClickHouse routes into routesByApp), add:

        // Merge routes from persistent catalog (covers routes with 0 executions
        // and routes from previous app versions within the selected time window)
        try {
            List<RouteCatalogEntry> catalogEntries = (environment != null && !environment.isBlank())
                    ? routeCatalogStore.findByEnvironment(environment, rangeFrom, rangeTo)
                    : routeCatalogStore.findAll(rangeFrom, rangeTo);
            for (RouteCatalogEntry entry : catalogEntries) {
                routesByApp.computeIfAbsent(entry.applicationId(), k -> new LinkedHashSet<>())
                           .add(entry.routeId());
            }
        } catch (Exception e) {
            log.warn("Failed to query route catalog: {}", e.getMessage());
        }

• Step 3: Add route_catalog to the dismiss deletion list

In the deleteClickHouseData method (line 348), add "route_catalog" to the tablesWithAppId array:

        String[] tablesWithAppId = {
            "executions", "processor_executions", "route_diagrams", "agent_events",
            "stats_1m_app", "stats_1m_route", "stats_1m_processor_type", "stats_1m_processor",
            "stats_1m_processor_detail", "route_catalog"
        };

Also call routeCatalogStore.deleteByApplication(applicationId) inside dismissApplication(), after the deleteClickHouseData call (after line 333):

        routeCatalogStore.deleteByApplication(applicationId);

• Step 4: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 5: Commit

git add cameleer-server-app/src/main/java/com/cameleer/server/app/controller/CatalogController.java
git commit -m "feat: merge persistent route catalog into unified catalog endpoint"

---

Task 7: Read Path — RouteCatalogController

Files:

• Modify: cameleer-server-app/src/main/java/com/cameleer/server/app/controller/RouteCatalogController.java

• Step 1: Add the RouteCatalogStore field and constructor parameter

Add imports:

import com.cameleer.server.core.storage.RouteCatalogStore;
import com.cameleer.server.core.storage.RouteCatalogEntry;

Add a field after routeStateRegistry (after line 44):

    private final RouteCatalogStore routeCatalogStore;

Update the constructor to accept and assign it:

    public RouteCatalogController(AgentRegistryService registryService,
                                   DiagramStore diagramStore,
                                   @org.springframework.beans.factory.annotation.Qualifier("clickHouseJdbcTemplate") JdbcTemplate jdbc,
                                   RouteStateRegistry routeStateRegistry,
                                   RouteCatalogStore routeCatalogStore) {
        this.registryService = registryService;
        this.diagramStore = diagramStore;
        this.jdbc = jdbc;
        this.routeStateRegistry = routeStateRegistry;
        this.routeCatalogStore = routeCatalogStore;
    }

• Step 2: Add persistent catalog merge in getCatalog()

After the existing ClickHouse stats merge block (after line 123, // Merge route IDs from ClickHouse stats into routesByApp), add:

        // Merge routes from persistent catalog (covers routes with 0 executions
        // and routes from previous app versions within the selected time window)
        try {
            List<RouteCatalogEntry> catalogEntries = (environment != null && !environment.isBlank())
                    ? routeCatalogStore.findByEnvironment(environment, rangeFrom, rangeTo)
                    : routeCatalogStore.findAll(rangeFrom, rangeTo);
            for (RouteCatalogEntry entry : catalogEntries) {
                routesByApp.computeIfAbsent(entry.applicationId(), k -> new LinkedHashSet<>())
                           .add(entry.routeId());
            }
        } catch (Exception e) {
            log.warn("Failed to query route catalog: {}", e.getMessage());
        }

• Step 3: Verify the project compiles

Run: mvn clean compile -q Expected: BUILD SUCCESS

• Step 4: Commit

git add cameleer-server-app/src/main/java/com/cameleer/server/app/controller/RouteCatalogController.java
git commit -m "feat: merge persistent route catalog into legacy catalog endpoint"

---

Task 8: Update Rule Files

Files:

• Modify: .claude/rules/core-classes.md

• Modify: .claude/rules/app-classes.md

• Step 1: Update core-classes.md storage section

In the ## storage/ -- Storage abstractions section (line 52-56), update the interfaces line and add the new record:

Change line 54 from:

- `ExecutionStore`, `MetricsStore`, `MetricsQueryStore`, `StatsStore`, `DiagramStore`, `SearchIndex`, `LogIndex` — interfaces

to:

- `ExecutionStore`, `MetricsStore`, `MetricsQueryStore`, `StatsStore`, `DiagramStore`, `RouteCatalogStore`, `SearchIndex`, `LogIndex` — interfaces
- `RouteCatalogEntry` — record: applicationId, routeId, environment, firstSeen, lastSeen

• Step 2: Update app-classes.md ClickHouse stores section

In the ## storage/ -- ClickHouse stores section (line 72-77), add after the ClickHouseUsageTracker line:

- `ClickHouseRouteCatalogStore` — persistent route catalog with first_seen cache, warm-loaded on startup

• Step 3: Commit

git add .claude/rules/core-classes.md .claude/rules/app-classes.md
git commit -m "docs: update rule files with RouteCatalogStore classes"

---

Task 9: Full Build Verification

• Step 1: Run the full build

Run: mvn clean verify -q Expected: BUILD SUCCESS (all tests pass)

• Step 2: If the build fails, fix the issue and re-run

Common issues:

• Constructor parameter ordering mismatches in tests that instantiate controllers directly
• Missing import statements

After fixing, run mvn clean verify -q again to confirm.