File size: 24,006 Bytes
fa1fead
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
# smartdocs/vectorstore/pgvector_client.py
"""
WHY THIS EXISTS:
All vector embeddings and document records live in Supabase pgvector.
This client wraps every DB operation with per-user RLS context enforcement.

FIXES IN THIS VERSION:
  1. store_document now returns StoreDocumentResult(is_new, doc_id) β€” never ambiguous
  2. update_document_status wrapped in transaction β€” set_config(is_local=true)
     only works inside a transaction. Without it, RLS context was leaking.
  3. exclude_injection_risk actually applied in similarity_search WHERE clause
  4. document_exists added as an explicit, typed method
  5. get_document added for idempotency checks in the API layer
  6. store_chunks batch size lowered to 50 to avoid Supabase payload limits
"""

from __future__ import annotations

import logging
from dataclasses import dataclass
from datetime import datetime
from typing import Optional

import asyncpg
from pgvector.asyncpg import register_vector

from config.settings import get_settings

logger = logging.getLogger(__name__)
settings = get_settings()


# ── Return type for store_document ───────────────────────────────────────────

@dataclass
class StoreDocumentResult:
    """
    Unambiguous result from store_document.
    is_new=True  β†’ document was just inserted, doc_id is the new UUID
    is_new=False β†’ document already existed, doc_id is the existing UUID
    doc_id is ALWAYS the correct UUID to use downstream β€” never empty string.
    """
    is_new: bool
    doc_id: str


# ── Client ───────────────────────────────────────────────────────────────────

class PgVectorClient:
    """
    Async PostgreSQL client for SmartDocs vector operations.
    All queries enforce per-user Row Level Security via set_config.
    Every method that writes or reads user data must call _set_user_context
    inside an explicit transaction.
    """

    def __init__(self) -> None:
        self._pool: Optional[asyncpg.Pool] = None

    async def connect(self) -> None:
        """Opens the connection pool. Call once at app startup."""
        try:
            self._pool = await asyncpg.create_pool(
                dsn=settings.database_url,
                min_size=2,
                max_size=10,
                ssl="require",
                # statement_cache_size=0 required for pgvector prepared statements
                statement_cache_size=0,
                server_settings={
                    "search_path": "public",
                    "application_name": "smartdocs",
                },
                init=self._init_connection,
            )
            logger.info("[pgvector] Connection pool opened (min=2, max=10)")
        except Exception as e:
            logger.exception(f"[pgvector] Failed to open connection pool: {e}")
            raise

    @staticmethod
    async def _init_connection(conn: asyncpg.Connection) -> None:
        """Registers the pgvector type on every new connection."""
        await register_vector(conn)

    async def close(self) -> None:
        """Closes the connection pool. Call at app shutdown."""
        if self._pool:
            await self._pool.close()
            logger.info("[pgvector] Connection pool closed")

    def _require_pool(self) -> asyncpg.Pool:
        if self._pool is None:
            raise RuntimeError(
                "PgVectorClient.connect() was never called. "
                "Call await client.connect() before any query."
            )
        return self._pool

    @staticmethod
    async def _set_user_context(conn: asyncpg.Connection, user_id: str) -> None:
        """
        Sets RLS context for the current transaction.
        MUST be called inside an explicit transaction (async with conn.transaction()).
        set_config with is_local=true is scoped to the current transaction.
        Outside a transaction, it only applies to the SET statement itself β€”
        the subsequent query runs without user context = silent RLS block.
        """
        await conn.execute(
            "SELECT set_config('app.current_user_id', $1, true)",
            str(user_id),
        )

    # ── Documents ─────────────────────────────────────────────────────────────

    async def store_document(
        self,
        doc_id: str,
        user_id: str,
        doc_hash: str,
        title: str,
        file_name: str,
        file_path: str,
        primary_language: str,
        doc_type: str,
    ) -> StoreDocumentResult:
        """
        Inserts a document record or returns the existing one.

        Returns StoreDocumentResult:
          is_new=True  β†’ freshly inserted, doc_id is the UUID we just stored
          is_new=False β†’ duplicate found (same doc_hash + user_id),
                         doc_id is the EXISTING UUID from the database

        The caller must always use result.doc_id, not the doc_id they passed in,
        because on duplicates the existing UUID is different from the new one generated.
        """
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)

                    # Check for existing document by content hash + user
                    existing_id: Optional[str] = await conn.fetchval(
                        """
                        SELECT doc_id::text
                        FROM documents
                        WHERE doc_hash = $1
                          AND user_id = $2
                        LIMIT 1
                        """,
                        doc_hash,
                        str(user_id),
                    )

                    if existing_id:
                        logger.debug(
                            f"[pgvector] Duplicate doc detected: hash={doc_hash[:8]}... "
                            f"existing_id={existing_id}"
                        )
                        return StoreDocumentResult(is_new=False, doc_id=existing_id)

                    await conn.execute(
                        """
                        INSERT INTO documents (
                            doc_id, user_id, doc_hash, title, file_name,
                            file_path, primary_language, doc_type,
                            total_chunks, ingestion_status, created_at, updated_at
                        ) VALUES (
                            $1::uuid, $2, $3, $4, $5,
                            $6, $7, $8,
                            0, 'pending', NOW(), NOW()
                        )
                        """,
                        doc_id,
                        str(user_id),
                        doc_hash,
                        title,
                        file_name,
                        file_path,
                        primary_language,
                        doc_type,
                    )

                    logger.debug(
                        f"[pgvector] Document inserted: doc_id={doc_id} user={user_id}"
                    )
                    return StoreDocumentResult(is_new=True, doc_id=doc_id)

        except Exception as e:
            logger.exception(f"[pgvector] store_document failed for doc_id={doc_id}: {e}")
            raise

    async def document_exists(self, doc_hash: str, user_id: str) -> Optional[str]:
        """
        Checks if a document with this hash already exists for this user.

        Returns:
          str  β†’ the existing doc_id (UUID as text) if found
          None β†’ document does not exist
        """
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)
                    result: Optional[str] = await conn.fetchval(
                        """
                        SELECT doc_id::text
                        FROM documents
                        WHERE doc_hash = $1
                          AND user_id = $2
                        LIMIT 1
                        """,
                        doc_hash,
                        str(user_id),
                    )
                    return result
        except Exception as e:
            logger.exception(f"[pgvector] document_exists check failed: {e}")
            return None

    async def get_document(self, doc_id: str, user_id: str) -> Optional[dict]:
        """
        Fetches a single document record by doc_id.
        Used by the API layer to return document metadata after upload.
        """
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)
                    row = await conn.fetchrow(
                        """
                        SELECT
                            doc_id::text,
                            user_id,
                            title,
                            file_name,
                            primary_language,
                            doc_type,
                            total_chunks,
                            ingestion_status,
                            error_message,
                            created_at
                        FROM documents
                        WHERE doc_id::uuid = $1::uuid
                          AND user_id = $2
                        """,
                        doc_id,
                        str(user_id),
                    )
                    return dict(row) if row else None
        except Exception as e:
            logger.exception(f"[pgvector] get_document failed for doc_id={doc_id}: {e}")
            return None

    async def update_document_status(
        self,
        doc_id: str,
        user_id: str,
        status: str,
        error_msg: Optional[str] = None,
        total_chunks: int = 0,
    ) -> None:
        """
        Updates ingestion_status and total_chunks on a document record.

        Called at two points:
          1. After successful chunk storage β†’ status='completed', total_chunks=N
          2. In the error handler β†’ status='failed', error_msg=str(e)

        BUG FIXED: Wrapped in explicit transaction so _set_user_context works.
        Without the transaction, set_config(is_local=true) only applied to the
        SET statement, not the UPDATE β€” the UPDATE ran without RLS context.
        """
        if not doc_id:
            logger.warning("[pgvector] update_document_status called with empty doc_id β€” skipping")
            return

        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():            # ← CRITICAL: must be inside transaction
                    await self._set_user_context(conn, user_id)
                    await conn.execute(
                        """
                        UPDATE documents
                        SET
                            ingestion_status = $1,
                            error_message    = $2,
                            total_chunks     = $3,
                            updated_at       = NOW()
                        WHERE doc_id::uuid = $4::uuid
                          AND user_id = $5
                        """,
                        status,
                        error_msg,
                        total_chunks,
                        doc_id,
                        str(user_id),
                    )
                    logger.debug(
                        f"[pgvector] Document status updated: doc_id={doc_id} "
                        f"status={status} chunks={total_chunks}"
                    )
        except Exception as e:
            logger.exception(
                f"[pgvector] update_document_status FAILED for doc_id={doc_id}: {e}"
            )
            # Do NOT re-raise β€” this is called from error handlers.
            # A failure here must never suppress the original exception.

    # ── Chunks ────────────────────────────────────────────────────────────────

    async def store_chunks(
        self,
        chunks: list[dict],
        embeddings: list[list[float]],
        user_id: str,
    ) -> int:
        """
        Stores chunk records with their embeddings in document_chunks.

        Column mapping is strict β€” matches schema.sql exactly.
        ON CONFLICT (chunk_id) DO NOTHING is safe because:
          - chunk_ids are UUIDs generated fresh per ingestion call
          - duplicate documents are caught by doc_hash BEFORE reaching this method
          - the only conflict case is a crash-and-retry, which is correct to skip

        Returns:
          int β†’ number of chunk records passed to the DB
                (ON CONFLICT skips are extremely rare β€” count is effectively accurate)
        """
        if len(chunks) != len(embeddings):
            raise ValueError(
                f"chunks ({len(chunks)}) and embeddings ({len(embeddings)}) must have equal length"
            )

        if not chunks:
            logger.warning("[pgvector] store_chunks called with empty chunk list")
            return 0

        pool = self._require_pool()
        stored = 0
        # 50 per batch β€” keeps Supabase payload under limits and aids debugging
        BATCH_SIZE = 50

        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)

                    for batch_start in range(0, len(chunks), BATCH_SIZE):
                        batch_chunks = chunks[batch_start : batch_start + BATCH_SIZE]
                        batch_embeddings = embeddings[batch_start : batch_start + BATCH_SIZE]

                        records = []
                        for chunk, emb in zip(batch_chunks, batch_embeddings):
                            records.append((
                                str(chunk.get("chunk_id", "")),                      # $1  TEXT
                                str(chunk.get("doc_id", "")),                        # $2  UUID
                                str(user_id),                                        # $3  TEXT
                                str(chunk.get("source", "unknown")),                 # $4  TEXT
                                str(chunk.get("title", "document")),                 # $5  TEXT
                                str(chunk.get("doc_primary_language", "en")),        # $6  TEXT
                                str(chunk.get("chunk_language", "en")),              # $7  TEXT
                                str(chunk.get("script_type", "latin")),              # $8  TEXT
                                str(chunk.get("doc_type", "other")),                 # $9  TEXT
                                int(chunk.get("page_number", 0)),                    # $10 INTEGER
                                str(chunk.get("section_heading", "")),               # $11 TEXT
                                int(chunk.get("chunk_index", 0)),                    # $12 INTEGER
                                int(chunk.get("total_chunks", 0)),                   # $13 INTEGER
                                str(chunk.get("parent_chunk_id", "")),               # $14 TEXT
                                bool(chunk.get("is_parent", False)),                 # $15 BOOLEAN
                                emb.tolist() if hasattr(emb, 'tolist') else list(emb),   # $16 vector(1024)                                                # $16 vector(1024)
                                str(chunk.get("content", chunk.get("text", ""))),   # $17 TEXT chunk_text
                                bool(chunk.get("injection_risk", False)),            # $18 BOOLEAN
                                bool(chunk.get("pii_detected", False)),              # $19 BOOLEAN
                                str(chunk.get("doc_hash", "")),                      # $20 TEXT
                            ))

                        await conn.executemany(
                            """
                            INSERT INTO document_chunks (
                                chunk_id, doc_id, user_id, source, title,
                                doc_primary_language, chunk_language, script_type, doc_type,
                                page_number, section_heading, chunk_index, total_chunks,
                                parent_chunk_id, is_parent, embedding, chunk_text,
                                injection_risk, pii_detected, doc_hash, created_at
                            ) VALUES (
                                $1, $2::uuid, $3, $4, $5,
                                $6, $7, $8, $9,
                                $10, $11, $12, $13,
                                $14, $15, $16::vector, $17,
                                $18, $19, $20, NOW()
                            )
                            ON CONFLICT (chunk_id) DO NOTHING
                            """,
                            records,
                        )
                        stored += len(batch_chunks)
                        logger.debug(
                            f"[pgvector] Stored batch {batch_start}–{batch_start + len(batch_chunks)}"
                        )

            logger.info(f"[pgvector] store_chunks complete: {stored} chunks stored for user={user_id}")
            return stored

        except Exception as e:
            logger.exception(f"[pgvector] store_chunks FAILED at batch starting {stored}: {e}")
            raise

    # ── Retrieval ─────────────────────────────────────────────────────────────

    async def similarity_search(
        self,
        query_embedding: list[float],
        user_id: str,
        doc_id: Optional[str] = None,
        top_k: int = 20,
        exclude_injection_risk: bool = True,
    ) -> list[dict]:
        """
        Cosine similarity search within a user's document chunks.

        BUG FIXED: exclude_injection_risk is now actually applied.
        Previously the parameter was declared but the WHERE clause never used it.
        Injection-flagged chunks were being returned and passed to Sarvam-30B.
        """
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)

                    conditions = ["user_id = $2"]
                    params: list = [query_embedding, str(user_id)]

                    if doc_id:
                        params.append(str(doc_id))
                        conditions.append(f"doc_id::uuid = ${len(params)}::uuid")

                    if exclude_injection_risk:
                        conditions.append("injection_risk = FALSE")

                    where_clause = " AND ".join(conditions)
                    params.append(top_k)
                    limit_param = f"${len(params)}"

                    query = f"""
                        SELECT
                            chunk_id::text            AS chunk_id,
                            doc_id::text              AS doc_id,
                            chunk_text,
                            title,
                            page_number,
                            chunk_index,
                            chunk_language,
                            script_type,
                            doc_primary_language,
                            doc_type,
                            section_heading,
                            parent_chunk_id,
                            injection_risk,
                            pii_detected,
                            1 - (embedding <=> $1::vector) AS similarity
                        FROM document_chunks
                        WHERE {where_clause}
                        ORDER BY embedding <=> $1::vector
                        LIMIT {limit_param}
                    """

                    rows = await conn.fetch(query, *params)
                    results = [dict(r) for r in rows]

                    logger.debug(
                        f"[pgvector] similarity_search: returned {len(results)} chunks "
                        f"(top_k={top_k}, doc_id={doc_id}, exclude_injection={exclude_injection_risk})"
                    )
                    return results

        except Exception as e:
            logger.exception(f"[pgvector] similarity_search FAILED: {e}")
            return []

    async def list_user_documents(self, user_id: str) -> list[dict]:
        """Returns all documents for a user, ordered by upload time desc."""
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)
                    rows = await conn.fetch(
                        """
                        SELECT
                            doc_id::text,
                            title,
                            file_name,
                            primary_language,
                            doc_type,
                            total_chunks,
                            ingestion_status,
                            error_message,
                            created_at
                        FROM documents
                        WHERE user_id = $1
                        ORDER BY created_at DESC
                        """,
                        str(user_id),
                    )
                    return [dict(r) for r in rows]
        except Exception as e:
            logger.exception(f"[pgvector] list_user_documents FAILED: {e}")
            return []

    async def delete_document(self, doc_id: str, user_id: str) -> bool:
        """
        Deletes a document and all its chunks (CASCADE in schema).
        Returns True if deleted, False if not found.
        """
        pool = self._require_pool()
        try:
            async with pool.acquire() as conn:
                async with conn.transaction():
                    await self._set_user_context(conn, user_id)
                    result = await conn.execute(
                        """
                        DELETE FROM documents
                        WHERE doc_id::uuid = $1::uuid
                          AND user_id = $2
                        """,
                        doc_id,
                        str(user_id),
                    )
                    deleted = result == "DELETE 1"
                    if deleted:
                        logger.info(f"[pgvector] Deleted document doc_id={doc_id}")
                    return deleted
        except Exception as e:
            logger.exception(f"[pgvector] delete_document FAILED: {e}")
            return False


# ── Singleton ────────────────────────────────────────────────────────────────

_client: Optional[PgVectorClient] = None


def get_pgvector_client() -> PgVectorClient:
    """
    Returns the shared PgVectorClient singleton.
    The pool is created lazily on first connect() call.
    Call await get_pgvector_client().connect() once at startup.
    """
    global _client
    if _client is None:
        _client = PgVectorClient()
    return _client