feat(storage): add PostgreSQL persistence foundation

[likun666661]

Context

The first OpenSandbox reconciliation slice intentionally stopped at the provider boundary. The repository currently has:

• a SandboxAttemptStore port used by the reconciler;
• in-memory test stores;
• a placeholder internal/store package;
• no PostgreSQL adapter, schema migrations, durable resource log, leases, or transactional outbox.

That was a valid first vertical slice, but it is not yet a durable cloud control plane. Process memory cannot be workflow truth: restart recovery, Activation deduplication, resource-version CAS, Session lease fencing, checkpoint commit, and reply publication all require authoritative persistence.

Architecture reference: docs/architecture-v1.md, especially the resource model, commit protocol, and Store/Watch sections.

Goal

Add the PostgreSQL persistence foundation required by the Maka Agent Cloud control plane, with explicit transactional invariants and repository ports that remain independent of gRPC transport and OpenSandbox.

Proposed schema

Start with versioned migrations for:

1. agents
2. cloud_sessions
3. activations
4. sandbox_attempts
5. bundle_revisions
6. session_leases
7. resource_changes
8. publication_outbox
9. schema_migrations

Invariant-critical identities and state should use typed columns. JSONB may be used for bounded extensible spec/status fields, but not as a substitute for database uniqueness, foreign keys, lease epochs, revisions, phases, or timestamps.

Required invariants

• Activation admission is idempotent with a unique (cloud_session_id, activation_id) key scoped to the tenant.
• Bundle revisions are immutable and unique by (cloud_session_id, revision).
• A Session has at most one current lease row; lease epochs increase monotonically.
• Resource mutations use opaque resource_version compare-and-swap semantics.
• Every successful resource transaction appends an ordered resource_changes entry.
• The checkpoint commit transaction atomically:
  • verifies the expected Session head;
  • verifies lease holder, Activation, and epoch;
  • inserts the next bundle_revision;
  • advances the Session head;
  • records the Activation outcome;
  • inserts the reply into publication_outbox.
• Outbox delivery is retryable and idempotent by Activation/destination identity.
• All authoritative queries and uniqueness constraints are tenant-scoped.
• No database transaction is held while calling OpenSandbox, object storage, model providers, or downstream messaging APIs.

Deliverables

• PostgreSQL configuration and lifecycle wiring.
• Repeatable forward migrations and a documented local migration workflow.
• Concrete repository implementations behind internal/store ports.
• Transaction helpers for CAS mutation, Session lease acquisition/renewal, ordered resource change append, and checkpoint/outbox commit.
• Integration tests against a real PostgreSQL instance or disposable test database.
• Concurrency tests covering stale resource versions, duplicate Activation admission, lease fencing, competing checkpoint commits, and outbox retries.
• Restart/recovery test proving a new process can resume durable non-terminal work.
• README updates describing local database setup and verification commands.

Suggested implementation order

1. Migration runner and PostgreSQL test harness.
2. Common metadata/resource-version conventions.
3. cloud_sessions, activations, and Activation deduplication.
4. sandbox_attempts repository for the existing reconciler.
5. session_leases and fencing.
6. bundle_revisions plus atomic Session-head commit.
7. resource_changes and resumable Watch cursor support.
8. publication_outbox and delivery claiming.
9. App composition, restart recovery, and end-to-end concurrency tests.

Acceptance criteria

• go test ./..., go vet ./..., and go build ./... pass.
• Migrations apply cleanly to an empty database and are safe to re-run through the migration tool.
• The existing Sandbox reconciler uses the PostgreSQL SandboxAttemptStore in application composition rather than only test fakes.
• Duplicate Activation admission returns the existing logical Activation.
• A stale resource_version or lease epoch cannot mutate or commit Session state.
• Exactly one competing checkpoint transaction advances the Session head.
• A committed reply always has an outbox row in the same transaction; an uncommitted candidate bundle never becomes publishable.
• Integration tests demonstrate recovery after process restart.

Non-goals

• Storing Session Bundle bytes in PostgreSQL; bytes belong in S3-compatible object storage.
• Redis as an authority or correctness dependency.
• Multi-provider scheduling, warm pools, preemption, or general Kubernetes-style cluster management.
• Complete public CloudSession/Activation gRPC APIs beyond what is needed to exercise persistence safely.