pgapi-is-sql-native-multi-tenant
IN derived (depth 1)
PgApi operates as a SQL-native multi-tenant implementation: all operations execute directly against PostgreSQL with no in-memory Network object constructed, composite primary keys on all tables provide project-level isolation, and each public method is a single committed transaction.
Summary
PgApi talks directly to PostgreSQL for everything rather than building a graph in memory first, which means multiple writers can safely work at the same time. Every table is keyed by project, so different tenants are isolated at the row level, and each method call is its own atomic transaction that either fully commits or fully rolls back.
Justifications
SL — Three architectural properties (no in-memory object, composite keys, per-method transactions) characterize PgApi as a distinct SQL-native implementation pattern
Antecedents (all must be IN):
- pgapi-no-in-memory-network — `PgApi` executes all operations as direct SQL against PostgreSQL — no in-memory `Network` object is ever constructed, enabling concurrent writers
- pgapi-multi-tenant-composite-keys — All PgApi tables use composite primary keys `(id, project_id)` for multi-tenancy, with JSONB columns (not TEXT) for antecedents, outlist, and metadata
- pgapi-one-transaction-per-method — Each PgApi public method is a single PostgreSQL transaction that commits or rolls back at the end; __exit__ rolls back on exception
Dependents
These beliefs depend on this one:
- atomicity-is-backend-independent — Both storage backends enforce atomic isolated operations through backend-appropriate mechanisms: the SQLite backend uses context-managed load/save with write-flag gating and per-function transaction scope, while PostgreSQL uses per-method transactions with composite-key multi-tenancy — achieving the same transactional guarantee at different architectural levels
- dual-backend-atomicity-is-feature-complete — Both storage backends provide atomic isolated operations with complete API coverage — the SQLite backend's context-managed mutations and PgApi's per-method transactions cover the same full set of operations
- pg-multi-tenancy-is-referentially-complete — PgApi's multi-tenant isolation with composite primary keys and application-level BFS propagation prevents all cross-project data leakage and ensures consistent truth maintenance — unless antecedent references stored as JSONB arrays lack foreign key constraints, allowing phantom node references within a project.
- pgapi-is-complete-sql-reimplementation — PgApi is a complete SQL-native reimplementation of the in-memory Network: it re-implements core algorithms (BFS propagation, entrenchment scoring, nogood resolution) directly in SQL with multi-tenant composite keys and per-method transaction semantics, achieving full behavioral parity.
- pgapi-referential-integrity-is-database-enforced — PgApi achieves complete referential integrity: application-level bidirectional enforcement (write-time validation and outlist-aware dependent querying) is complemented by database-level foreign key constraints, preventing orphaned justification references even under direct SQL manipulation outside the application layer.