Most enterprise data already lives in structured systems like PostgreSQL. The challenge is not access, it is making that data usable for AI.
Search today is no longer just keyword matching. Teams need semantic search: the ability to find meaning, not just exact text. This requires transforming structured records into embeddings and storing them in a way AI systems can query in real time.
This guide shows how to do exactly that using AISquared’s UNIFI platform. You will learn how to connect your PostgreSQL database, prepare your data, and sync it into the AISquared Vector Store to enable semantic search across your records.
The goal is simple: take existing data and make it ready for AI-driven applications without rebuilding your systems.
Overview
This workflow covers how to connect a PostgreSQL database as a source, define a vector store destination in UNIFI, build a data model over your Postgres data, and run a sync that writes embeddings and metadata into the AISquared Vector Store.
| Use case: Structured drug records stored in PostgreSQL are modeled, embedded, and synced into the AISquared Vector Store — enabling semantic similarity search over prescription drug data within UNIFI-powered applications. |
Step 1 — Configure the PostgreSQL Source
In UNIFI: Sources → New Source → PostgreSQL
Add your PostgreSQL database as a source using standard connection credentials. The user must have at minimum SELECT access on the tables you intend to query.
| Field | Value | Notes |
|---|---|---|
| auth_type | username/password | Standard Postgres credential auth |
| Username | e.g. davidtestdb_user | Must have SELECT on target tables |
| Password | (your password) | Stored encrypted by UNIFI |
| Host | e.g. a.oregon-postgres.render.com | Hostname or IP of your Postgres server |
| Port | 5432 | Default Postgres port |
| Database | e.g. davidtestdb | Name of the database to connect to |
| Schema | e.g. public | Schema containing the tables to sync from |
Step 2 — Configure the AISquared Vector Store Destination
In UNIFI: Destinations → New Destination → AISquared Vector Store
The AISquared Vector Store is UNIFI’s native managed vector database. After creating the destination, define a table to hold your embeddings and metadata.
2.1 Create a Table
Once the destination is saved, click into it and select Create Table. Define your table name and columns using the schema editor:
| Column Name | Data Type | Constraints | Notes |
|---|---|---|---|
| id | int8 | Primary Key | Auto-incrementing row identifier |
| drug_id | text | NULL | Source identifier for the record |
| drug_name | text | NULL | Human-readable name for display/search |
| embedding | vector | NULL | The generated embedding vector |
| created_at | timestamp | NULL | Record creation time |
| embedding_text | text | NULL | Raw text that was embedded |
| embedding_type | text | NULL | Embedding model or type label |
| Note: If the table is part of an active sync it cannot be modified. Disable the sync first before editing the schema, then re-enable it after saving. |
Step 3 — Create the Data Model
In UNIFI: Models → Add Model → select your PostgreSQL source
The data model defines how UNIFI reads and prepares data from your Postgres source before syncing it to the vector store. Configure it using one of the three retrieval methods below.
3.1 Data Retrieval Method
Choose one of the following methods when setting up the model:
| Method | Description | Best For |
|---|---|---|
| Table Selection | Browse and select a table directly from the source schema | Simple full-table syncs with no transformation needed |
| Static Query | Write a fixed SQL SELECT statement executed on every sync run | Filtered or joined datasets that don’t change structure |
| Dynamic Query | Parameterized SQL that can incorporate runtime variables | Incremental or time-windowed queries |
3.2 Preview and Finalize
- After configuring your retrieval method, click Show Preview to confirm the query returns the expected rows and column structure
- Verify the output contains the columns you intend to map (e.g. drug_id, drug_name, embedding_text)
- Give the model a descriptive name and click Continue to save it
| Tip: Always use Show Preview before saving the model. A missing column at this stage will cause mapping errors later in the sync configuration and require you to recreate the model. |
Step 4 — Create and Run the Sync
In UNIFI: Syncs → New Sync → select your data model → select your AISquared Vector Store destination
4.1 Sync Configuration Steps
- Select the data model created in Step 3 as the sync source
- Select the AISquared Vector Store destination configured in Step 2
- In the Stream Name dropdown, select your target vector table (e.g. prescription_drugs_synthetic_vector_db). If it does not appear, click Refresh to reload the destination catalog
- Set Sync Mode to Incremental to process only new or updated records on each run
- Map each PostgreSQL source field to the corresponding AISquared Vector Store column (see field mapping table below). For the embedding field, select the appropriate embedding model or check Skip embedding configuration when both fields are vector types if the source already contains pre-computed vectors
- Configure the sync schedule — choose from Manual, Interval (e.g. every N hours), or Cron Expression for fine-grained control
- Click Save Changes to persist the sync configuration
- Click Run Now to execute the sync. Monitor progress under the Sync Runs tab
| Tip: Some model types support a Test Sync button, which processes a single row and returns enhanced debugging output. Use this to validate your field mappings and embedding configuration before running a full sync. |
4.2 Field Mapping Reference
| PostgreSQL Field (Source) | → | Vector Store Column (Destination) | Notes |
|---|---|---|---|
| id | → | id | |
| drug_name | → | drug_name | |
| embedding_text | → | embedding_text | Text used for embedding generation |
| embedding | → | embedding | Skip config if both are vector type |
| embedding_type | → | embedding_type | |
| drug_id | → | drug_id |
4.3 Scheduling Options
| Mode | Configuration | Use When |
|---|---|---|
| Manual | Triggered via Run Now button only | Ad-hoc loads or initial data validation |
| Interval | Set a frequency in minutes or hours | Regular recurring syncs without precise timing needs |
| Cron Expression | Standard cron syntax, e.g. 0 2 * * * | Precise scheduled windows, e.g. nightly at 2 AM |
Quick Reference — Workflow Summary
| Step | Action | Key Detail |
|---|---|---|
| 1 | Create PostgreSQL Source | username/password auth; user needs SELECT on target tables; specify schema |
| 2 | Create Vector Store Destination | Add table with id (int8 PK), drug_id, drug_name, embedding (vector), embedding_text, embedding_type, created_at |
| 3 | Create Data Model | Choose Table Selection, Static Query, or Dynamic Query; always Show Preview before saving |
| 4 | Create & Run Sync | Map 6 fields; Incremental mode; select embedding model or skip for pre-computed vectors; set schedule; Save + Run Now |
