state-pg: support migration-owned schema and renew expired setIfNotExists entries

[collinschaafsma]

Chat SDK 4.34.0 state-pg currently creates all five state tables during every connect. Applications with migration-owned production schemas need a supported way to connect without runtime DDL.

Proposed option:

• Add autoCreateSchema?: boolean to both Postgres state option shapes.
• Default it to true for backwards compatibility.
• When false, connect should run the connectivity check but skip ensureSchema.

There is also a TTL correctness issue in setIfNotExists. The implementation uses ON CONFLICT DO NOTHING, so an expired cache row continues returning false forever unless a separate get happens to delete it first. Chat message dedupe calls setIfNotExists directly, which can make a provider message id remain deduped beyond dedupeTtlMs.

Proposed query behavior: on conflict, replace the row only when its existing expires_at is non-null and less than or equal to now, then return the cache key. Live non-expired and non-expiring entries should continue returning false.

I have a narrow local package patch and Postgres contract coverage for both behaviors and can open a PR if this direction is acceptable.

[estrany]

+1, we hit this too.

There's also a permissions problem: because ensureSchema() runs on every connect(), the app's DB role needs CREATE on the schema forever. Our migrations already create these tables, so the runtime DDL never does anything, but we still can't revoke CREATE from the role. And if someone does revoke it, everything looks fine until a cold start calls connect() and the bot stops working.

autoCreateSchema: false would fix this for us.