#Search and multimodel databases

The default application database remains Doctrine-managed SQL. Optional search, vector and graph providers have separate contracts and deployment switches. Enabling one capability does not migrate another owner's data.

#Provider roles and delivery status

SearchEngineInterface / SearchProviderInterface own lexical retrieval and capability metadata. VectorStoreInterface owns embedding projections. GraphStoreInterface owns tenant-bound node/edge operations. Canonical relational repositories retain Doctrine DBAL. ArcadeDB's native ArcadeDbClient is an infrastructure SQL/SQL-script API, not a substitute DBAL driver. Never pass visitor-authored SQL/scripts to it or expose it as an anonymous HTTP endpoint.

Vespa belongs behind retrieval/ranking ports: its matching and ranking model can combine text, nearest-neighbor retrieval and rank profiles. For ARCA, ArcadeDB can own canonical documents/relationships while Vespa owns rebuildable retrieval projections. This architecture is inferred from Vespa's hybrid search model. A future hybrid port must define typed filters, rank-profile identifiers and normalized results around an actual consumer; do not pass arbitrary YQL into application services or pretend Vespa supplies SQL transactions.

Supabase/pgvector will use the PostgreSQL connection boundary plus a separate vector adapter, versioned extension/index migrations, embedding identity, tenant/privacy predicates and RLS when exposed through Supabase APIs. Validate connection mode and prepared-statement support explicitly. Graph-shaped PostgreSQL data remains relational graph storage, not a native graph claim. See Supabase connections and vector columns.

#Modern search configuration

Automatic selection follows exactly OpenSearch → Typesense → Meilisearch → Elasticsearch, independent of profile declaration order. Only enabled profiles with an endpoint are candidates. An explicit modern.provider pins that provider for compatibility; auto or an omitted provider enables priority selection. Without profiles, modern search defaults to OpenSearch. search.engine itself still defaults to sql.

{
  "search": {
    "engine": "modern",
    "modern": {
      "provider": "auto",
      "enabled": true,
      "index": "products_v2",
      "index_per_tenant": true,
      "timeout_seconds": 5,
      "providers": {
        "opensearch": {
          "endpoint": "https://k0smos.example.com:9200",
          "auth": {
            "username_env": "K0SMOS_OPENSEARCH_USERNAME",
            "password_env": "K0SMOS_OPENSEARCH_PASSWORD"
          }
        }
      }
    },
    "fallback": {"enabled": true},
    "sql": {"table": "products"}
  }
}

The same resolver and authenticated transport feed catalog reads and writes. Profiles may override endpoint, index, per-tenant suffix, timeout and auth. Typesense and Meilisearch use auth.api_key_env (provider-specific API-key and Bearer headers respectively); Elasticsearch accepts Basic or ApiKey. OpenSearch supports Basic authentication in this adapter; AWS SigV4 is not implemented. Put credential values in the process environment/secret manager, never tenant JSON. Referenced missing secrets fail closed. Remote production endpoints should use HTTPS with a trusted certificate and least-privilege credentials. Redirects and automatic transport retries are disabled; requests have a 1-second connect ceiling, configurable total timeout up to 15 seconds, and an 8 MiB response bound.

The request context owns the tenant predicate even if query filters disagree. Invalid tenant identities fail before a remote query. OpenSearch requests exact hit totals and reports timeout, failed shards or nonexact totals as partial. Non-2xx responses and malformed hits envelopes throw, allowing the existing SQL fallback to run. Queries rejecting partial results retain that fallback behavior. A backend outage never selects another remote index automatically: the catalog projects to one selected provider, so another endpoint may contain stale data. Changing the selected provider requires a rebuild and explicit traffic cutover.

Catalog remote IDs are SHA-256 hashes of the JSON tuple [tenantId, documentId]; canonical product_id/locale remain in the payload and the SQL ledger keeps its owner-local identity. This prevents identical product/locale IDs in different tenants from overwriting or deleting each other even in a shared index. Bulk acknowledgements must cover each requested action/ID; concrete _index names may differ from the configured alias. Missing-document deletes are idempotent.

Upgrade from the previous ID format: pause modern-search traffic and projection workers; configure a fresh versioned index/collection, then run ecommerce:search:rebuild --force --batch-size=100 for every affected tenant. Keep search.engine=modern during rebuild because SQL selects the no-op writer. Verify tenant isolation, memberships, facets, sorting and deletions before resuming workers/traffic. Do not rebuild into an old index containing unscoped IDs: it would retain duplicate legacy documents. Retire that index only after all tenants using it have switched. Rollback selects canonical SQL or a verified previous provider/index; it must not delete another tenant's index.

The wire behavior follows OpenSearch's Search API and Bulk API.

#ArcadeDB setup and schema ownership

Provision the ArcadeDB database and credentials outside application requests. The delivered protocol was exercised on ArcadeDB 26.9.1. SQL compatibility is native ArcadeDB, even when a deployment also offers a PostgreSQL wire endpoint.

{
  "arcadedb": {
    "enabled": true,
    "endpoint": "http://127.0.0.1:2480",
    "database": "k0smos",
    "username_env": "K0SMOS_ARCADEDB_USERNAME",
    "password_env": "K0SMOS_ARCADEDB_PASSWORD",
    "timeout_seconds": 5
  },
  "search": {
    "vector": {
      "enabled": true,
      "provider": "arcadedb",
      "collection": "public_docs_v1",
      "allowed_sources": ["documentation:guide"],
      "embedding": {"provider": "feature_hash", "dimensions": 384}
    }
  }
}

Use HTTPS with trusted certificates for remote deployments. ArcadeDB credentials are mandatory when an operation runs. Service construction performs no network I/O, and ArcadeDB/vector enablement defaults to false. The legacy omitted-vector- provider default remains Qdrant for compatibility; new ArcadeDB deployments must name arcadedb explicitly. Its endpoint comes solely from the arcadedb block; search.vector.endpoint remains the Qdrant connection setting. Feature hash is an explicit lexical-vector proof, not a semantic model. Pinned Ollama embeddings remain available through the existing explicit scope; see vector search.

TENANT_ENV=k0smos.example.com php bin/console database:arcadedb:migrate --dry-run
TENANT_ENV=k0smos.example.com php bin/console database:arcadedb:migrate
TENANT_ENV=k0smos.example.com php bin/console search:vector:rebuild --source=documentation --batch-size=16

The dedicated command runs a Doctrine migration with metadata in the tenant SQL connection, in a table keyed by the remote endpoint/database identity. Ordinary migrate does not modify external databases. The remote migration lives under src/Infrastructure/Database/ArcadeDb/Migrations; it creates K0smosSchema, K0smosVector documents, K0smosNode vertices and K0smosRelation edges. Runtime readiness checks type kinds, property types, unique identity indexes and the supported schema marker without writing DDL. Existing incompatible schemas and newer versions are rejected. Remote DDL is additive and retryable, but it is not transactional. A failed migration may leave partial DDL; the marker is published only after structure verification.

After restoring or recreating the remote database at the same endpoint/name, use database:arcadedb:migrate --reconcile to reapply the supported additive migration through its dedicated Doctrine ledger. It checks compatibility first and refuses unknown/newer ledger versions. Back up canonical graph records with ArcadeDB's native tooling as well as the SQL ledger; SQL backups alone cannot recover them. There is no destructive down operation.

Graph methods bind tenant identity in DI, scope both edge endpoints and expose idempotent node/edge updates, lookup, deterministic outgoing relations (one hop, at most 100), and node/edge deletion. Native vertex deletion removes incident edges. Applications still own authorization, provenance and source freshness; no existing module is silently redirected from its SQL graph to this store.

Vector writes accept at most 100 documents per atomic SQL script and validate the whole batch before sending it. They store identities, versions/checksums, embeddings and scalar metadata, never source text. Queries enforce collection/tenant/model/dimensions/public/live/source/locale/metadata predicates before exact cosine scoring, then revalidate returned scope. Tombstones retain identity and remain excluded. Source versions are opaque: canonical owners must prevent stale jobs and validate freshness; the adapter does not invent an order for them. Model changes require a new collection and rebuild.

Exact cosine scans the scoped corpus; it is not an ANN/HNSW performance claim. Client time/response bounds do not certify server computation time after a client disconnect. Current online ArcadeDB documentation includes behavior newer than 26.9.1; ANN filtering and scale tuning require separate versioned acceptance. Reference protocols: HTTP API, SQL scripts, vector functions.

Discovery's immutable generations still use concrete Qdrant verification and physical-removal operations. Core ArcadeDB selection does not switch those generations. Generalizing that lifecycle and its SQL graph is a separate change; Discovery's public guided-input gate remains in effect.

#Reproducible acceptance

tools/benchmarks/search-databases-smoke.php requires explicit loopback endpoints and the four K0SMOS_ARCADEDB_* / K0SMOS_OPENSEARCH_* username/password environment variables above. It creates uniquely named disposable database/index resources, uses in-memory SQLite for Doctrine metadata and cleans up its owned resources. It never boots a tenant or opens a runtime tenant database.

php -d xdebug.mode=off tools/benchmarks/search-databases-smoke.php \
  --arcade-endpoint=http://127.0.0.1:24881 \
  --search-endpoint=http://127.0.0.1:19281

The 2026-10-04 acceptance ran against disposable ArcadeDB 26.9.1 and OpenSearch 3.3.2 containers with authentication enabled over loopback HTTP. It verifies migration preview/replay/reconciliation, graph updates and tenant isolation, vertex/edge deletion, cosine/privacy/model/locale/metadata/tombstone behavior, shared-index product identity, facets, alias writes/deletes and HTTP 401 rejection. This is functional compatibility evidence, not production-scale relevance, throughput, HA, TLS, cloud IAM or provider-version certification.