Improve data integrity and reliability of ingestion

[BigSimmo]

Problem

• The ingestion job re‑queue endpoint uses a complex conditional WHERE clause to avoid resetting jobs that are still locked. If a worker crashes without clearing its lock, the lock expires based on WORKER_STALE_AFTER_MINUTES. Under heavy load, race conditions or stale locks could leave jobs stuck indefinitely.
• The import script caches existing documents by content hash and warns if more than 5 000 documents match. It only compares names and hashes in memory; identical file names with different content may slip through or legitimate differences may be flagged.
• The Supabase schema uses vector(1536) columns and warns that any change to EMBEDDING_DIMENSIONS or the OpenAI embedding model must be synchronised across migrations and worker environment variables. Misconfiguration will cause runtime failures.

Suggestions

• Use Postgres advisory locks or a heartbeat mechanism so workers can renew their locks periodically. This provides stronger guarantees that stale locks are cleaned up.
• Create a unique constraint on (owner_id, content_hash) to enforce deduplication at the database level. Remove reliance on in‑memory caches for duplicate detection.
• Add automated CI checks that validate embedding‑dimension consistency between the schema, migrations and worker configuration. Provide a migration script to alter vector dimensions safely when upgrading models.