Mini-challenge: TaskFlow v2 — Postgres + JPA capstone

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springdoc</groupId>
        <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
        <version>2.6.0</version>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-database-postgresql</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-testcontainers</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: taskflow
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
    ports:
      - "5432:5432"
    volumes:
      - taskflow-data:/var/lib/postgresql/data

volumes:
  taskflow-data:

docker compose up -d

spring:
  application:
    name: taskflow-api
  datasource:
    url: jdbc:postgresql://localhost:5432/taskflow
    username: app
    password: app
    hikari:
      maximum-pool-size: 10
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        format_sql: true
        jdbc.batch_size: 50
        order_inserts: true
        order_updates: true
        default_batch_fetch_size: 20
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
  mvc:
    problemdetails:
      enabled: true

logging:
  level:
    com.olhub.taskflow: DEBUG
    org.hibernate.SQL: DEBUG                    # log SQL
    org.hibernate.orm.jdbc.bind: TRACE           # log binding param
    org.hibernate.stat: DEBUG                    # statistics

  pattern:
    console: "%d{HH:mm:ss.SSS} [%thread] %-5level [%X{requestId:-no-req}] %logger{30} : %msg%n"

springdoc:
  swagger-ui:
    path: /swagger-ui.html

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(50),
    updated_by VARCHAR(50)
);

CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(500),
    status VARCHAR(20) NOT NULL,
    owner_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(50),
    updated_by VARCHAR(50),
    CONSTRAINT fk_project_owner FOREIGN KEY (owner_id) REFERENCES users(id),
    CONSTRAINT chk_project_status CHECK (status IN ('PLANNING', 'ACTIVE', 'DONE', 'ARCHIVED'))
);

CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    assignee_id BIGINT,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(1000),
    status VARCHAR(20) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    due_date DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(50),
    updated_by VARCHAR(50),
    CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_assignee FOREIGN KEY (assignee_id) REFERENCES users(id),
    CONSTRAINT chk_task_status CHECK (status IN ('TODO', 'IN_PROGRESS', 'DONE')),
    CONSTRAINT chk_task_priority CHECK (priority IN ('LOW', 'MEDIUM', 'HIGH', 'URGENT'))
);

CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    task_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(50),
    updated_by VARCHAR(50),
    CONSTRAINT fk_comment_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_author FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_owner ON projects(owner_id);
CREATE INDEX idx_projects_created ON projects(created_at DESC);

CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_status_priority ON tasks(status, priority);
CREATE INDEX idx_tasks_due_date ON tasks(due_date) WHERE due_date IS NOT NULL;

CREATE INDEX idx_comments_task ON comments(task_id);
CREATE INDEX idx_comments_created ON comments(created_at DESC);

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Getter
public abstract class AuditableEntity {

    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    protected Instant createdAt;

    @LastModifiedDate
    @Column(name = "updated_at", nullable = false)
    protected Instant updatedAt;

    @CreatedBy
    @Column(name = "created_by", updatable = false, length = 50)
    protected String createdBy;

    @LastModifiedBy
    @Column(name = "updated_by", length = 50)
    protected String updatedBy;
}

@Entity
@Table(name = "projects")
@Getter
@Setter
public class Project extends AuditableEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 100)
    private String name;

    @Column(length = 500)
    private String description;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 20)
    private ProjectStatus status;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "owner_id", nullable = false)
    private User owner;

    @OneToMany(mappedBy = "project", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Task> tasks = new ArrayList<>();

    protected Project() {}

    public Project(String name, String description, ProjectStatus status, User owner) {
        this.name = name;
        this.description = description;
        this.status = status;
        this.owner = owner;
    }

    // Helper methods bidirectional
    public void addTask(Task task) {
        tasks.add(task);
        task.setProject(this);
    }

    public void removeTask(Task task) {
        tasks.remove(task);
        task.setProject(null);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof Project p)) return false;
        return id != null && id.equals(p.id);
    }

    @Override
    public int hashCode() {
        return getClass().hashCode();
    }
}

public interface ProjectRepository extends JpaRepository<Project, Long>, JpaSpecificationExecutor<Project> {

    Optional<Project> findByName(String name);

    boolean existsByName(String name);

    @Query("""
        SELECT new com.olhub.taskflow.api.dto.ProjectSummary(
            p.id, p.name, p.status, p.owner.name, COUNT(t.id)
        )
        FROM Project p
        LEFT JOIN p.tasks t
        WHERE (:status IS NULL OR p.status = :status)
        GROUP BY p.id, p.name, p.status, p.owner.name
        """,
        countQuery = """
        SELECT COUNT(p) FROM Project p
        WHERE (:status IS NULL OR p.status = :status)
        """)
    Page<ProjectSummary> findSummaries(@Param("status") ProjectStatus status, Pageable pageable);

    // Overrides findById to eager-load owner + tasks for the detail view (avoids N+1 / LazyInitException).
    // Trade-off: this graph applies to EVERY findById call. For existence-only paths,
    // prefer existsById to skip loading the graph.
    @EntityGraph(attributePaths = {"owner", "tasks"})
    Optional<Project> findById(Long id);
}

@Service
@Transactional(readOnly = true)
@Slf4j
public class ProjectService {

    private final ProjectRepository projectRepo;
    private final UserRepository userRepo;

    public ProjectService(ProjectRepository projectRepo, UserRepository userRepo) {
        this.projectRepo = projectRepo;
        this.userRepo = userRepo;
    }

    @Transactional
    public Project create(CreateProjectRequest req) {
        if (projectRepo.existsByName(req.name())) {
            throw new DuplicateException("Project name '" + req.name() + "' already exists");
        }

        User owner = userRepo.findById(req.ownerId())
            .orElseThrow(() -> new UserNotFoundException(req.ownerId()));

        Project project = new Project(
            req.name(),
            req.description(),
            req.status() != null ? req.status() : ProjectStatus.PLANNING,
            owner
        );

        Project saved = projectRepo.save(project);
        log.info("Created project {} for user {}", saved.getId(), owner.getId());
        return saved;
    }

    public Project findById(Long id) {
        return projectRepo.findById(id)
            .orElseThrow(() -> new ProjectNotFoundException(id));
    }

    public Page<ProjectSummary> list(ProjectStatus status, Pageable pageable) {
        return projectRepo.findSummaries(status, pageable);
    }

    @Transactional
    public void delete(Long id) {
        Project project = findById(id);
        // Cascade ALL + orphanRemoval auto delete tasks + comments
        projectRepo.delete(project);
        log.info("Deleted project {}", id);
    }
}

@Component
public class SecurityAuditor implements AuditorAware<String> {
    public Optional<String> getCurrentAuditor() {
        // Spring Security course wires this via SecurityContext
        return Optional.of("system");
    }
}

@Configuration
@EnableJpaAuditing(auditorAwareRef = "securityAuditor")
public class JpaConfig { }

@SpringBootTest
@AutoConfigureMockMvc
@Testcontainers
@ActiveProfiles("test")
class ProjectControllerIT {

    @Container
    @ServiceConnection                       // Boot 3.1+ auto-config DataSource
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired MockMvc mockMvc;
    @Autowired ProjectRepository repo;
    @Autowired UserRepository userRepo;

    User testUser;

    @BeforeEach
    void setUp() {
        repo.deleteAll();
        userRepo.deleteAll();
        testUser = userRepo.save(new User("[email protected]", "Alice"));
    }

    @Test
    void create_validRequest_returns201() throws Exception {
        mockMvc.perform(post("/api/v1/projects")
                .contentType(MediaType.APPLICATION_JSON)
                .content(json.writeValueAsString(new CreateProjectRequest("Mobile App", null, null, testUser.getId()))))
            .andExpect(status().isCreated())
            .andExpect(jsonPath("$.name").value("Mobile App"));
    }

    @Test
    void list_withStatusFilter_returnsPaged() throws Exception {
        Project p1 = repo.save(new Project("Project A", null, ProjectStatus.ACTIVE, testUser));
        Project p2 = repo.save(new Project("Project B", null, ProjectStatus.PLANNING, testUser));

        mockMvc.perform(get("/api/v1/projects?status=ACTIVE&page=0&size=10"))
            .andExpect(status().isOk())
            .andExpect(jsonPath("$.content.length()").value(1))
            .andExpect(jsonPath("$.content[0].name").value("Project A"));
    }
}