PostgreSQL Dependency Inventory

May 26, 2026 ยท View on GitHub

Developer-facing inventory of PostgreSQL-specific features, schema choices, and code paths in BinktermPHP.

This is a living document. BinktermPHP is PostgreSQL-only today, so PostgreSQL-specific code is allowed. The purpose of this file is not to ban PostgreSQL features; it is to keep a running list of deliberate dependencies so future compatibility work is scoping a known surface area rather than rediscovering it from scratch.

CLAUDE.md includes guidance for AI agents to update this document when they add a new PostgreSQL-specific dependency that future compatibility work would need to account for.

For the higher-level direction and rationale, see:

  • docs/proposals/MariaMySQLCompat.md

How To Use This Document

  • Update this file when adding a new PostgreSQL-specific dependency that future MariaDB/MySQL compatibility work would need to understand.
  • Do not list every ordinary SQL query here. Only track dependencies that are meaningfully PostgreSQL-specific.
  • If a dependency is intentional, say so plainly.
  • If a future-compatible abstraction exists, note it.

Suggested fields for new entries:

  • dependency or pattern
  • why it is PostgreSQL-specific
  • current locations
  • likely future compatibility strategy
  • migration difficulty: low, medium, or high

Current Support Position

  • PostgreSQL is the only supported database backend.
  • MariaDB/MySQL support is not an active roadmap item.
  • This document exists to preserve optionality for the future, not to require present-day portability work on every change.

Connection And Bootstrap

Hardcoded PostgreSQL DSN and session setup

  • Why PostgreSQL-specific:
    • the current platform abstraction still resolves to a PostgreSQL implementation only
    • PostgreSQL session commands such as SET TIME ZONE and SET application_name remain PostgreSQL-specific behavior
  • Current locations:
    • src/Database.php
    • src/DatabasePlatform/PostgresPlatform.php
  • Likely future strategy:
    • extend the existing platform layer with additional implementations if compatibility work is ever pursued
  • Difficulty:
    • medium

PostgreSQL-only base schema install path

  • Why PostgreSQL-specific:
    • the base schema path is now abstracted, but still resolves only to PostgreSQL
  • Current locations:
    • src/Database.php
    • scripts/install.php
    • src/DatabasePlatform/PostgresPlatform.php
  • Likely future strategy:
    • select base schema by configured engine
    • add database/mysql_schema.sql only if compatibility work is ever pursued
  • Difficulty:
    • medium

Realtime And Event Signaling

LISTEN/NOTIFY and native pg_* usage

  • Why PostgreSQL-specific:
    • depends on PostgreSQL pub/sub behavior
    • uses native PostgreSQL client functions, not generic PDO
  • Current locations:
    • src/Realtime/PostgresEventListener.php
  • Likely future strategy:
    • replace with an event transport abstraction
    • possible backends: Redis pub/sub, polling, queue daemon
  • Difficulty:
    • high

pg_notify(...) from application code

  • Why PostgreSQL-specific:
    • directly calls PostgreSQL notification functions
  • Current locations:
    • src/Realtime/PostgresEventPublisher.php
  • Likely future strategy:
    • notifier interface with PostgreSQL implementation now and alternative backend later
  • Difficulty:
    • high

Trigger-based realtime signaling

  • Why PostgreSQL-specific:
    • uses PL/pgSQL trigger functions and pg_notify
  • Current locations:
    • database/migrations/v1.11.0.54_chat_notify_trigger.php
    • database/migrations/v1.11.0.55_sse_events_table.php
    • database/migrations/v1.11.0.57_sse_events_user_targeting.php
    • database/migrations/v1.11.0.58_dashboard_stats_triggers.php
    • database/migrations/v1.11.0.67_targeted_dashboard_stats_triggers.php
  • Likely future strategy:
    • move event signaling behind a service abstraction
    • possibly replace trigger wakeups with app-side event publishing or Redis
  • Difficulty:
    • high

SQL Dialect Dependencies

RETURNING id

  • Why PostgreSQL-specific:
    • PostgreSQL supports row-returning insert/update syntax heavily used in the codebase
  • Current locations:
    • multiple files under src/, routes/, and database/migrations/
  • Likely future strategy:
    • isolate insert-id retrieval behind repository/service helpers
  • Difficulty:
    • medium

Representative examples:

  • routes/api-routes.php
  • routes/admin-routes.php
  • src/Advertising.php
  • src/BbsDirectory.php
  • src/Chat/ChatMessageService.php
  • src/MessageHandler.php

ON CONFLICT

  • Why PostgreSQL-specific:
    • PostgreSQL upsert syntax and conflict-target behavior are used widely across the codebase
    • some queries rely on PostgreSQL-specific forms such as conflict targets, predicates, and returned-row assumptions
  • Current locations:
    • many files under src/, routes/, database/postgresql_schema.sql, and database/migrations/
  • Likely future strategy:
    • isolate common upsert patterns behind helper methods where practical
    • redesign per-engine upsert behavior explicitly rather than assuming direct syntax parity
  • Difficulty:
    • medium

Representative examples:

  • src/AreaFixManager.php
  • src/BbsDirectory.php
  • src/BulletinManager.php
  • src/PacketBbs/PacketBbsSession.php
  • src/UserMeta.php
  • routes/api-routes.php
  • routes/packetbbs-routes.php

ILIKE

  • Why PostgreSQL-specific:
    • case-insensitive text matching syntax is PostgreSQL-specific
  • Current locations:
    • many search and filtering queries across src/, routes/, and scripts
  • Likely future strategy:
    • controlled collation rules or LOWER(...) LIKE LOWER(...)
    • centralize identity/search comparison helpers where practical
  • Difficulty:
    • medium

Representative examples:

  • src/AddressBookController.php
  • src/AdminController.php
  • src/BbsDirectory.php
  • src/Nodelist/NodelistManager.php
  • routes/api-routes.php

DISTINCT ON

  • Why PostgreSQL-specific:
    • PostgreSQL-only syntax for "first row per group" queries
  • Current locations:
    • routes/webdoor-routes.php
    • scripts/activity_digest.php
    • src/Auth.php
    • database/migrations/v1.11.0.82_echomail_perf.sql
  • Likely future strategy:
    • rewrite with window functions or grouped subqueries
  • Difficulty:
    • medium

PostgreSQL casts such as ?::jsonb

  • Why PostgreSQL-specific:
    • PostgreSQL cast syntax is used directly in application SQL
  • Current locations:
    • routes/webdoor-routes.php
    • routes/admin-routes.php
    • src/AI/UsageRecorder.php
    • src/AiBot/AiBotRepository.php
    • src/MessageHandler.php
    • src/Qwk/QwkBuilder.php
  • Likely future strategy:
    • centralize JSON binding and mutation logic
  • Difficulty:
    • medium

AT TIME ZONE

  • Why PostgreSQL-specific:
    • uses PostgreSQL timezone conversion syntax and semantics in queries and migrations
  • Current locations:
    • routes/admin-routes.php
    • routes/api-routes.php
    • src/Auth.php
    • src/DashboardStatsService.php
    • src/MessageHandler.php
    • many timestamp migrations
  • Note:
    • BinktermPHP already aims to store timestamps in UTC; this dependency is about PostgreSQL-specific conversion, legacy migration, and reporting syntax rather than a non-UTC storage model
  • Likely future strategy:
    • prefer UTC storage and UTC comparison rules
    • move display-timezone conversion out of hot SQL paths where practical
  • Difficulty:
    • medium

Schema And Type Dependencies

SERIAL and BIGSERIAL

  • Why PostgreSQL-specific:
    • PostgreSQL sequence-backed shorthand types
  • Current locations:
    • database/postgresql_schema.sql
    • many files in database/migrations/
  • Likely future strategy:
    • map to engine-specific auto-increment behavior if ever needed
  • Difficulty:
    • low to medium

TIMESTAMPTZ

  • Why PostgreSQL-specific:
    • timezone-aware timestamp type used widely in schema and migrations
  • Current locations:
    • database/postgresql_schema.sql
    • many files in database/migrations/
  • Likely future strategy:
    • define explicit UTC storage policy and per-engine mapping
  • Difficulty:
    • medium

INET

  • Why PostgreSQL-specific:
    • PostgreSQL-native IP address type
  • Current locations:
    • database/postgresql_schema.sql
    • related session/login tables derived from base schema
  • Likely future strategy:
    • likely map to VARCHAR(45) if compatibility is ever pursued
  • Difficulty:
    • low

JSONB

  • Why PostgreSQL-specific:
    • used for mutable metadata, event payloads, settings-like state, and draft/session structures
  • Current locations:
    • many files in database/migrations/
    • related queries under src/ and routes/
  • Likely future strategy:
    • map to JSON with backend-specific helper behavior
    • avoid scattering direct PostgreSQL JSON assumptions
  • Difficulty:
    • medium to high

Representative schema examples:

  • database/migrations/v1.11.0.55_sse_events_table.php
  • database/migrations/v1.11.0.70_drafts_meta_column.sql
  • database/migrations/v1.11.0.73_dashboard_layout.sql
  • database/migrations/v20260517123000_add_packet_bbs_session_state.sql

Indexing, Search, And Constraints

Functional indexes on LOWER(...)

  • Why PostgreSQL-specific:
    • relies on functional-index strategy for case-insensitive lookups and uniqueness
  • Current locations:
    • database/postgresql_schema.sql
    • database/migrations/v1.11.0.15_users_lower_username_index.sql
    • database/migrations/v1.11.0_bbs_directory.sql
  • Likely future strategy:
    • explicit normalization policy plus per-engine index design
  • Difficulty:
    • medium

Trigram search indexes

  • Why PostgreSQL-specific:
    • uses PostgreSQL pg_trgm strategy for fast substring/ILIKE search
  • Current locations:
    • database/migrations/v1.11.0.12_trigram_search_indexes.sql
    • database/migrations/v1.11.0.27_file_search_indexes.sql
  • Likely future strategy:
    • per-engine search/index redesign
    • possibly accept degraded search behavior if compatibility is ever introduced experimentally
  • Difficulty:
    • high

Trigger-enforced username/real-name collision rules

  • Why PostgreSQL-specific:
    • implemented with PostgreSQL trigger/function logic
  • Current locations:
    • database/migrations/v1.10.15_username_realname_collision_trigger.php
  • Likely future strategy:
    • preserve the rule at application level and redesign DB-level enforcement per engine if needed
  • Difficulty:
    • medium

PostgreSQL Catalog, Stats, And Introspection

PostgreSQL system catalog and statistics views

  • Why PostgreSQL-specific:
    • relies on PostgreSQL system views and helper functions such as pg_stat_*, pg_class, pg_namespace, pg_locks, pg_stat_replication, and pg_size_pretty(...)
  • Current locations:
    • src/DatabaseStats.php
    • scripts/binktop.php
    • scripts/database_maintenance.php
    • some tests and analysis scripts
  • Likely future strategy:
    • keep operational reporting behind backend-specific services
    • do not assume these views exist on non-PostgreSQL backends
  • Difficulty:
    • high

PostgreSQL schema introspection in migrations

  • Why PostgreSQL-specific:
    • queries PostgreSQL catalogs and PostgreSQL helper functions such as pg_constraint and pg_get_constraintdef(...)
  • Current locations:
    • database/migrations/v1.11.0.63_mrc_local_presence_user_id.php
  • Likely future strategy:
    • keep advanced migration introspection in PHP migrations
    • branch explicitly by backend if compatibility work is ever pursued
  • Difficulty:
    • medium

Tooling And External Integration

PostgreSQL backup and restore tooling

  • Why PostgreSQL-specific:
    • relies on PostgreSQL command-line tools such as pg_dump and pg_restore
  • Current locations:
    • scripts/backup_database.php
    • scripts/restore_database.php
    • related operational documentation
  • Likely future strategy:
    • introduce backend-specific backup/restore tooling only if additional engines are ever supported
  • Difficulty:
    • medium

MCP server PostgreSQL-specific SQL

  • Why PostgreSQL-specific:
    • the Node MCP server uses PostgreSQL-specific SQL and functions such as pg_catalog.textsend, convert_from(...), and ILIKE
  • Current locations:
    • mcp-server/server.js
  • Likely future strategy:
    • treat MCP database access as its own compatibility surface
    • isolate query rewrites there separately from the PHP app
  • Difficulty:
    • medium to high

Migration Hotspots

PostgreSQL-specific migration language and behavior

  • Why PostgreSQL-specific:
    • many historical migrations use PostgreSQL DDL, PostgreSQL functions, or timezone semantics
  • Current locations:
    • database/migrations/
    • scripts/upgrade.php
  • Likely future strategy:
    • if compatibility is ever pursued, prefer new PHP migrations for portability-sensitive changes
    • some historical migrations may remain PostgreSQL-only and be rolled into a separate base schema instead of replayed verbatim
  • Difficulty:
    • high

Guidance For New Entries

When adding a new entry, keep it brief and concrete. A good note answers:

  1. What is the PostgreSQL-specific dependency?
  2. Where is it?
  3. Was it chosen intentionally?
  4. What would a future compatibility effort probably replace it with?

If the answer is "this is PostgreSQL-specific on purpose because it is clearly better here," that is fine. Write that down and move on.