Database Development

Aya uses PostgreSQL 16 with sqlc for type-safe SQL queries and goose for migrations.

Database Schema

Core Tables

The database schema follows a pattern of base tables with separate translation (_tx) tables:

CREATE TABLE "profile" (
  "id" CHAR(26) PRIMARY KEY,        -- ULID
  "slug" TEXT UNIQUE NOT NULL,
  "kind" TEXT NOT NULL,             -- 'individual', 'organization', 'product'
  "profile_picture_uri" TEXT,
  "default_locale" CHAR(12),         -- Profile's preferred locale
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE "profile_tx" (
  "profile_id" CHAR(26) REFERENCES "profile",
  "locale_code" CHAR(12) NOT NULL,  -- 'en', 'tr', 'pt-PT', etc.
  "title" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  PRIMARY KEY ("profile_id", "locale_code")
);

Pattern:

Base table: Contains locale-independent fields (ID, slug, timestamps, URIs)
Translation table (_tx): Contains locale-specific fields (title, description, content)
Primary key on translation table: (entity_id, locale_code)

Table Overview

Profiles
Stories
Users & Auth

profile           -- Base profile data
profile_tx        -- Translated title/description
profile_page      -- Custom pages (About, CV, etc.)
profile_page_tx   -- Page content translations
profile_link      -- External links (GitHub, LinkedIn)
profile_membership -- Organization members
profile_resource  -- Synced resources (repos, packages)

story             -- Base story data
story_tx          -- Translated title/summary/content
story_series      -- Series grouping
story_series_tx   -- Series translations
story_interaction -- Bookmarks, likes
story_date_proposal -- Event date proposals

user              -- User accounts
session           -- Authentication sessions
question          -- Q&A questions
discussion_comment -- Comments on stories/profiles

Migrations

Creating Migrations

Migrations use goose and live in etc/data/default/migrations/:

cd apps/services/etc/data/default/migrations

# Create new migration file
touch 0011_add_profile_badges.sql

0011_add_profile_badges.sql

-- +goose Up
ALTER TABLE "profile" ADD COLUMN "badges" TEXT[] DEFAULT '{}';

CREATE INDEX profile_badges_idx ON "profile" USING GIN ("badges");

COMMENT ON COLUMN "profile"."badges" IS 'User badges: verified, early_adopter, contributor';

-- +goose Down
DROP INDEX IF EXISTS profile_badges_idx;
ALTER TABLE "profile" DROP COLUMN IF EXISTS "badges";

Running Migrations

cd apps/services

# Apply all pending migrations
make migrate-up

# Rollback last migration
make migrate-down

# Check status
go run ./cmd/migrate/ default status

# Direct goose commands
goose -dir etc/data/default/migrations postgres "postgres://..." up
goose -dir etc/data/default/migrations postgres "postgres://..." status

Migration Best Practices

Always include Down migration

Every migration must be reversible:

-- +goose Up
CREATE TABLE "new_table" (...);

-- +goose Down
DROP TABLE "new_table";

Use IF EXISTS/IF NOT EXISTS

Make migrations idempotent:

-- Safe to run multiple times
CREATE INDEX IF NOT EXISTS idx_name ON "table" ("column");
ALTER TABLE "table" DROP COLUMN IF EXISTS "old_column";

Add indexes for foreign keys

Always index foreign key columns:

CREATE TABLE "profile_membership" (
  "profile_id" CHAR(26) REFERENCES "profile",
  "member_profile_id" CHAR(26) REFERENCES "profile"
);

CREATE INDEX profile_membership_profile_id_idx ON "profile_membership" ("profile_id");
CREATE INDEX profile_membership_member_profile_id_idx ON "profile_membership" ("member_profile_id");

Use CHAR(26) for ULIDs

All primary keys use ULID format (26 characters):

CREATE TABLE "entity" (
  "id" CHAR(26) PRIMARY KEY,  -- ULID: 01ARZ3NDEKTSV4RRFFQ69G5FAV
  ...
);

sqlc (Type-Safe SQL)

Writing Queries

SQL queries are in etc/data/default/queries/ and use sqlc annotations:

etc/data/default/queries/profiles.sql

-- name: GetProfileBySlug :one
SELECT 
  p.id,
  p.slug,
  p.kind,
  p.profile_picture_uri,
  p.default_locale,
  pt.locale_code,
  pt.title,
  pt.description
FROM "profile" p
JOIN "profile_tx" pt ON pt.profile_id = p.id
WHERE p.slug = sqlc.arg(slug)
  AND p.deleted_at IS NULL
  -- 3-tier locale fallback (CRITICAL)
  AND pt.locale_code = (
    SELECT ptx.locale_code FROM "profile_tx" ptx
    WHERE ptx.profile_id = p.id
    ORDER BY CASE
      WHEN ptx.locale_code = sqlc.arg(locale_code) THEN 0
      WHEN ptx.locale_code = p.default_locale THEN 1
      ELSE 2
    END
    LIMIT 1
  )
LIMIT 1;

-- name: ListProfilesByKinds :many
SELECT
  p.id,
  p.slug,
  p.kind,
  pt.locale_code,
  pt.title,
  pt.description
FROM "profile" p
JOIN "profile_tx" pt ON pt.profile_id = p.id
WHERE p.kind = ANY(sqlc.arg(kinds)::text[])
  AND p.deleted_at IS NULL
  AND p.approved_at IS NOT NULL
  AND pt.locale_code = (
    SELECT ptx.locale_code FROM "profile_tx" ptx
    WHERE ptx.profile_id = p.id
    ORDER BY CASE
      WHEN ptx.locale_code = sqlc.arg(locale_code) THEN 0
      WHEN ptx.locale_code = p.default_locale THEN 1
      ELSE 2
    END
    LIMIT 1
  )
ORDER BY p.created_at DESC
LIMIT sqlc.arg(limit)
OFFSET sqlc.arg(offset);

-- name: CreateProfile :one
INSERT INTO "profile" (
  "id",
  "slug",
  "kind",
  "default_locale"
) VALUES (
  sqlc.arg(id),
  sqlc.arg(slug),
  sqlc.arg(kind),
  sqlc.arg(default_locale)
)
RETURNING *;

-- name: CreateProfileTranslation :exec
INSERT INTO "profile_tx" (
  "profile_id",
  "locale_code",
  "title",
  "description"
) VALUES (
  sqlc.arg(profile_id),
  sqlc.arg(locale_code),
  sqlc.arg(title),
  sqlc.arg(description)
);

sqlc Annotations

:one - Returns single row (error if not found)
:many - Returns slice of rows
:exec - Executes query, returns error only
:execrows - Returns number of affected rows
sqlc.arg(name) - Named parameter
sqlc.narg(name) - Nullable parameter

Generating Go Code

cd apps/services

# Generate type-safe Go code
make generate
# or directly:
sqlc generate

Generated code (pkg/api/adapters/profiles/db.go):

type Queries struct {
    db DBTX
}

type GetProfileBySlugParams struct {
    Slug       string
    LocaleCode string
}

type GetProfileBySlugRow struct {
    ID                string
    Slug              string
    Kind              string
    ProfilePictureURI *string
    DefaultLocale     string
    LocaleCode        string
    Title             string
    Description       string
}

func (q *Queries) GetProfileBySlug(ctx context.Context, arg GetProfileBySlugParams) (GetProfileBySlugRow, error)
func (q *Queries) ListProfilesByKinds(ctx context.Context, arg ListProfilesByKindsParams) ([]ListProfilesByKindsRow, error)
func (q *Queries) CreateProfile(ctx context.Context, arg CreateProfileParams) (Profile, error)
func (q *Queries) CreateProfileTranslation(ctx context.Context, arg CreateProfileTranslationParams) error

sqlc Configuration

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "etc/data/default/queries"
    schema: "etc/data/default/migrations"
    gen:
      go:
        package: "profiles"
        out: "pkg/api/adapters/profiles"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_pointers_for_null_types: true
        emit_empty_slices: true

3-Tier Locale Fallback

This is the most critical database pattern in Aya.

The Problem

A profile may have translations in multiple locales:

English (en)
Turkish (tr)
French (fr)

User requests the profile in Portuguese (pt-PT), which doesn’t exist. Wrong approach (2-tier fallback):

-- BAD: Returns nothing if pt-PT doesn't exist AND default_locale isn't available
WHERE (locale_code = 'pt-PT' OR locale_code = p.default_locale)

Problem: If default_locale is es (Spanish) and the profile only has en, tr, fr translations, the query returns nothing.

The Solution

3-tier fallback subquery:

AND pt.locale_code = (
  SELECT ptx.locale_code
  FROM "profile_tx" ptx
  WHERE ptx.profile_id = p.id
  ORDER BY CASE
    WHEN ptx.locale_code = sqlc.arg(locale_code) THEN 0  -- Tier 1: Requested
    WHEN ptx.locale_code = p.default_locale THEN 1       -- Tier 2: Default
    ELSE 2                                                -- Tier 3: Any available
  END
  LIMIT 1
)

How it works:

Tier 1: Requested Locale

If a translation exists for pt-PT, use it. Priority = 0 (highest).

Tier 2: Entity's Default

If not, use the profile’s default_locale (e.g., es). Priority = 1.

Tier 3: Any Available

If neither exists, use ANY translation (en, tr, or fr). Priority = 2.

Result: The query always returns a translation if the entity has at least one.

Story Fallback (Author’s Default)

For stories, Tier 2 uses the author profile’s default locale:

-- name: GetStoryBySlug :one
SELECT
  s.id,
  s.slug,
  st.locale_code,
  st.title,
  st.content
FROM "story" s
JOIN "story_tx" st ON st.story_id = s.id
WHERE s.slug = sqlc.arg(slug)
  AND st.locale_code = (
    SELECT stx.locale_code FROM "story_tx" stx
    WHERE stx.story_id = s.id
    ORDER BY CASE
      WHEN stx.locale_code = sqlc.arg(locale_code) THEN 0
      -- Use author's default locale
      WHEN stx.locale_code = (
        SELECT p.default_locale FROM "profile" p WHERE p.id = s.author_profile_id
      ) THEN 1
      ELSE 2
    END
    LIMIT 1
  )
LIMIT 1;

NEVER use 2-tier fallback. Always use the 3-tier subquery pattern for ALL _tx table joins.

Indexes

Essential Indexes

-- Foreign keys (always index!)
CREATE INDEX profile_membership_profile_id_idx ON "profile_membership" ("profile_id");
CREATE INDEX story_author_profile_id_idx ON "story" ("author_profile_id");

-- Lookup fields
CREATE UNIQUE INDEX profile_slug_unique ON "profile" ("slug") WHERE "deleted_at" IS NULL;
CREATE INDEX profile_kind_idx ON "profile" ("kind");

-- Composite indexes for common queries
CREATE INDEX story_kind_published_at_idx ON "story" ("kind", "published_at" DESC) WHERE "deleted_at" IS NULL;

-- Full-text search
CREATE INDEX story_tx_search_idx ON "story_tx" USING gin("search_vector");

-- Array columns
CREATE INDEX profile_badges_idx ON "profile" USING GIN ("badges");

Partial Indexes

Use WHERE clauses to reduce index size:

-- Only index non-deleted profiles
CREATE INDEX profile_approved_idx ON "profile" ("approved_at") 
WHERE "deleted_at" IS NULL;

-- Only index published stories
CREATE INDEX story_published_at_idx ON "story" ("published_at" DESC)
WHERE "published_at" IS NOT NULL AND "deleted_at" IS NULL;

Full-Text Search

Aya implements PostgreSQL full-text search for stories:

etc/data/default/migrations/0005_fts_search.sql

-- +goose Up
ALTER TABLE "story_tx" ADD COLUMN "search_vector" tsvector;

CREATE INDEX story_tx_search_idx ON "story_tx" USING gin("search_vector");

-- Auto-update search vector on insert/update
CREATE FUNCTION story_tx_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER story_tx_search_update_trigger
BEFORE INSERT OR UPDATE ON "story_tx"
FOR EACH ROW EXECUTE FUNCTION story_tx_search_update();

-- +goose Down
DROP TRIGGER IF EXISTS story_tx_search_update_trigger ON "story_tx";
DROP FUNCTION IF EXISTS story_tx_search_update();
DROP INDEX IF EXISTS story_tx_search_idx;
ALTER TABLE "story_tx" DROP COLUMN IF EXISTS "search_vector";

Search query:

-- name: SearchStories :many
SELECT
  s.id,
  s.slug,
  st.title,
  ts_rank(st.search_vector, query) AS rank
FROM "story" s
JOIN "story_tx" st ON st.story_id = s.id,
  to_tsquery('english', sqlc.arg(search_query)) query
WHERE st.search_vector @@ query
  AND s.deleted_at IS NULL
  AND s.published_at IS NOT NULL
ORDER BY rank DESC
LIMIT 50;

Performance Tips

EXPLAIN ANALYZE

Always check query plans:

EXPLAIN ANALYZE
SELECT p.*, pt.*
FROM "profile" p
JOIN "profile_tx" pt ON pt.profile_id = p.id
WHERE p.kind = 'organization'
  AND pt.locale_code = (
    SELECT ptx.locale_code FROM "profile_tx" ptx
    WHERE ptx.profile_id = p.id
    ORDER BY CASE
      WHEN ptx.locale_code = 'en' THEN 0
      WHEN ptx.locale_code = p.default_locale THEN 1
      ELSE 2
    END
    LIMIT 1
  );

Look for:

Seq Scan (bad) vs Index Scan (good)
Nested Loop (expensive for large tables)
High cost values

Connection Pooling

Use pgx pool:

import "github.com/jackc/pgx/v5/pgxpool"

pool, err := pgxpool.New(ctx, "postgres://...")
defer pool.Close()

queries := New(pool)

Pagination

Always use LIMIT and OFFSET:

-- name: ListStoriesPaginated :many
SELECT * FROM "story"
WHERE deleted_at IS NULL
ORDER BY published_at DESC
LIMIT sqlc.arg(limit)
OFFSET sqlc.arg(offset);

stories, err := queries.ListStoriesPaginated(ctx, ListStoriesPaginatedParams{
    Limit:  20,
    Offset: (page - 1) * 20,
})

Database Access from Code

psql

# Via Docker
docker compose exec postgres psql -U postgres -d postgres

# Direct connection
psql postgres://postgres:s3cr3t@localhost:5432/postgres

GUI Clients

Connection details:

Host: localhost
Port: 5432
Database: postgres
User: postgres
Password: s3cr3t

Recommended:

Troubleshooting

CHAR(12) locale codes don't match

PostgreSQL pads CHAR(12) with spaces. Always trim in Go:

LocaleCode: strings.TrimRight(row.LocaleCode, " "),

Profile disappears when changing locale

Check if you’re using 2-tier fallback instead of 3-tier. Use the subquery pattern.

Migration fails: relation already exists

Use IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS "new_table" (...);
CREATE INDEX IF NOT EXISTS idx_name ON "table" ("column");

sqlc generate fails

Check:

SQL syntax is valid PostgreSQL
All referenced tables exist in migrations
sqlc.yaml paths are correct

Run with verbose output:

sqlc generate -v

Next Steps

Backend Development

Use generated sqlc code in repositories

Internationalization

Understand the 3-tier fallback in depth

Architecture

See how database fits in hexagonal architecture

Deployment

Deploy PostgreSQL to production

Getting Started

Core Concepts

Development

Deployment

Database Development

Database Development

Database Schema

Core Tables

Table Overview

Migrations

Creating Migrations

Running Migrations

Migration Best Practices

sqlc (Type-Safe SQL)

Writing Queries

sqlc Annotations

Generating Go Code

sqlc Configuration

3-Tier Locale Fallback

The Problem

The Solution

Story Fallback (Author’s Default)

Indexes

Essential Indexes

Partial Indexes

Full-Text Search

Performance Tips

EXPLAIN ANALYZE

Connection Pooling

Database Access from Code

psql

GUI Clients

Troubleshooting

Next Steps

Backend Development

Internationalization

Architecture

Deployment

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Development

Deployment

​Database Development

​Database Schema

​Core Tables

​Table Overview

​Migrations

​Creating Migrations

​Running Migrations

​Migration Best Practices

​sqlc (Type-Safe SQL)

​Writing Queries

​sqlc Annotations

​Generating Go Code

​sqlc Configuration

​3-Tier Locale Fallback

​The Problem

​The Solution

​Story Fallback (Author’s Default)

​Indexes

​Essential Indexes

​Partial Indexes

​Full-Text Search

​Performance Tips

​EXPLAIN ANALYZE

​Connection Pooling

​Pagination

​Database Access from Code

​psql

​GUI Clients

​Troubleshooting

​Next Steps

Backend Development

Internationalization

Architecture

Deployment

Build docs developers (and LLMs) love

Database Development

Database Schema

Core Tables

Table Overview

Migrations

Creating Migrations

Running Migrations

Migration Best Practices

sqlc (Type-Safe SQL)

Writing Queries

sqlc Annotations

Generating Go Code

sqlc Configuration

3-Tier Locale Fallback

The Problem

The Solution

Story Fallback (Author’s Default)

Indexes

Essential Indexes

Partial Indexes

Full-Text Search

Performance Tips

EXPLAIN ANALYZE

Connection Pooling

Pagination

Database Access from Code

psql

GUI Clients

Troubleshooting

Next Steps