davidbirchall/squad-skills-data

Bulk data import and export for Node and PostgreSQL - CSV, JSON and NDJSON formats, streaming instead of buffering, row-level validation with error reports, idempotent re-runnable imports, staging tables with merge or upsert, batch sizes and transactions, encoding, locale and timezone pitfalls, large files, deduplication, mapping and transform layers, dry-run mode, progress and resumability, export pagination and consistency, PII in exports, signed download links, and CSV formula injection protection. Load when building file imports or exports, bulk loading data, migrating data between systems, or debugging bad or partial loads.

Data integrity for PostgreSQL and Prisma in Node services - database constraints (NOT NULL, UNIQUE, CHECK, foreign keys) as source of truth, transactions and isolation levels, idempotency keys, optimistic vs pessimistic locking, race conditions and unique-violation handling, referential actions, app vs database validation layers, orphan detection, reconciliation jobs, backups and restore testing, PII handling, retention and deletion, audit trails, and data quality checks. Load when designing constraints, handling concurrent writes, adding idempotency, planning backups or deletion, or investigating inconsistent data.

Relational data modelling for PostgreSQL - entities and relationships, normalisation to 3NF and deliberate denormalisation, surrogate uuid vs natural keys, many-to-many join tables, soft delete tradeoffs, temporal and history modelling, JSONB boundaries, multi-tenancy models, audit columns, modelling for reads vs writes, and dimensional modelling basics with facts and dimensions. Load when designing or changing a schema, choosing keys, modelling history or tenants, deciding between JSONB and columns, or shaping analytics tables.

Safe schema migrations for PostgreSQL - versioned forward-only migrations, one change per migration, expand/contract for zero-downtime deploys, batched backfills, lock awareness (concurrent index creation, NOT NULL and default adds), reversible vs roll-forward policy, testing on production-sized data, ordering and conflicts in teams, seed vs migration data, never editing applied migrations, and Prisma migrate workflow notes. Load when changing a schema, writing a migration, planning a backfill, adding indexes or constraints to live tables, or resolving migration conflicts.

Diagnose PostgreSQL slowness with measured plans, EXPLAIN ANALYZE, indexes, joins, N+1 queries, pagination, pooling, and statistics before changing code. Load when a query, endpoint, report, or database is slow or when designing indexes.

Search design and indexing for Node and PostgreSQL systems - choosing database search vs a dedicated engine (OpenSearch, Meilisearch, Typesense), Postgres full-text basics (tsvector, GIN), the index as derived data with the database as source of truth, sync strategies (dual-write pitfalls, outbox, CDC, batch reindex), analysers, tokenisation and language, relevance tuning, facets and filters, typo tolerance, zero-downtime reindex with aliases, consistency lag, permission-aware search, and monitoring index drift. Load when adding search, choosing a search engine, building or syncing an index, tuning relevance, or debugging missing or stale results.

SQL conventions for PostgreSQL-backed TypeScript services - snake_case naming, singular vs plural tables, key and index naming, formatting, explicit column lists and no SELECT *, explicit joins, CTE usage, NULL handling, timestamptz in UTC, parameterised queries only, text vs varchar, enums vs lookup tables, schema comments, and transaction and isolation basics. Load when writing or reviewing SQL, naming schema objects, using raw SQL from Prisma, or choosing column types.