إنتقل إلى المحتوى الرئيسي

Runtime schema audit

Migration files describe intended history. They do not prove the shape or security posture of a running database. ShamBus therefore keeps a separate read-only runtime audit.

Run it against the local shambus-db container:

pnpm audit:db-schema

Run it against an explicitly configured database connection:

DB_HOST=database-host \
DB_PORT=5432 \
DB_USER=postgres \
DB_NAME=postgres \
DB_PASSWORD='provided-out-of-band' \
pnpm audit:db-schema

The script never prints the password and the SQL transaction is READ ONLY.

Hard failures​

The command fails when:

  • the checked-out migration count or latest filename differs from public.applied_migrations;
  • Supabase API roles, the migration ledger, or the runtime RLS verifier are missing;
  • the canonical RLS posture verifier reports a violation;
  • an anon or authenticated table grant bypasses RLS;
  • a public index is invalid; or
  • a client role can create objects in public;
  • a public security-definer function inherits the caller's search path; or
  • an RLS table without policies differs from the exact service-only deny-by-default allowlist; or
  • migration versions or filenames are duplicated in the live ledger; or
  • a Company is missing its durable cash-only payment-policy row, or that row enables a non-cash traveler payment method.

Review findings​

The JSON report also inventories schema debt that requires classification rather than automatic deletion:

  • total public functions, application-owned public functions, and extension-owned public functions as separate counts, so a different extension installation schema cannot be mistaken for ShamBus application drift;
  • tables without primary keys;
  • public tables without RLS, including internal metadata tables;
  • the exact deny-by-default service-only tables that intentionally have no policies;
  • security-definer functions without a fixed search_path;
  • unvalidated check or foreign-key constraints; and
  • invalid indexes.

These findings are not silently treated as safe. Each must be either repaired or documented as an intentional, non-client-exposed exception before the Phase 1 inventory gate closes.

Error-level function findings on 2026-08-25​

The runtime schema/security audit above passes through migration 00307; it is separate from PL/pgSQL static analysis. The independent command supabase db lint --schema public --level error --fail-on error found no error-level issue in the Company-confirmation functions added by migrations 00305 through 00307. It did confirm pre-existing defects in these legacy functions, so the database-wide function-lint gate is not green:

  • cancel_round_trip and create_round_trip_booking: lowercase values invalid for the current booking_status enum;
  • get_or_create_passenger, get_linked_booking, and reserve_seats_atomic: ambiguous PL/pgSQL column references;
  • get_upcoming_maintenance: invalid extract input type;
  • verify_admin_with_permission and verify_admin_with_permission_by_email: returned enum/text type mismatch;
  • is_seat_available: integer/text seat-number comparison; and
  • get_seat_price and reserve_seats_safe: references to obsolete tables.

These procedures require individual caller and replacement-path audits before repair or removal. They are not release evidence for migration 00305, but they remain Phase 1 schema-debt blockers.

Function parity between environments must be evaluated with counts.application_functions. counts.functions intentionally includes extension-owned functions when an environment installs an extension such as pgcrypto or uuid-ossp in public; production installs those extensions in the dedicated extensions schema.

Company payment-policy completeness​

Every Company must have exactly one company_payment_settings row. Migration 00297_company_cash_payment_settings_provisioning.sql backfills existing Companies and provisions future Companies through an idempotent AFTER INSERT trigger. While online providers remain development-only, the database enforces accept_cash = true, accept_shamcash = false, and accept_bank_transfer = false; API-level fallback objects are resilience only, not a substitute for the durable tenant policy row.