[FEATURE] Drop SQLite — PostgreSQL-only database backend

[iplay88keys]

📋 Prerequisites

• I have searched the existing issues to avoid creating a duplicate
• By submitting this issue, you agree to follow our Code of Conduct

📝 Feature Summary

Drop SQLite as a supported database backend and make PostgreSQL the only supported option, with a bundled PostgreSQL instance in the Helm chart for zero-config installs.

❓ Problem Statement / Motivation

• SQLite has no pgvector support, requiring a separate code path for memory/vector search
• Single-writer constraint prevents horizontal scaling of the controller
• Divergent SQL dialects require maintaining two code paths (JSON_EXTRACT vs JSONB operators, F32_BLOB vs vector(N), SQLite-specific delete workarounds)
• In-memory SQLite unit tests mask Postgres bugs (a pre-existing SearchCrewAIMemoryByTask bug using MySQL-syntax JSON_EXTRACT is hidden behind SQLite tests)
• PostgreSQL is already the recommended production backend — SQLite is only used as a convenience default that causes more problems than it solves

💡 Proposed Solution

• Remove all SQLite code paths from manager.go and client.go
• Fix the pre-existing SearchCrewAIMemoryByTask bug exposed by the removal (replace JSON_EXTRACT with Postgres JSONB operators)
• Remove --database-type and --sqlite-database-path flags; retain --database-vector-enabled (default true) so external PostgreSQL users without pgvector can opt out without a hard crash at startup
• Replace in-memory SQLite unit tests with testcontainers-go (pgvector/pgvector:pg18-trixie)
• Remove glebarez/sqlite and turso.tech/tursogo from go.mod
• Bundle PostgreSQL in the Helm chart (helm/kagent/templates/postgresql.yaml) — deployed automatically when database.postgres.url is empty (the default); set url to use an external PostgreSQL instead
• Simplify CI E2E — remove the database: [sqlite, postgres] matrix and separate Postgres service container; postgres is deployed inside the Kind cluster by make helm-install

🔄 Alternatives Considered

• Keep SQLite as default, promote Postgres — rejected; maintaining two backends indefinitely has an ongoing cost and the divergent test coverage creates a false sense of correctness
• Bitnami postgresql subchart — considered for bundling postgres; rejected as too heavy for a dev dependency and ties the chart to an external release cycle
• Separate kagent-postgres chart — considered; rejected as it adds install complexity without meaningful benefit

🎯 Affected Service(s)

Multiple services / System-wide

📚 Additional Context

No response

🙋 Are you willing to contribute?

• I am willing to submit a PR for this feature

[iplay88keys]

Follow-up in: #1527