You are tasked with building a full-stack MVP application using Next.js 14 App Router, React, TypeScript, Tailwind CSS, and Drizzle ORM with a SQLite database. The application, named 'Optimal Savings Assistant', will help users compare Certificate of Deposit (CD) rates, simulate reinvestment strategies, and manage their savings goals. The application must feature a multi-page structure, robust API routes for CRUD operations, and a clear database schema.
**Application Structure & Technologies:**
* **Frontend**: Next.js 14 (App Router), React, TypeScript, Tailwind CSS.
* **Backend**: Next.js API Routes.
* **Database**: SQLite (for MVP simplicity) managed by Drizzle ORM.
* **Authentication**: Implement basic user registration and login functionality (e.g., using NextAuth.js or a custom solution for MVP).
**Database Schema (Drizzle ORM):**
1. **`users` table:**
* `id`: INTEGER PRIMARY KEY AUTOINCREMENT
* `email`: TEXT UNIQUE NOT NULL
* `password_hash`: TEXT NOT NULL
* `created_at`: TEXT DEFAULT CURRENT_TIMESTAMP
* `updated_at`: TEXT DEFAULT CURRENT_TIMESTAMP
2. **`cd_rates` table:** (Simulated data, actual scraping is out of scope for MVP coding)
* `id`: INTEGER PRIMARY KEY AUTOINCREMENT
* `bank_name`: TEXT NOT NULL
* `term_months`: INTEGER NOT NULL
* `apy`: REAL NOT NULL
* `min_deposit`: REAL
* `is_promotional`: BOOLEAN DEFAULT FALSE
* `last_updated`: TEXT DEFAULT CURRENT_TIMESTAMP
3. **`user_savings_goals` table:**
* `id`: INTEGER PRIMARY KEY AUTOINCREMENT
* `user_id`: INTEGER NOT NULL (FOREIGN KEY REFERENCES `users`(`id`))
* `goal_name`: TEXT NOT NULL
* `target_amount`: REAL NOT NULL
* `target_date`: TEXT
* `current_saved`: REAL DEFAULT 0.0
* `created_at`: TEXT DEFAULT CURRENT_TIMESTAMP
4. **`user_cd_investments` table:**
* `id`: INTEGER PRIMARY KEY AUTOINCREMENT
* `user_id`: INTEGER NOT NULL (FOREIGN KEY REFERENCES `users`(`id`))
* `bank_name`: TEXT NOT NULL
* `term_months`: INTEGER NOT NULL
* `apy`: REAL NOT NULL
* `initial_amount`: REAL NOT NULL
* `start_date`: TEXT NOT NULL
* `maturity_date`: TEXT NOT NULL
* `is_reinvested`: BOOLEAN DEFAULT FALSE
* `linked_goal_id`: INTEGER (FOREIGN KEY REFERENCES `user_savings_goals`(`id`), NULLABLE)
5. **`simulations` table:**
* `id`: INTEGER PRIMARY KEY AUTOINCREMENT
* `user_id`: INTEGER NOT NULL (FOREIGN KEY REFERENCES `users`(`id`))
* `simulation_name`: TEXT NOT NULL
* `parameters`: TEXT NOT NULL (JSON string of input parameters)
* `results`: TEXT NOT NULL (JSON string of simulation output)
* `created_at`: TEXT DEFAULT CURRENT_TIMESTAMP
**Pages (App Router Structure):**
* `/app/layout.tsx`: Root layout.
* `/app/page.tsx`: Homepage (marketing page, call to action).
* `/app/auth/login/page.tsx`: User login form.
* `/app/auth/register/page.tsx`: User registration form.
* `/app/dashboard/page.tsx`: (Protected) User's personal dashboard displaying goals, current investments, and quick insights.
* `/app/compare/page.tsx`: (Protected) CD rate comparison tool. Displays rates from `cd_rates` table with filters (term, bank).
* `/app/simulate/page.tsx`: (Protected) Reinvestment and CD laddering simulator. Users can input scenarios and view projected returns.
* `/app/goals/page.tsx`: (Protected) CRUD interface for managing `user_savings_goals`.
* `/app/investments/page.tsx`: (Protected) CRUD interface for managing `user_cd_investments`.
**API Routes (for CRUD Operations):**
* `/app/api/auth/register/route.ts`: POST to create a new user.
* `/app/api/auth/login/route.ts`: POST to authenticate a user.
* `/app/api/rates/route.ts`:
* GET: Fetch all or filtered `cd_rates`. (Public or protected for advanced features).
* POST: (Admin only for MVP) Add new `cd_rates`.
* `/app/api/goals/route.ts`:
* GET: Fetch `user_savings_goals` for the authenticated user.
* POST: Create a new `user_savings_goal`.
* `/app/api/goals/[id]/route.ts`:
* PUT: Update an existing `user_savings_goal`.
* DELETE: Delete a `user_savings_goal`.
* `/app/api/investments/route.ts`:
* GET: Fetch `user_cd_investments` for the authenticated user.
* POST: Create a new `user_cd_investment`.
* `/app/api/investments/[id]/route.ts`:
* PUT: Update an existing `user_cd_investment`.
* DELETE: Delete a `user_cd_investment`.
* `/app/api/simulations/route.ts`:
* POST: Run a new simulation (reinvestment or laddering) based on user input, save parameters and results to `simulations` table.
* GET: Fetch past `simulations` for the authenticated user.
**Core Functionalities & Implementation Details:**
1. **Database Setup**: Configure Drizzle ORM to connect to SQLite. Define all schemas in `db/schema.ts` and set up `db/index.ts` for database connection.
2. **Authentication**: Implement simple token-based authentication (e.g., using `jsonwebtoken` for session management). Protect dashboard and other user-specific pages.
3. **CD Rate Comparison**: On `/compare`, fetch `cd_rates` using the `/api/rates` GET endpoint. Display rates in a sortable and filterable table. Provide input fields for filtering by term, bank name.
4. **Reinvestment Simulator**: On `/simulate`, allow users to input an initial investment amount, a starting CD term (e.g., 6 months), and an assumed reinvestment rate/term. Calculate the cumulative return over a longer period (e.g., 5 years) and compare it to a single long-term CD. Allow saving simulation results via POST to `/api/simulations`.
5. **CD Laddering Tool**: As part of `/simulate`, allow users to define a CD ladder strategy (e.g., invest 1/5 of capital into 1, 2, 3, 4, 5-year CDs). Visualize the maturity schedule and projected returns. Allow saving simulation results.
6. **Savings Goal Management**: On `/goals`, implement full CRUD for `user_savings_goals