Search

Your tables can be searched two ways at once: full-text, over the words in your text columns, and vector, over the meaning of an embedding. Both are declared in your schema file beside the columns they cover, and both are read through one method — Database.public.<name>.search() — which runs under the same Row-Level Security as every other read. You never write the SQL, and you never manage an index.

If you also declare a model, Palbase writes the embeddings for you: it watches the source columns, embeds new and changed rows in the background, and embeds the query at search time. That is the whole setup — there is no separate vector store to run, nothing to sync, and no second database to keep consistent with this one.

Quick example

// db/public.ts
import {
  defineSchema, defineTable, openai, ownedByUser, text, timestamp, uuid, vector,
} from "@palbase/backend";

export const articles = defineTable("articles", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    user_id: ownedByUser(),
    title: text().notNull(),
    body: text().notNull(),
    embedding: vector(1536).nullable(),
    created_at: timestamp().defaultNow(),
  },
  search: {
    text: ["title", "body"],
    vector: {
      model: openai.embedding("text-embedding-3-small"),
      from: ["title", "body"],
    },
  },
});

export default defineSchema("public", {
  tables: [articles],
  extensions: ["vector"],
});

// modules/articles/dto/search.ts
import { z } from "@palbase/backend";

export const SearchQuery = z.object({ q: z.string().min(1) });
export type SearchQuery = z.infer<typeof SearchQuery>;

export const Hit = z.object({
  id: z.string(),
  user_id: z.string(),
  title: z.string(),
  body: z.string(),
  embedding: z.array(z.number()).nullable(),
  created_at: z.string(),
  _score: z.number(),
});
export type Hit = z.infer<typeof Hit>;

// modules/articles/article.service.ts
import { Controller, Database, Get, Injectable, QueryParams } from "@palbase/backend";
import { Hit, SearchQuery } from "./dto/search";

@Injectable()
export class ArticleService {
  search(query: string): Promise<Hit[]> {
    // Both legs run: the words from `query`, and its meaning as a vector.
    // RLS still applies — this only searches rows the caller can read.
    return Database.public.articles.search({ query, limit: 20 });
  }
}

// modules/articles/articles.controller.ts
@Controller("/articles")
export class ArticlesController {
  constructor(private readonly articles: ArticleService) {}

  @Get("/search")
  search(@QueryParams(SearchQuery) q: SearchQuery): Promise<Hit[]> {
    return this.articles.search(q.q);
  }
}

Both classes are named in modules/articles/articles.module.ts — the controller under controllers, the service under providers.

Three lines of schema and one method call. palbase push creates the full-text column, the indexes, and the embedding pipeline; the first search({ query }) embeds the query and returns ranked rows.

Warning: extensions: ["vector"] is required for any table with a vector column. Without it the deploy refuses the whole schema, naming the extension, rather than applying half of it. The Postgres extension is named vector, not pgvector — declare "vector".

The vector column

vector(n) declares an embedding column of exactly n dimensions.

import { defineTable, text, uuid, vector } from "@palbase/backend";

const articles = defineTable("articles", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    title: text().notNull(),
    embedding: vector(1536).nullable(),
  },
});

Writing a vector yourself is ordinary:

import { Database } from "@palbase/backend";

declare const title: string;
declare const body: string;
declare const userId: string;
declare const myVector: number[];

await Database.public.articles.insert({
  title, body, user_id: userId,
  embedding: myVector,          // number[], length must equal the declared n
});

A length mismatch fails the write with the database's own dimension error. If you declare a model (below), you do not write this column at all.

The search declaration

A table opts into search with a search block. Both legs are optional; declare the ones you want.

import { defineTable, text, uuid, vector } from "@palbase/backend";

const articles = defineTable("articles", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    title: text().notNull(),
    body: text().notNull(),
    embedding: vector(1536).nullable(),
  },
  search: {
    text: ["title", "body"],                             // full-text leg
    vector: { column: "embedding", metric: "cosine" },   // vector leg
  },
});

text names the text columns to index for word search. Palbase derives a stored full-text column from them plus a GIN index, and regenerates both when the list changes. The columns must exist and must be text-typed, or the deploy fails naming the offender.

vector names one ANN leg — or an array of them, when a table carries more than one embedding column:

An empty search: {} is refused, and so is text: [] — if you do not want a leg, leave the field out entirely rather than writing an empty one. A table with a vector column but no search block is still searchable: the column alone is enough.

Searching

const hits = await Database.public.articles.search({
  query: "how do i rotate a key",
  where: { user_id: user.id, created_at: { gte: "2026-01-01" } },
  limit: 10,
});

Every row comes back as your row type plus _score, a number, already sorted best-first. When both legs run, the two rankings are fused, so a row that is strong in either one surfaces.

Note: There is no offset, deliberately. Deep paging over a ranked result re-runs the whole ranking to throw most of it away, and the page boundaries move under you as rows change. Narrow with where, or raise limit toward the cap.

search() exists only where it means something

search() is on the table accessor only when the table declares a search block or has a vector column. Call it on a table that has neither and the code does not compile — the member is not there to call. That is deliberate: a search surface that answers "nothing configured" at runtime is a bug you find in production, and a missing method is one you find while typing.

When a leg cannot run

Legs degrade rather than lie. Search with no query and the full-text leg simply does not run; supply a vector and the embedding call is skipped. If no leg can run, search() fails with a message saying so — it never quietly returns an empty array, because "no results" and "not configured" are different answers and you must be able to tell them apart.

Auto-embedding

Add model and from, and Palbase takes over both halves of the embedding work:

import { defineTable, openai, text, uuid, vector } from "@palbase/backend";

const articles = defineTable("articles", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    title: text().notNull(),
    body: text().notNull(),
    embedding: vector(1536).nullable(),
  },
  search: {
    vector: {
      model: openai.embedding("text-embedding-3-small"),
      from: ["title", "body"],
    },
  },
});

On write. Inserting a row, or updating any column in from, queues that row for embedding. The work happens in the background, once per row rather than once per edit — a row edited three times in a second is embedded once. When the declaration first lands, every existing row with a NULL embedding is queued the same way, so you get a backfill without asking for one.

On read. search({ query }) embeds the query with the same model before running the vector leg. Pass vector yourself and no provider call is made.

openai.embedding(model, opts) takes:

The descriptor is plain data, not a live client — it travels with your schema, and the calls are made for you.

Warning: The API key must already exist as a secret before you deploy a schema that declares a model. The deploy checks first and refuses with missing_embed_secret, naming the secret and the declaration that wants it, rather than shipping a pipeline that cannot run. Set it with palbase secret set OPENAI_API_KEY=… first.

The target table needs a single-column primary key, and provider is "openai" today.

staleness — what a stale row does

When the source text changes, the stored embedding is briefly out of date. You choose which risk you take:

The default is the honest one: it never serves a match that the current text does not support. Choose "keep" when a brief gap in results is worse than a brief mismatch — bulk imports that rewrite many rows at once are the usual reason.

Watching the queue

Embedding is background work, so it has a window you can look at. The stack answers, admin-only:

GET /admin/embed/status

{
  "queues": [
    { "table": "articles", "column": "embedding", "pending": 12, "parked": 0 }
  ]
}

pending is waiting or retrying. parked is rows that exhausted their retries; those carry lastError, which is where a rejected API key or a rate limit shows up. A parked count that is not zero means embeddings are not being written — check lastError before assuming the model is at fault.

What to keep in mind

• Vector columns are excluded from nothing. They come back on ordinary reads too, as number[]. If you do not want a 1536-float array in an API response, leave it out of your response schema.
• RLS applies to search. Both legs run inside the caller's transaction, so search can only find rows that caller could already read. A public search endpoint needs a policy that permits it, exactly like any other read.
• where runs inside the legs, not after them — filtering to a small slice does not quietly drain your limit.
• A where key that is not a column fails the call by name rather than being ignored.

Related

• Schema — the column types, extensions, and how a schema is applied
• Database — the table accessor search() lives on
• Row-Level Security — the policies search runs under
• Secrets — where the embedding API key lives