Database Design

The system contains 38 tables organized into 8 functional groups. All tables use integer primary keys except where noted. Timestamps (created_at, updated_at) are present on all tables.

Schema Overview

Core Location

municipalities

Top-level geographic unit. Links barangays to their city or municipality.

barangays

Primary jurisdictional unit. All residents, households, officials, and transactions are scoped to a barangay.

Design note: The field is named municity_code but stores an integer FK after the barangay ID migration. The eGovPH code string is preserved separately for external API calls.

Users & Authentication

users (29 columns)

Central identity record for all residents and officials on the platform. Implements Filament multi-tenancy via barangay_id and municity_id. Supports soft deletes.

Key fields:

password_reset_tokens

Laravel built-in. email (PK), token (hashed), created_at.

personal_access_tokens

Laravel Sanctum. 10 columns. Polymorphic token ownership with abilities, last_used_at, expires_at.

Roles & Permissions (Spatie)

Standard Spatie Laravel Permission tables. Five tables total:

Permission cache is invalidated on every role assignment or revocation via PermissionRegistrar::forgetCachedPermissions().

Barangay Governance

barangay_terms (8 columns)

One row = one official's tenure in one position at one barangay. The authorization middleware checks this table on every document signing attempt.

On term creation, the user is automatically assigned the corresponding Spatie role.

delegations (6 columns)

Records a signing authority grant from a Captain to another official.

All delegations are automatically voided when the granting Captain's barangay_terms.ended_at is populated.

Housing & Households

houses (7 columns)

Physical dwelling unit. Foundational jurisdictional anchor — a household's barangay_id flows from its house.

households (9 columns)

Economic/social unit within a house. Multiple households can occupy one house (e.g., extended family + boarders).

Design note: household_head_id has no database-level FK to avoid a circular dependency between households and household_member_profiles. Head assignment is managed at the application layer.

household_member_profiles (12 columns)

Junction table linking a user to a household with role, presence, and tenure metadata.

Partial unique indexes:

-- Prevents double-dipping for social services
CREATE UNIQUE INDEX idx_unique_active_primary_resident
ON household_member_profile (user_id)
WHERE presence_status = 'Primary' AND end_date IS NULL;

-- Ensures each household has exactly one active Head
CREATE UNIQUE INDEX idx_single_active_household_head
ON household_member_profile (household_id)
WHERE role = 'Head' AND end_date IS NULL;

families (8 columns)

Nuclear family unit. Managed automatically by UserObserver. Linked to father_id, mother_id, barangay_id, and optionally a household_id.

residency_requests (16 columns)

Temporary holding record for pending residency applications. Drives the household linking workflow on official approval.

Document System

document_type_properties (9 columns)

Master catalog of all supported document types.

document_transactions (18 columns)

Central record for every document request or issuance. The full lifecycle is tracked here.

Design decision: approver_id references barangay_terms.id rather than users.id. This creates a legally defensible audit trail — even years later, the system can answer exactly who was legally authorized to sign a document and in what capacity.

transaction_requirements (8 columns)

Stores actual supporting documents submitted per transaction.

Document Detail Tables

Each document type has a dedicated satellite table linked 1-to-1 with document_transactions via transaction_id. These tables store only the fields unique to that type.

DocumentTransaction::getSpecificDetails() resolves the correct detail record dynamically using the doc_type_model field on document_type_properties.

System & Infrastructure