backend-data · diff
git:20260923.b5acb6d to git:20260924.15dc64a
123 added, 18 removed. Audit A to A.
---
name: backend-data
- description: JPA entities, repositories, MyBatis projections, PostgreSQL, Flyway, MapStruct, IDs, audit, and soft-delete rules. Load when persistence or migrations change.
+ description: Use when adding or changing a JPA entity, column, relation, repository query, MyBatis read model, Flyway migration, MapStruct mapper, Snowflake ID, audit field, soft delete, or permission seed data in this Spring Boot backend.
---
- # Backend Data — Canonical
+ # Backend Data — Playbook
- > Canonical data skill. Detailed rules: `skills/backend/DATA.md`.
- > Requires: `AGENTS.md`, `.agents/skills/backend-core/SKILL.md`.
+ > **Role:** HOW to change persistence safely. Binding facts + evidence: `skills/backend/DATA.md` (read it too).
+ > **Requires:** `backend-core`. **Often paired with:** `backend-api`, `backend-security`, `backend-testing`.
- ## When to load
+ ## Know this first
- JPA entities/repositories, MyBatis mappers/XML, PostgreSQL, Flyway migrations, MapStruct persistence mapping, IDs, audit, or soft-delete work.
+ - Local dev runs Hibernate `ddl-auto: update` and **Flyway is disabled**. Your app starting locally proves nothing about the migration. You must still write the migration.
+ - IDs are Snowflake `Long` from `Id#generateId`. Never add `@GeneratedValue`.
+ - JPA = CRUD, filters, simple lookups. MyBatis = joined/paged DTO read models only. No new MyBatis writes.
- ## Implementation path
+ ## Recipe A — add a column to an existing entity
- 1. Find the existing entity and repository or MyBatis interface/XML pair. Use JPA for CRUD, specifications, and simple lookups; use MyBatis with `vo/Paging` for joined or paged DTO read models.
- 2. Put write transactions in `serviceImpl`. Review every mapped field because MapStruct's ignored-target policy can hide omissions. For a new standard CRUD service/repository, use `docs/agent/STANDARD_CRUD_SERVICE_REPOSITORY.md`.
- 3. For owner-scoped rows, inspect `backend-security`; permission checks alone do not replace owner predicates. MyBatis and native SQL must state soft-delete and ownership conditions explicitly.
- 4. List `src/main/resources/db/migration/` before choosing a Flyway version. Add a migration instead of editing an existing one, then select PostgreSQL/pgvector evidence per `backend-testing`.
+ 1. Add the field to `model/{Model}.java` with `@Column(name = "snake_case", comment = "...")`.
+ 2. Add a migration `V{next}__add_{column}_to_{table}.sql` (list `src/main/resources/db/migration/` for `{next}`):
- ## Rules (summary — binding details in `skills/backend/DATA.md`)
+ ```sql
+ ALTER TABLE {table} ADD COLUMN IF NOT EXISTS {column} character varying(255);
+ COMMENT ON COLUMN {table}.{column} IS '...';
+ ```
- - IDs: Snowflake `Long` via `@PrePersist`; never `IDENTITY`/`SEQUENCE`.
- - Audit via `Auditable`/`SoftDeletedAuditable` + `AuditAwareImpl`; pass actor explicitly in async/Reactor paths.
- - Soft delete requires field + `@SQLDelete` + `@SQLRestriction`; MyBatis/native SQL must add `deleted=false` + ownership predicates.
- - Repositories extend `BaseRepository` + `JpaSpecificationExecutor` where filtering is needed.
- - Flyway: `src/main/resources/db/migration/`, `V{version}__{snake_case}.sql`; inspect current versions before adding one; never rewrite deployed migrations; validate PG/pgvector SQL on disposable PostgreSQL, not H2.
- - MapStruct: `*Mapper.java`, `componentModel="spring"`, `IGNORE` unmapped targets — review every field intentionally.
+ 3. Add the field to the DTO (with validation + i18n message key) and check the mapper maps it in both directions.
+ 4. If the column is searchable by `_keyword`, add it to the controller's `buildSpecification(request, List.of(...))`.
+
+ ## Recipe B — add a new entity (table)
+
+ 1. Choose the base class from the table in `DATA.md` (usually `SoftDeletedAuditable<Long>`).
+ 2. Write the entity:
+
+ ```java
+ @GenSourceableTable(createPermission = false) // optional: only if the dev generator should see it
+ @NoArgsConstructor @Getter @Setter
+ @Entity
+ @Table(name = "{table}", comment = "...",
+ indexes = {@Index(columnList = "deleted"), @Index(columnList = "created_user")})
+ @SQLDelete(sql = "UPDATE {table} SET deleted = true WHERE id=?")
+ @SQLRestriction("deleted=false")
+ public class {Model} extends SoftDeletedAuditable<Long> {
+ @Column(name = "name", length = 125, nullable = false, comment = "...")
+ private String name;
+
+ @ManyToOne(fetch = FetchType.LAZY)
+ @JoinColumn(name = "parent_id", comment = "FK -> Ref table: parent (id). ...")
+ private Parent parent;
+
+ public static Sort getSort() { return Sort.by(Sort.Direction.ASC, "name"); }
+ }
+ ```
+
+ 3. Migration `V{next}__create_{table}_table.sql`: `CREATE TABLE` with `id bigint NOT NULL PRIMARY KEY`, the audit columns of the base class, `deleted boolean DEFAULT false`, FKs, indexes, and `COMMENT ON` lines (copy the style of `V3__create_app_user_face_table.sql`).
+ 4. If the entity has admin CRUD: seed permissions (Recipe D).
+ 5. Continue with the repository/service/controller in `docs/agent/STANDARD_CRUD_SERVICE_REPOSITORY.md`.
+
+ ## Recipe C — add a MyBatis read model (joins / projections)
+
+ 1. `mybatis/{Model}Mybatis.java`:
+
+ ```java
+ @Mapper
+ public interface {Model}Mybatis {
+ List<{Model}Dto> findAllByOwner(@Param("page") Paging page, @Param("ownerId") Long ownerId);
+ }
+ ```
+
+ 2. `src/main/resources/mybatis/{Model}Mybatis.xml` — `namespace` = the interface FQCN, `id` = method name:
+
+ ```xml
+ <select id="findAllByOwner" resultType="com.bekaku.api.spring.dto.{Model}Dto">
+ SELECT t.id, t.name, p.name AS parentName
+ FROM {table} t LEFT JOIN parent p ON p.id = t.parent_id
+ WHERE t.deleted is false AND t.created_user = #{ownerId}
+ <if test="page.sortfield != null">ORDER BY ${page.sortfield} ${page.sortmode}</if>
+ LIMIT #{page.limit} OFFSET #{page.offset}
+ </select>
+ ```
+
+ 3. The controller builds `Paging` with `getPaging(pageable, SORT_FIELDS)` and a non-empty allow-list. Every other value uses `#{...}`.
+ 4. Write `deleted is false` and the owner predicate yourself — `@SQLRestriction` does not apply to MyBatis.
+
+ ## Recipe D — seed permissions for a new resource
+
+ 1. Generate IDs (one per row, same Snowflake shape as the app):
+
+ ```bash
+ python3 -c "import time; b=(int(time.time()*1000)-1672531200000)<<22; print('\n'.join(str(b+i) for i in range(5)))"
+ ```
+
+ 2. In the table's migration (or a new one):
+
+ ```sql
+ INSERT INTO permission (id, code, module, description, operation_type) VALUES
+ (<id1>, '{table}_list', '{table}', 'Permission for {table}_list', 'CRUD'),
+ (<id2>, '{table}_view', '{table}', 'Permission for {table}_view', 'CRUD'),
+ (<id3>, '{table}_add', '{table}', 'Permission for {table}_add', 'CRUD'),
+ (<id4>, '{table}_edit', '{table}', 'Permission for {table}_edit', 'CRUD'),
+ (<id5>, '{table}_delete', '{table}', 'Permission for {table}_delete', 'CRUD')
+ ON CONFLICT (code) DO NOTHING;
+ ```
+
+ 3. Only if the task asks for it, grant them to a role:
+
+ ```sql
+ INSERT INTO role_permission (app_role, permission)
+ SELECT 350888314967953409, id FROM permission WHERE module = '{table}'
+ ON CONFLICT DO NOTHING;
+ ```
+
+ 4. Add labels to `i18n/permission/messages.properties` and `messages_th.properties`: `permission.{table}_list=...` etc.
+
+ ## Done checklist
+
+ - [ ] Every schema change has a new `V{n}__*.sql`; no existing migration was edited.
+ - [ ] Entity uses the right base; soft-delete entities have both `@SQLDelete` and `@SQLRestriction`.
+ - [ ] No `@GeneratedValue`, no `@Data` on entities with relations, relations are `LAZY`.
+ - [ ] Mapper reviewed field by field (unmapped targets are silently ignored).
+ - [ ] MyBatis SQL has `deleted is false` + owner predicate; only allow-listed sort uses `${}`.
+ - [ ] Writes happen in a `@Transactional` service method.
+ - [ ] PostgreSQL-specific SQL was validated on real PostgreSQL, or the report says it was not (`backend-testing`).
+
+ ## Common mistakes
+
+ | Mistake | Fix |
+ |---|---|
+ | "It started locally, so the migration works" | Dev uses `ddl-auto: update`; validate the SQL on disposable PostgreSQL or say it was not validated |
+ | Editing `V1`–`V6` | Add `V{next}` |
+ | `limit #{offset}, #{limit}` (MySQL) | `LIMIT #{page.limit} OFFSET #{page.offset}` |
+ | Relying on `deleted` column alone | Add `@SQLDelete` + `@SQLRestriction` |
+ | New permission but nobody can use it | Assign via `role_permission` (only when asked) — there is no admin bypass |
+ | `ON CONFLICT (id)` on permission seeds | `ON CONFLICT (code)` — `code` is the unique business key |