vikingowl
a1d93f7a8e
Go backend with Gin, pgx, Valkey (go-valkey), and PostGIS. Domains: - Market search with PostGIS geo-queries (ST_DWithin, ST_Distance), German full-text search (tsvector + ILIKE fallback for compound words), date range filtering, pagination, and slug-based detail endpoint - Auth with email+password (bcrypt), JWT access tokens (15min), session tokens (30d, dual Valkey+Postgres storage), OAuth (Google/GitHub/Facebook), magic links, and TOTP 2FA - User profile with CRUD, soft-delete (30d grace), and restore Infrastructure: - 6 database migrations (users, sessions, oauth_accounts, magic_links, markets with PostGIS+FTS, totp_secrets) - Middleware: recovery, request ID, structured logging (slog), CORS, per-IP rate limiting, JWT auth - Seed data: 10 medieval markets across DACH region - Docker Compose (PostGIS 17 + Valkey 8), multi-stage Dockerfile, Woodpecker CI pipeline, Kubernetes manifests - Justfile, golangci-lint config, env example
31 lines
932 B
PL/PgSQL
31 lines
932 B
PL/PgSQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email TEXT NOT NULL,
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
password_hash TEXT,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
avatar_url TEXT NOT NULL DEFAULT '',
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;
|
|
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|