feat(cloud): add standalone DB-backed admin policy for tokens and projects

[commonriskpro]

Pre-flight Checks

• I have searched existing issues and this is not an exact duplicate
• I understand this issue needs status:approved before a PR can be opened

Problem Description

Engram Cloud currently treats token and project administration as deploy-time configuration:

• ENGRAM_CLOUD_TOKEN is read from the environment at server startup.
• ENGRAM_CLOUD_ALLOWED_PROJECTS is read from the environment at server startup.
• Adding or rotating a sync token requires editing environment/configuration and restarting or recreating the container.
• Allowing or denying a project requires editing ENGRAM_CLOUD_ALLOWED_PROJECTS and restarting or recreating the container.
• The local engram cloud enroll <project> state and the server-side allowed-project policy are managed through separate workflows, which makes the operational model feel manual and fragile.

This is painful for standalone/self-hosted cloud deployments. Operators should not need to treat Docker environment variables as the admin UI for routine token and project policy changes.

The desired improvement is not a SaaS identity system. Engram should remain local-first and standalone, but cloud policy should be administrable at runtime.

Proposed Solution

Add a standalone DB-backed cloud admin policy layer for tokens and projects.

High-level behavior:

1. Store cloud admin/sync tokens in the cloud database.
2. Store only secure token hashes, never plaintext tokens.
3. Return generated token plaintext only once at creation time.
4. Store project sync policy in the cloud database using explicit allowed / denied project rows.
5. Keep ENGRAM_CLOUD_TOKEN as a bootstrap seed for the first active admin-capable token.
6. Keep ENGRAM_CLOUD_ALLOWED_PROJECTS as a bootstrap seed for the initial project policy.
7. After initial seeding, routine administration happens through CLI commands, not by editing env vars.
8. Server auth and project policy checks read current DB state per request, or through an explicitly safe refresh strategy, so changes take effect without Docker/server restart.
9. Preserve the existing local-first model: local enrollment remains a local decision; cloud policy remains server-side enforcement.
10. Preserve loud failure behavior: denied/unknown projects should return an explicit policy error rather than silently dropping data.

Suggested CLI surface:

engram cloud token add --label "laptop"
engram cloud token list
engram cloud token revoke --id 123

engram cloud project allow my-project
engram cloud project deny my-project
engram cloud project list

Suggested server-side data model:

• cloud_admin_tokens
  • id
  • prefix
  • label
  • token_hash
  • created_at
  • revoked_at
  • last_used_at
• cloud_allowed_projects
  • project
  • status (allowed / denied)
  • created_at
  • updated_at
  • updated_by

Acceptance Criteria

• A cloud operator can create a new token without restarting the cloud server/container.
• A cloud operator can revoke a token without restarting the cloud server/container.
• A cloud operator can allow or deny a project without restarting the cloud server/container.
• New tokens are shown in plaintext only once and are persisted only as secure hashes.
• Token list output never reveals plaintext token values.
• Revoked tokens fail with an explicit auth error.
• Missing/invalid tokens fail with explicit auth errors.
• Unknown or denied projects fail with an explicit server-side policy error.
• ENGRAM_CLOUD_TOKEN remains backward-compatible as a bootstrap seed when the DB has no active token rows.
• ENGRAM_CLOUD_ALLOWED_PROJECTS remains backward-compatible as a bootstrap seed when the DB has no project policy rows.
• Once DB rows exist, runtime administration happens through the DB-backed policy layer instead of requiring env var edits.
• Existing sync payload formats and /sync/* route shapes remain unchanged.
• Existing local enrollment preflight behavior remains intact.
• Documentation and compose examples explain bootstrap-vs-runtime semantics clearly.

Non-Goals

This issue intentionally does not propose:

• organizations
• managed users
• invitations
• username/password login
• RBAC roles
• SSO / external identity providers
• billing / tenant management
• a SaaS-style control plane
• changing sync payload formats
• changing the core chunk/mutation sync protocol

The goal is a standalone operational improvement: make token and project policy manageable without Docker restarts, while keeping Engram's local-first and single-binary philosophy intact.

Affected Area

Sync (multi-instance)

Alternatives Considered

1. Hot reload for environment variables

The server could reload ENGRAM_CLOUD_TOKEN and ENGRAM_CLOUD_ALLOWED_PROJECTS without restart.

This removes the immediate restart pain, but keeps Docker/env vars as the admin interface. It is still manual, hard to audit, and awkward for normal operations.

2. Wildcard allowlist

Issue #260 proposes wildcard support for ENGRAM_CLOUD_ALLOWED_PROJECTS.

That helps local development, but it weakens project policy rather than making policy easier to administer. It also does not solve token creation/revocation.

3. Project-scoped bearer tokens

Issue #274 proposes project-scoped bearer tokens. That is related and in scope, but still needs a concrete persistence/administration model.

This issue proposes a smaller standalone foundation: DB-backed token and project policy administration first.

4. Managed users / per-user sync tokens

Issue #348 proposes managed users, per-user tokens, dashboard login, and project grants.

That may be useful later, but it is intentionally larger than this issue. For standalone/self-hosted deployments, token/project policy in DB is enough to remove the operational pain without building an identity platform.

Related Issues / PRs

• Related: feat(cloud): support project-scoped bearer tokens for multi-project teams #274 — project-scoped bearer tokens for multi-project teams.
• Related but broader: feat(cloud): replace global cloud tokens with managed users and per-user sync tokens #348 — managed users and per-user sync tokens.
• Related pain point / alternative: feat(cloud): add wildcard support to ENGRAM_CLOUD_ALLOWED_PROJECTS for local development #260 — wildcard allowed projects for local development.
• Related security hardening: fix(cloud/auth): use constant-time compare for bearer token #350 and PR fix(cloud): constant-time credential comparisons (#350) #399 — constant-time comparisons for legacy/env/dashboard token paths.

Additional Context

This issue is meant to be reviewable as a focused cloud administration improvement.

A possible implementation can be split into small PRs:

1. Storage foundation

   • Add token and project policy tables.
   • Add cloudstore CRUD.
   • Add live DB tests.

2. Auth/bootstrap

   • Add token generation, validation, revocation behavior.
   • Seed from ENGRAM_CLOUD_TOKEN when DB has no active tokens.
   • Seed project policy from ENGRAM_CLOUD_ALLOWED_PROJECTS when policy table is empty.

3. Server enforcement

   • Route auth and project policy checks through DB-backed policy.
   • Return structured auth/policy errors.
   • Preserve existing sync contracts.

4. CLI admin

   • Add token and project commands.
   • Ensure changes take effect on the next request without restart.

5. Docs and compose examples

   • Explain first-start bootstrap seeds.
   • Explain runtime CLI administration.
   • Clarify that env vars are no longer the ongoing admin surface.

This keeps the work modular and avoids turning Engram Cloud into a SaaS identity platform.

[commonriskpro]

Thanks — I opened this issue because the existing related issues were close, but not an exact match for the standalone scope described here.

I also prepared a proof-of-approach implementation in a fork so the proposal can be reviewed against concrete code rather than only prose:

• Fork: https://github.com/commonriskpro/engram-fork
• Branch: https://github.com/commonriskpro/engram-fork/tree/feat/standalone-cloud-admin-policy

The implementation is intentionally scoped to standalone DB-backed cloud policy:

• DB-backed admin/sync tokens with hash-only persistence
• DB-backed project allow/deny policy
• ENGRAM_CLOUD_TOKEN / ENGRAM_CLOUD_ALLOWED_PROJECTS as bootstrap seeds
• CLI admin surface for token/project management
• server-side policy enforcement without Docker/server restart for routine changes
• docs/compose updates

It deliberately does not add orgs, users, invites, RBAC, SSO, or a SaaS-style control plane.

Important note: this is not meant as a request to merge as-is. If this issue is approved and the direction is welcome, the proof-of-approach should get a second technical review before any upstream PR is opened or merged. In particular, it should be reviewed for:

• fit with the maintainer's intended cloud auth direction
• interaction with fix(cloud/auth): use constant-time compare for bearer token #350 / PR fix(cloud): constant-time credential comparisons (#350) #399 around legacy/dashboard token comparison hardening
• whether the implementation should be split into chained PRs
• API/CLI naming and error-code semantics
• rebase against current upstream main
• full upstream CI validation

So the fork is mainly there as concrete design evidence and a starting point for discussion, not as pressure to bypass the issue-first review process.

[Alan-TheGentleman]

Good direction, but this touches the cloud token and admin architecture (new tables, CLI verbs, server-side enforcement), so it needs a design decision before a PR. Propose the contract here: what does the admin policy model look like, how do tokens rotate without a restart, and how does it stay backward-compatible with the env-based setup? This also overlaps the auth-model discussion in #348 and #349, so it is worth converging them. Marking this as design-gated, not rejected.