showcase

Shipped: Library loans

What did you build, and how can a reviewer see that the acceptance criteria are met? I built a Library Loans API using Node.js, TypeScript, Express.js, and PostgreSQL. It manages library books, physical book copies, members, and loan records. The API provides a paginated GET /loans endpoint that returns loan details along with book and member information. I added database constraints to maintain data integrity and a partial unique index to prevent the same physical book copy from having multiple active loans. I seeded the database with 12,000 loan records and optimized the listing query using a composite index on loaned_at and id. In my local testing, the query execution time improved from approximately 328.63 ms to 2.25 ms, meeting the under-200-ms performance target in that test environment. The acceptance criteria can be verified as follows: - Database design: Review db/schema.sql to see the related tables, foreign keys, constraints, and indexes. - Data volume: Run SELECT COUNT() FROM loans; to verify that at least 10,000 loan records exist after running the seed script. - Prevention of duplicate active loans: Review the unique_active_loan_per_copy partial unique index in db/schema.sql. - API functionality: Start the server and test GET /loans?limit=20&offset=0. Try different pagination values and invalid inputs to verify validation. - Performance: Review the before-and-after query plans and execution times documented in README.md. These can also be reproduced using EXPLAIN (ANALYZE, BUFFERS). - Automated testing: Run npm test to execute the API tests. - Build verification: Run npm run build to verify that the TypeScript code compiles successfully. The repository includes setup instructions, database schema and seed scripts, API documentation, performance analysis, and automated tests so the reviewer can reproduce the results locally. Repository: https://github.com/sindhu-dev02/library-loans-api Built for the Verified Backend Internship · a data model and the queries on it

showcase

internship

backend-task-data

Add a comment

0/2000