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.
SKILL.md
| Name | postgres-best-practices |
| Description | 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. |
Project Phoenix

A modern RFID-based student attendance and room management system for educational institutions
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
</details>Note: The
DIRENV_LOG_FORMATenvironment variable no longer works in direnv 2.36.0+ due to a known regression. The TOML config above is the correct solution.
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:
- Frontend: http://localhost:3000
- Backend API: http://localhost:8080
Manual Setup
<details> <summary>Click to expand manual setup instructions</summary>-
Generate SSL certificates (required for GDPR-compliant database connections):
cd config/ssl/postgres ./create-certs.sh cd ../../.. -
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 -
Start services:
docker compose up -d -
Run database migrations:
docker compose run server ./main migrate
ποΈ Architecture
Tech Stack
| Layer | Technology |
|---|---|
| Backend | Go 1.25+, Chi Router, Bun ORM |
| Frontend | Next.js 16, React 19, TypeScript 5 |
| Styling | Tailwind CSS 4 |
| Database | PostgreSQL 17 with SSL encryption |
| Auth | JWT with refresh tokens, NextAuth.js |
| Deployment | Docker Compose, Caddy (production) |
| CI/CD | GitHub 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:
| Schema | Purpose |
|---|---|
auth | Authentication, tokens, permissions |
users | User profiles, students, teachers, staff |
education | Groups and educational structures |
facilities | Rooms and physical locations |
activities | Student activities and enrollments |
active | Real-time session tracking |
schedule | Time and schedule management |
iot | RFID device management |
audit | GDPR compliance logging |
π Documentation
Development
| Command | Description |
|---|---|
go run main.go serve | Start backend server |
go run main.go migrate | Run database migrations |
go run main.go gendoc | Generate API documentation |
pnpm run dev | Start frontend dev server |
pnpm run check | Run lint + typecheck |
API Documentation
cd backend
go run main.go gendoc # Generate routes.md and OpenAPI spec
This creates:
backend/routes.mdβ Complete route documentationbackend/docs/openapi.yamlβ OpenAPI 3.0 specification
Key API Endpoints
| Endpoint | Description |
|---|---|
POST /api/auth/login | Authentication |
GET /api/students | List students |
GET /api/rooms | List rooms |
GET /api/active/groups | Active sessions |
POST /iot/checkin | RFID 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.
- Fork the repository
- Create your feature branch (
git checkout -b feature/amazing-feature) - Commit your changes (
git commit -m 'feat: add amazing feature') - Push to the branch (
git push origin feature/amazing-feature) - 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
- Project Website: moto.nrw
- GitHub: github.com/moto-nrw/project-phoenix
- Issues: Report a bug or request a feature
π 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
</div>