BDNS Spanish Government Grants System Expert

Expert guidance for working with the BDNS Web Application - a production-ready Spanish Government Grants Database search system that creates a local PostgreSQL mirror of Spain's official BDNS (Base de Datos Nacional de Subvenciones) for ultra-fast search capabilities.

What This Skill Helps With

This skill provides comprehensive guidance for developing and maintaining a high-performance grants search system that:

1. **Mirrors Spanish Government Data**: Downloads all grants from official BDNS API

2. **Enables Ultra-Fast Search**: Local PostgreSQL with Spanish full-text search

3. **Provides Rich Web Interface**: Modern React/Next.js search application

4. **Maintains Data Freshness**: Multiple sync modes for different use cases

5. **Handles Massive Scale**: Designed for 500k+ records with efficient processing

6. **Manages User Authentication**: Complete email verification and 2FA system

System Architecture

Core Components (All Verified Working)

Data Flow

```

External BDNS API → Sync Engine → PostgreSQL → Search API → Frontend

↓

Progress Tracking & Logging

```

Key Instructions for AI Agent

1. Always Check Current System Status First

Before making any changes or suggestions, verify the current state:

```bash

Check database statistics and sync status

curl http://localhost:3000/api/sync | jq '.'

Check container status

docker-compose ps

Monitor application logs

docker-compose logs -f web

Monitor database logs

docker-compose logs -f postgres

```

2. Understanding the Database Sync Modes

The system has three distinct sync modes:

**Incremental Sync** (`npm run db:sync`)

**Full Sync** (`npm run db:sync:full`)

**Complete Migration** (`npm run db:sync:complete`)

3. Working with the Search System

The search system has dual capabilities:

**Local Database Search** (Primary)

**External API Search** (Fallback)

**Key Search Features:**

4. Email Verification System Architecture

**Database Schema:**

```sql

users:

- email_verified: boolean

- email_verification_token: varchar(255)

- two_factor_enabled: boolean

- two_factor_email: boolean

two_factor_codes:

- user_id: uuid

- code: varchar(6)

- expires_at: timestamp (10 minutes)

- used: boolean

```

**Key API Endpoints:**

**Email Configuration:**

```env

SMTP_HOST=smtp.gmail.com

SMTP_PORT=587

[email protected]

SMTP_FROM="BDNS Web" <[email protected]>

```

5. Critical File Locations

**API Routes** (`src/app/api/`):

**Components** (`src/components/`):

**Data Layer** (`src/lib/`):

**Scripts** (`scripts/`):

6. Development Commands

**Development Workflow:**

```bash

npm run dev # Start Next.js dev server

npm run build # Production build

npm run lint # ESLint checking

npm run type-check # TypeScript validation

```

**Docker Operations:**

```bash

npm run docker:dev # Docker Compose dev mode

docker-compose up -d # Start services

docker-compose logs -f # View logs

```

**Database Synchronization:**

```bash

npm run db:sync # Incremental sync

npm run db:sync:full # Full sync (2023+)

npm run db:sync:complete # Complete migration (2008-2026)

npm run db:reset # Reset schema

```

7. PostgreSQL Optimization Patterns

When working with the database:

8. Type Safety Requirements

All BDNS data structures are typed in `src/types/bdns.ts`:

Always maintain type safety when modifying data structures.

9. Email Verification Best Practices

**Session Synchronization:**

```typescript

// Automatic verification status updates with intelligent caching

// Cache: 30 seconds in development, 5 minutes in production

// Location: src/lib/auth.ts session callback

```

**Template Compatibility:**

**2FA Flow:**

1. User enables 2FA in profile

2. Login validates credentials first

3. System sends 6-digit code via email

4. User enters code in special form

5. Login completes after verification

10. Troubleshooting Common Issues

**Email Verification Status Mismatch:**

**2FA Not Activating:**

**Sync Performance:**

**Search Not Finding Results:**

Example Usage

When a user asks to:

**"Add a new search filter"**

1. Check `src/components/filters/` for existing patterns

2. Update `src/lib/bdns-local.ts` query builder

3. Add TypeScript types in `src/types/bdns.ts`

4. Test with Spanish text examples

5. Verify pagination still works

**"Fix email verification issues"**

1. Check current status: Query users table for `email_verified`

2. Verify session cache timing in `src/lib/auth.ts`

3. Check SMTP configuration in environment variables

4. Test email template rendering

5. Monitor logs for email sending errors

**"Optimize sync performance"**

1. Check current sync status via API endpoint

2. Review batch size settings in `scripts/sync-bdns-data.js`

3. Verify database connection pooling

4. Monitor PostgreSQL performance

5. Consider switching sync modes based on needs

Important Constraints

Notes

This is a production-ready system with real government API integration. When making changes:

1. Always verify current system state first

2. Understand the sync mode implications

3. Maintain Spanish language search optimization

4. Respect type safety throughout

5. Test email functionality across clients

6. Monitor performance impact on large datasets

7. Document any architectural changes

The system successfully handles massive datasets (500k+ records) with sophisticated sync mechanisms, real-time monitoring, and optimized search capabilities.