Pharmacy POS System Development

A development skill for building and maintaining a pharmacy management system with strict database protection rules. This system handles inventory management, order processing, payment handling, and pharmacist tracking with an existing MySQL database schema that must never be modified.

Instructions

When working on this pharmacy POS system, you MUST follow these critical rules:

🚨 CRITICAL DATABASE PROTECTION

**NEVER modify the MySQL database in any way:**

**ALWAYS use the existing database connection:**

📋 EXISTING DATABASE SCHEMA

You MUST use ONLY these exact table structures with their specific column names:

**medicines table:**

**inventory table:**

**orders table:**

**order_details table:**

**pharmacists table:**

**payments table:**

**payment_types table:**

**categories table:**

**accounts table:**

🔧 DEVELOPMENT GUIDELINES

**Project Structure:**

1. Keep all backend code in the `backend/` directory

2. Use existing middleware from `backend/middleware/`

3. Maintain API routes in `backend/routes/`

4. Store file uploads in `backend/uploads/`

5. Write application logs to `backend/logs/`

**Code Style:**

1. Follow the existing project structure and patterns

2. Use naming conventions that match the database schema exactly

3. Maintain consistency with existing code

4. Write SQL queries using the exact column names from the schema

5. Handle database relationships according to the existing foreign keys

**API Development:**

1. Build APIs that work with the existing table structure

2. Use proper parameterized SQL queries to prevent SQL injection

3. Validate input data before database operations

4. Return appropriate HTTP status codes

5. Handle errors gracefully with meaningful messages

**Database Operations:**

1. Always refer to exact table and column names from the schema above

2. Respect existing relationships between tables

3. Use joins properly for related data (e.g., medicines ↔ categories, orders ↔ order_details)

4. Never ignore foreign key constraints

5. Write efficient queries that minimize database load

📝 EXAMPLE PATTERNS

**Correct Database Query:**

```javascript

const query = 'SELECT drug_name, dosage, base_price FROM medicines WHERE category_id = ?';

db.query(query, [categoryId], callback);

```

**Correct API Route Structure:**

```javascript

// backend/routes/medicines.js

router.get('/medicines/:id', (req, res) => {

const query = 'SELECT * FROM medicines WHERE drug_id = ?';

// ... implementation

});

```

**Correct Error Handling:**

```javascript

db.query(query, [params], (error, results) => {

if (error) {

console.error('Database error:', error);

return res.status(500).json({ error: 'Database operation failed' });

}

res.json(results);

});

```

🎯 SYSTEM CONTEXT

This pharmacy management system provides:

⚠️ PROHIBITED ACTIONS

You must NEVER:

1. Create database setup or migration scripts

2. Modify `package.json` to add database setup commands

3. Suggest changes to the existing schema

4. Use column names that don't exist in the schema

5. Create duplicate tables or views

6. Run DDL statements (CREATE, ALTER, DROP)

7. Modify the database connection configuration

✅ REQUIRED ACTIONS

You must ALWAYS:

1. Reference the exact table and column names from this skill

2. Use the existing database connection from `backend/config/database.js`

3. Write queries that match the existing schema precisely

4. Validate that column names exist before writing queries

5. Test queries against the known schema structure

6. Respect the existing table relationships and foreign keys

7. Follow the established project file structure

8. Maintain backward compatibility with existing code

🔒 SECURITY CONSIDERATIONS

1. Use parameterized queries to prevent SQL injection

2. Never expose sensitive data like passwords in API responses

3. Validate and sanitize all user inputs

4. Implement proper authentication and authorization

5. Log security-relevant events

6. Handle pharmacist credentials securely (hashed passwords)

7. Protect patient and order information appropriately

Constraints