Agent Skill
2/7/2026

postgres-best-practices

Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations. Triggers on tasks involving SQL queries, database schema design, indexes, connection pooling, RLS policies, or performance optimization.

M
moto
7GitHub Stars
2Views
npx skills add moto-nrw/project-phoenix

SKILL.md

Namepostgres-best-practices
DescriptionPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations. Triggers on tasks involving SQL queries, database schema design, indexes, connection pooling, RLS policies, or performance optimization.
<div align="center">

Project Phoenix

moto Logo

A modern RFID-based student attendance and room management system for educational institutions

GitHub Stars GitHub Issues GitHub Pull Requests License GDPR PRs Welcome Contributor Covenant

Go Next.js React TypeScript PostgreSQL Docker

Features β€’ Quick Start β€’ Documentation β€’ Contributing β€’ License

</div>

πŸ“– About

Project Phoenix is a comprehensive room and student management system designed for educational institutions in compliance with European data protection regulations. It leverages RFID technology to track student attendance and location in real-time, providing administrators with powerful tools for monitoring room occupancy, managing activities, and generating detailed analytics.

Why Project Phoenix?

  • Privacy-First Design β€” Built from the ground up with GDPR compliance, featuring configurable data retention, audit logging, and right-to-erasure support
  • Real-Time Visibility β€” Know instantly where students are, which rooms are occupied, and how spaces are being utilized
  • Modern Stack β€” Go backend with Next.js 16 frontend, designed for performance and developer experience
  • Self-Hosted β€” Your data stays on your infrastructure, with full control over security and compliance

✨ Features

Core Functionality

  • 🏷️ RFID Student Tracking β€” Real-time location tracking using RFID technology
  • 🏫 Room Management β€” Monitor room occupancy and usage patterns
  • πŸ‘₯ Group Management β€” Organize students into groups and manage activities
  • πŸ‘¨β€πŸ« Multiple Supervisors β€” Assign multiple supervisors to groups and rooms
  • πŸ“Š Analytics Dashboard β€” Comprehensive reporting and utilization statistics
  • πŸ—“οΈ Schedule Management β€” Handle class schedules and time-based activities
  • 🎯 Activity Tracking β€” Track student participation in various activities

Technical Features

  • πŸ” JWT Authentication β€” Secure authentication with role-based access control
  • βœ‰οΈ Email Workflows β€” SMTP-backed invitations with branded templates and rate-limited password reset
  • πŸš€ RESTful API β€” Well-documented API with OpenAPI specification
  • πŸ“± Responsive UI β€” Modern, mobile-friendly interface
  • 🐳 Docker Support β€” Easy deployment with containerization
  • πŸ”„ Real-time Updates β€” Live tracking of student movements and room occupancy
  • 🌐 i18n Ready β€” Internationalization support built-in

πŸš€ Quick Start

Prerequisites

  • Docker and Docker Compose β€” For running PostgreSQL and optional containerized development
  • Devbox β€” Reproducible development environment (installs Go, Node.js, and all CLI tools)
  • direnv β€” Automatic environment activation when entering the project directory

Why Devbox? We use Devbox to ensure every developer has identical tool versions. No more "works on my machine" issues β€” everyone gets the same Go, Node.js, golangci-lint, etc.

Install Development Tools

<details> <summary><strong>macOS</strong></summary>
# Install Devbox
curl -fsSL https://get.jetify.com/devbox | bash

# Install direnv
brew install direnv

# Add to ~/.zshrc (or ~/.bashrc)
eval "$(direnv hook zsh)"
</details> <details> <summary><strong>Windows (WSL) / Linux</strong></summary>
# Install Devbox
curl -fsSL https://get.jetify.com/devbox | bash

# Install direnv (Ubuntu/Debian)
sudo apt install direnv

# Add to ~/.bashrc (or ~/.zshrc)
eval "$(direnv hook bash)"
</details> <details> <summary><strong>Optional: Suppress direnv output</strong></summary>

By default, direnv prints all exported environment variables when entering the project. To silence this output, create a direnv config file:

mkdir -p ~/.config/direnv
cat > ~/.config/direnv/direnv.toml << 'EOF'
[global]
log_format = "-"
log_filter = "^$"
EOF

Note: The DIRENV_LOG_FORMAT environment variable no longer works in direnv 2.36.0+ due to a known regression. The TOML config above is the correct solution.

</details>

One-Command Setup

# Clone the repository
git clone https://github.com/moto-nrw/project-phoenix.git
cd project-phoenix

# Allow direnv to activate the environment (one-time)
direnv allow

# Run the automated setup script
./scripts/setup-dev.sh

# Start all services
docker compose up -d

When you cd into the project, direnv automatically activates Devbox and you'll see:

phoenix dev ready - go 1.25.5, node 20.20.0

All tools (Go, Node, pnpm, golangci-lint, etc.) are now available.

The application will be available at:

Manual Setup

<details> <summary>Click to expand manual setup instructions</summary>
  1. Generate SSL certificates (required for GDPR-compliant database connections):

    cd config/ssl/postgres
    ./create-certs.sh
    cd ../../..
    
  2. Configure environment files:

    cp backend/dev.env.example backend/dev.env
    cp frontend/.env.local.example frontend/.env.local
    # Edit the files with your settings
    
  3. Start services:

    docker compose up -d
    
  4. Run database migrations:

    docker compose run server ./main migrate
    
</details>

πŸ—οΈ Architecture

Tech Stack

LayerTechnology
BackendGo 1.25+, Chi Router, Bun ORM
FrontendNext.js 16, React 19, TypeScript 5
StylingTailwind CSS 4
DatabasePostgreSQL 17 with SSL encryption
AuthJWT with refresh tokens, NextAuth.js
DeploymentDocker Compose, Caddy (production)
CI/CDGitHub Actions

Project Structure

project-phoenix/
β”œβ”€β”€ backend/                   # Go backend API
β”‚   β”œβ”€β”€ api/                   # HTTP handlers and routes
β”‚   β”œβ”€β”€ auth/                  # Authentication logic
β”‚   β”œβ”€β”€ database/              # Migrations and repositories
β”‚   β”œβ”€β”€ models/                # Domain models
β”‚   └── services/              # Business logic
β”œβ”€β”€ frontend/                  # Next.js frontend
β”‚   └── src/
β”‚       β”œβ”€β”€ app/               # Next.js App Router
β”‚       β”œβ”€β”€ components/        # UI components
β”‚       └── lib/               # Utilities and API clients
β”œβ”€β”€ deployment/                # Production configurations
β”œβ”€β”€ docs/                      # Documentation
└── docker-compose.yml         # Development environment

Database Schema

The database uses PostgreSQL schemas to organize tables by domain:

SchemaPurpose
authAuthentication, tokens, permissions
usersUser profiles, students, teachers, staff
educationGroups and educational structures
facilitiesRooms and physical locations
activitiesStudent activities and enrollments
activeReal-time session tracking
scheduleTime and schedule management
iotRFID device management
auditGDPR compliance logging

πŸ“š Documentation

Development

CommandDescription
go run main.go serveStart backend server
go run main.go migrateRun database migrations
go run main.go gendocGenerate API documentation
pnpm run devStart frontend dev server
pnpm run checkRun lint + typecheck

API Documentation

cd backend
go run main.go gendoc          # Generate routes.md and OpenAPI spec

This creates:

  • backend/routes.md β€” Complete route documentation
  • backend/docs/openapi.yaml β€” OpenAPI 3.0 specification

Key API Endpoints

EndpointDescription
POST /api/auth/loginAuthentication
GET /api/studentsList students
GET /api/roomsList rooms
GET /api/active/groupsActive sessions
POST /iot/checkinRFID check-in

Testing

# Backend tests
cd backend && go test ./...

# Frontend checks
cd frontend && pnpm run check

πŸ›‘οΈ Security & Privacy

This project handles sensitive student data and implements comprehensive security measures:

  • SSL/TLS Encryption β€” All database connections use SSL (sslmode=require)
  • GDPR Compliance β€” Configurable data retention, audit logging, right-to-erasure
  • Role-Based Access β€” Teachers only see data for students in their assigned groups
  • Secure Defaults β€” No secrets in code, environment-based configuration

Reporting Vulnerabilities: Please see SECURITY.md for our security policy and responsible disclosure process.


πŸ—ΊοΈ Roadmap

  • RFID student tracking
  • Multi-supervisor support
  • GDPR compliance features (data retention, audit logging)
  • Email invitation workflow
  • Password reset with rate limiting
  • Mobile companion app
  • Real-time push notifications
  • Advanced analytics and reporting
  • Multi-language UI

See the open issues for a full list of proposed features and known issues.


🀝 Contributing

Contributions are what make the open source community amazing! Any contributions you make are greatly appreciated.

  1. Fork the repository
  2. Create your feature branch (git checkout -b feature/amazing-feature)
  3. Commit your changes (git commit -m 'feat: add amazing feature')
  4. Push to the branch (git push origin feature/amazing-feature)
  5. Open a Pull Request against development

Please read CONTRIBUTING.md for details on our code of conduct, development setup, and the process for submitting pull requests.

Note: By contributing, you agree to our Contributor License Agreement.


πŸ“„ License

Distributed under a Source-Available License. See LICENSE for more information.


πŸ“¬ Contact


πŸ™ Acknowledgments

  • Chi Router β€” Lightweight, idiomatic Go HTTP router
  • Bun ORM β€” Fast and simple SQL-first ORM for Go
  • Next.js β€” The React framework for production
  • Tailwind CSS β€” Utility-first CSS framework
  • Shields.io β€” Badges for this README

<div align="center">

Made with ❀️ by moto

⬆ Back to top

</div>
Skills Info
Original Name:postgres-best-practicesAuthor:moto