Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database for RDM Digital is built on a federated and sovereign architecture designed to manage digital identity, territorial mapping (POIs), economic commerce, and digital manuscripts. The system leverages Supabase for its backend, utilizing PostgreSQL features such as Row Level Security (RLS), custom functions for role-based access control, and specialized tables to track "Node Zero" operations and the TAMV (Territorio, Arte, Memoria, Vida) framework.
Sources: README.md:1-15, scripts/001_rdm_schema.sql:1-10
Core Architectural Entities
The schema is organized into several primary modules that reflect the project's "Heptafederated" structure. This includes user profiles, federations, territorial points of interest (POIs), and immutable records like audit signatures and manuscripts.
Entity Relationship Overview
The following diagram illustrates the primary relationships between core tables in the RDM system.
The schema establishes a strict hierarchy where the federations table acts as the high-level grouping for technology, economy, culture, and other domains.
Sources: scripts/001_rdm_schema.sql:35-135, supabase/migrations/20260512080845_110fe96e-4027-403f-98c8-f6d495c1bf4c.sql:45-120
Identity and Access Control
RDM Digital uses a sophisticated role-based access control (RBAC) system integrated with Supabase Auth. Profiles extend the default authentication users with specific domain roles such as arquitecto, gobierno, or ciudadano.
Roles and Permissions
Permissions are enforced via PostgreSQL Row Level Security (RLS) policies and a helper function has_role.
| Role | Description | Source |
|---|---|---|
arquitecto | The primary architect role with full administrative oversight. | scripts/001_rdm_schema.sql:19 |
gobierno | Government-level access for assembly and oversight. | scripts/001_rdm_schema.sql:19 |
comercio | Merchant role for managing businesses and products. | scripts/001_rdm_schema.sql:19 |
admin | Internal Supabase admin role used for writes across critical tables. | supabase/migrations/20260512080845_110fe96e-4027-403f-98c8-f6d495c1bf4c.sql:2 |
Implementation of RBAC
The system uses a has_role function to check if the authenticated auth.uid() possesses the required role or the global admin role before allowing operations.
create or replace function public.has_role(required_role text)
returns boolean language sql stable security definer set search_path = public as $$
select exists (
select 1 from public.profiles p
where p.id = auth.uid() and (p.role = required_role or p.role = 'admin')
);
$$;
Sources: supabase/migrations/202605080001_lovable_federated_kernel.sql:3-10, scripts/001_rdm_schema.sql:15-30
Territorial and Georeferenced Data
The territory_pois (and its alias pois in migrations) table stores georeferenced locations within the Real del Monte municipality and surrounding areas. These points are linked to specific federations for management.
| Field | Type | Description |
|---|---|---|
name | text | Name of the location. |
category | text | Type (mineria, templo, plaza, etc.). |
lat | double precision | Latitude coordinate. |
lng | double precision | Longitude coordinate. |
federation_id | text | Reference to the managing federation. |
Sources: scripts/001_rdm_schema.sql:51-68, supabase/migrations/20260512080845_110fe96e-4027-403f-98c8-f6d495c1bf4c.sql:45-65
Economic System and Commerce
The economic module manages merchants, products, and a dual-balance wallet system (MXN cents and TAMV credits). It also includes automated triggers to handle business suspension based on payment dues.
Business Lifecycle and Billing
Businesses are categorized by commerce_type (e.g., tiendita, plateria, hotel), which dictates their registration and monthly fees.
A specific trigger trg_suspend_overdue_businesses monitors the next_payment_due column to enforce payment policies automatically.
Sources: supabase/migrations/202605080001_lovable_federated_kernel.sql:103-125, scripts/001_rdm_schema.sql:103-125
Evolution and Migrations
The database evolution is tracked through versioned migration scripts. These scripts not only define the schema but also seed essential "canonical" data, such as the initial 7 federations and the foundational manuscripts.
Canonical Seed Data
Seed scripts ensure that the environment is populated with the base philosophical and operational nodes of the project.
- Federations Seeded:
educativa,cultural,economica,tecnologica,salud,comunicacion,gubernamental. - Manuscripts Seeded:
tomo-i-genesis,tomo-ii-kernel,tomo-iii-territorio,tomo-iv-geopolitica.
Sources: scripts/002_rdm_seed.sql:8-60, supabase/migrations/20260512080845_110fe96e-4027-403f-98c8-f6d495c1bf4c.sql:14-42
Audit and Immutability
To ensure forensic resilience, the system includes an audit_signatures table. This table stores SHA-256 hashes of resources to provide a "third technical witness" for administrative actions.
| Field | Type | Description |
|---|---|---|
resource_type | text | The type of entity being signed. |
resource_id | text | The specific ID of the resource. |
sha256 | text | Forensic hash of the state. |
signed_at | timestamptz | Timestamp of the audit entry. |
Sources: supabase/migrations/20260512080845_110fe96e-4027-403f-98c8-f6d495c1bf4c.sql:100-115, scripts/001_rdm_schema.sql:220-235
Summary
The RDM Digital database schema is a comprehensive implementation of a digital sovereignty framework. It integrates standard e-commerce and identity features with specialized modules for territorial management and forensic auditing. By using PostgreSQL triggers and RLS, the system maintains a high degree of automation and security, ensuring that all data remains auditable and aligned with the "Arquitecto's" vision for a sovereign territory.