Skip to content

project_mtp_postgres_ltree.md

Latest commit

 

History

History
43 lines (35 loc) · 2.3 KB

project_mtp_postgres_ltree.md

File metadata and controls

43 lines (35 loc) · 2.3 KB
Raw
name project-mtp-postgres-ltree
description Multi-Tenant Platform repo uses PostgreSQL 17+ exclusively and uses ltree extension for hierarchical data.
metadata
type
project

For the multi-tenant-platform repo at D:\Projects\mtp\mtp-docs (GitHub: paramhq/mtp-docs):

Why: User stated explicitly: "we will be using postgres v17 or later only in this, we will also use ltree for hierarchical data". Treat as hard architectural constraint.

How to apply:

Database

  • PostgreSQL 17+ only. No SQL Server, no other RDBMS suggestions in this repo's docs/code.
  • Use Postgres-specific features freely: jsonb, gen_random_uuid(), native JSON path operators (->, ->>, @?, @@), partial indexes, generated columns, range types.
  • Hierarchical data via ltree extension (path-as-string with . separators, GiST indexes for ancestor/descendant queries).

Implications for existing docs

  • All SQL Server-flavored guidance must be revised:
    • NVARCHAR(MAX) → text or jsonb
    • JSON column → jsonb (not stringly-typed nvarchar)
    • JSON_VALUE(col, '$.path') → col -> 'key' (jsonb) or col ->> 'key' (text)
    • Computed columns + indexed JSON_VALUE → GIN indexes on jsonb or expression indexes
    • uniqueidentifier → uuid
    • RAISERROR(..., 16, 1) (state 50000+) → RAISE EXCEPTION USING ERRCODE = '...' or domain-specific SQLSTATE
    • OPENJSON shredding → jsonb_to_recordset / jsonb_array_elements
  • SP-message i18n migration plan (page 08) needs Postgres ERRCODE strategy instead of state-number mapping.
  • Page 09 ExtensionData column = jsonb, indexable via GIN or expression indexes — no need for persisted computed columns.

ltree-specific use cases in this platform

  • Account/cost-centre hierarchies
  • Org chart / role hierarchy
  • Permissions tree
  • Class/section/student hierarchies in school ERP scenarios
  • Category trees, menu hierarchies, location/region trees

Stack alignment

  • This repo's stack note must explicitly call out Postgres 17 + ltree, not "SQL Server".
  • ORM: assume EF Core 9 with Npgsql provider, or Dapper with Npgsql, or both.
  • Connection string keys: Postgres style.

Related: pairs with [[project-gnschoolerp-api-conventions]] which still uses SQL Server — those conventions do NOT carry over to the multi-tenant-platform repo.