> For the complete documentation index, see [llms.txt](https://docs.uxwizz.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.uxwizz.com/guides/database-querying.md).

# Database querying

Use a read-only database account for reporting queries. Start with a small result limit or a restored copy, especially on a busy server. Database access can expose data beyond a dashboard user's assigned domains.

## Database structure

The following overview describes the main analytics relationships in UXWizz. It is not a complete schema. Inspect your installed database before writing queries; older installations and Agency multi-database setups can differ.

### Remarks

| Table                                               | Stores                                             | Relationship                          |
| --------------------------------------------------- | -------------------------------------------------- | ------------------------------------- |
| `ust_clients`                                       | Visitor sessions, domain, browser, and visit times | One row per session, not per person   |
| `ust_clientpage`                                    | Pageviews                                          | `clientid` refers to `ust_clients.id` |
| `ust_records`, `ust_partials`, `ust_records_chunks` | Recording data and chunks                          | `client` refers to a **pageview ID**  |
| `ust_movements`, `ust_clicks`                       | Heatmap data                                       | `client` refers to a **pageview ID**  |
| `ust_client_tag`                                    | Session tags                                       | `clientid` refers to a session        |
| `ust_client_event`                                  | Custom events                                      | Links to a session and pageview       |
| `ust_ab`                                            | A/B test definitions                               | Associated with a domain              |
| `ust_users`, `ust_access`                           | Dashboard accounts and domain access               | Administrative data                   |

Do not join every column named `client` directly to `ust_clients`. Recording and heatmap tables use pageview IDs. IDs from different recording tables are also independent.

An IP address or hash is not a reliable unique-person identifier. Shared connections and changing addresses can group different people or split one person's visits.

### Examples

Use your actual domain value. Keep database passwords out of commands and saved query files. A server administrator can provide a read-only account or run the query for you.

#### Basic query

```sql
SELECT id, domain, first_date, last_date
FROM ust_clients
WHERE domain = 'example.com'
ORDER BY id DESC
LIMIT 100;
```

#### Domain change (move all users tracked from one domain to another)

Changing only `ust_clients.domain` is not a complete domain migration. Access rules, saved settings, A/B tests, and Agency database mappings can also refer to the domain.

Back up the affected databases and ask [support](/guides/support.md) for a plan that matches your version and database layout. Test it on a restored copy before changing production data. For a server move that keeps the same tracked domains, follow [Migrating to a new server](/guides/migrating-to-a-new-server.md).

If you only need to **relabel historical sessions in one analytics database**, the original update is still useful:

1. Back up that database and confirm that both domain names refer to sites you manage. Add the destination domain in UXWizz and grant the intended users access first.
2. Check the matching sessions:

   ```sql
   SELECT COUNT(*) FROM ust_clients WHERE domain = 'old.example.com';
   ```
3. In one database connection, update those rows:

   ```sql
   START TRANSACTION;
   UPDATE ust_clients SET domain = 'new.example.com'
   WHERE domain = 'old.example.com';
   SELECT ROW_COUNT() AS sessions_moved;
   ```
4. Compare the count with step 2. Run `COMMIT;` if it is correct, or `ROLLBACK;` before leaving that connection if it is not.
5. Check the destination's historical reports. Update the website's tracking setup separately if its hostname changed.

This does not move A/B tests, saved domain settings, access records, or data between Agency databases. Do not use it as a cross-database migration. Shared links or filters tied to the old domain may need updating.

#### Get the path each visitor took before first reaching the pricing page:

This query shows up to ten session paths through the first matching pageview. It groups by **session**, not by person. Replace the example domain and page match.

```sql
SELECT
    p.clientid AS session_id,
    GROUP_CONCAT(p.page ORDER BY p.id SEPARATOR ' -> ') AS path
FROM ust_clientpage AS p
JOIN ust_clients AS s ON s.id = p.clientid
WHERE s.domain = 'example.com'
  AND p.id <= (
      SELECT MIN(first_pricing.id)
      FROM ust_clientpage AS first_pricing
      WHERE first_pricing.clientid = p.clientid
        AND first_pricing.page LIKE '%pricing%'
  )
GROUP BY p.clientid
ORDER BY p.clientid DESC
LIMIT 10;
```

`GROUP_CONCAT` can truncate long paths at the database's configured limit. Review query cost on a copy before running it across a large history.