> ## Documentation Index
> Fetch the complete documentation index at: https://docs.searchcompany.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Operations

> Internal database helper functions for visibility tracking

# Internal Service: database\_operations

Handles all database interactions for visibility results and scores. Uses `entity_prompts_tracker` (combined prompts + visibility) and `visibility_score_history` tables.

## Functions

### get\_entity\_id

Resolves a business/product to its database entity ID.

```python theme={null}
async def get_entity_id(
    business_id: str,
    product_id: Optional[str] = None
) -> Optional[str]
```

| Parameter     | Type | Description           |
| ------------- | ---- | --------------------- |
| `business_id` | str  | Clerk org slug        |
| `product_id`  | str  | Optional product slug |

***

### get\_business\_info

Fetches business/product information for visibility checks.

```python theme={null}
async def get_business_info(
    business_id: str,
    product_id: Optional[str] = None
) -> dict
```

**Returns:**

```python theme={null}
{
    "business_name": "...",
    "business_url": "...",
    "product_name": "..."  # Only if product_id provided
}
```

***

### update\_prompt\_visibility

Updates visibility results for a single prompt.

```python theme={null}
async def update_prompt_visibility(
    entity_id: str,
    prompt: str,
    visibility_results: dict
) -> bool
```

Updates columns: `chatgpt`, `claude`, `gemini`, `perplexity`, `copilot`, `deepseek`, `grok`, `google_ai`, `checked_at`, `last_tested_at`

***

### batch\_update\_visibility\_results

Updates visibility results for multiple prompts in parallel.

```python theme={null}
async def batch_update_visibility_results(
    entity_id: str,
    results: list[dict]
) -> int
```

**Returns:** Number of prompts successfully updated

**Performance:** Uses `asyncio.gather` for parallel database updates.

***

### update\_visibility\_scores

Updates the overall visibility score for an entity (UPSERT pattern).

```python theme={null}
async def update_visibility_scores(
    business_id: str,
    product_id: Optional[str],
    platform_scores: dict,
    overall_score: int,
    total_queries: int,
    total_visible: int
) -> None
```

**Behavior:**

* Updates `recent_score` column
* Appends to `history_scores` JSONB array
* Keeps last 365 days of history

***

### get\_visibility\_results\_from\_db

Retrieves visibility results from `entity_prompts_tracker`.

```python theme={null}
async def get_visibility_results_from_db(
    business_id: str,
    product_id: Optional[str] = None,
    limit: int = 100
) -> list[dict]
```

***

### get\_visibility\_trends\_from\_db

Gets visibility score history for trend analysis.

```python theme={null}
async def get_visibility_trends_from_db(
    business_id: str,
    product_id: Optional[str] = None,
    days: int = 30
) -> list[dict]
```

**Returns:** List of history entries from `history_scores` JSONB array

## Database Tables

### entity\_prompts\_tracker

Combined prompts and visibility results per entity.

| Column           | Type      | Description              |
| ---------------- | --------- | ------------------------ |
| `entity_id`      | UUID      | Foreign key to entities  |
| `prompt`         | text      | The search prompt        |
| `chatgpt`        | boolean   | Visibility on ChatGPT    |
| `claude`         | boolean   | Visibility on Claude     |
| `gemini`         | boolean   | Visibility on Gemini     |
| `perplexity`     | boolean   | Visibility on Perplexity |
| `copilot`        | boolean   | Visibility on Copilot    |
| `deepseek`       | boolean   | Visibility on DeepSeek   |
| `grok`           | boolean   | Visibility on Grok       |
| `google_ai`      | boolean   | Visibility on Google AI  |
| `checked_at`     | timestamp | When last checked        |
| `last_tested_at` | timestamp | When last tested         |

### visibility\_score\_history

Historical visibility scores per entity.

| Column           | Type    | Description                  |
| ---------------- | ------- | ---------------------------- |
| `entity_id`      | UUID    | Foreign key to entities      |
| `recent_score`   | integer | Latest overall score (0-100) |
| `history_scores` | JSONB   | Array of daily score entries |

## Code Location

```
src/app/apis/cron/analyze_visibility/database_operations.py
```