PageBridge

Database

The @pagebridge/db package provides the PostgreSQL schema, Drizzle ORM client, and helper functions.

Creating a client

import { createDb } from '@pagebridge/db'

const { db, close } = createDb(process.env.DATABASE_URL!)

// Use the db client for queries
await db.select().from(searchAnalytics)

// Close the connection when done
await close()
typescript

Table exports

All table definitions are exported for use in custom queries:

import {
  searchAnalytics,
  queryAnalytics,
  syncLog,
  pageIndexStatus,
  unmatchDiagnostics
} from '@pagebridge/db'
typescript

searchAnalytics

Page-level search metrics per day.

// Columns
{
  id: string           // Primary key: `${siteId}:${page}:${date}`
  siteId: string
  page: string
  date: date
  clicks: number
  impressions: number
  ctr: number
  position: number
  fetchedAt: timestamp
}

// Indexes: (siteId, page), (siteId, date)
typescript

queryAnalytics

Query-level search metrics per page per day.

// Columns
{
  id: string           // Primary key: `${siteId}:${page}:${query}:${date}`
  siteId: string
  page: string
  query: string
  date: date
  clicks: number
  impressions: number
  ctr: number
  position: number
}

// Index: (siteId, page, query)
typescript

syncLog

Tracks sync execution history.

// Columns
{
  id: uuid
  siteId: string
  status: 'running' | 'completed' | 'failed'
  startedAt: timestamp
  completedAt: timestamp | null
  rowsProcessed: number | null
  error: text | null
}
typescript

pageIndexStatus

Cached Google index inspection results with 24-hour TTL.

// Columns
{
  id: string           // Primary key: `${siteId}:${page}`
  siteId: string
  page: string
  verdict: string      // 'PASS' | 'FAIL' | 'NEUTRAL'
  coverageState: string | null
  indexingState: string | null
  pageFetchState: string | null
  lastCrawlTime: timestamp | null
  robotsTxtState: string | null
  fetchedAt: timestamp
}

// Index: (siteId, page)
typescript

unmatchDiagnostics

Stores diagnostic information for unmatched URLs.

// Columns
{
  id: string                    // Primary key: `${siteId}:${gscUrl}`
  siteId: string
  gscUrl: string
  extractedSlug: string | null
  unmatchReason: string         // 'no_slug_extracted' | 'no_matching_document' | 'outside_path_prefix'
  normalizedUrl: string | null
  pathAfterPrefix: string | null
  configuredPrefix: string | null
  similarSlugs: string | null   // JSON array of similar slug suggestions
  availableSlugsCount: number | null
  lastSeenAt: timestamp
  firstSeenAt: timestamp
}

// Indexes: (siteId), (siteId, unmatchReason)
typescript

Drizzle utilities

Common Drizzle ORM utilities are re-exported for convenience:

import { eq, desc, asc, and, or, sql, gte, lte } from '@pagebridge/db'

// Example: query recent analytics
const recentData = await db
  .select()
  .from(searchAnalytics)
  .where(
    and(
      eq(searchAnalytics.siteId, 'sc-domain:example.com'),
      gte(searchAnalytics.date, startDate)
    )
  )
  .orderBy(desc(searchAnalytics.date))
typescript

See also