## Implementation Plan

### Root Cause
The `votes` table lacks a UNIQUE constraint on `(user_uid, target_uid)». Duplicate rows (from race conditions or prior bugs) cause `get_user_votes` to return unpredictable values, including `0` when all live rows are soft-deleted. The fix must prevent duplicates and clean up existing ones.

### Changes Required

#### 1. Add UNIQUE constraint to `votes` table
**File:** `database/schema.py` (or wherever the `votes` table is defined – locate the `CREATE TABLE votes` statement).

- Add a UNIQUE constraint on `(user_uid, target_uid)`. Use `UNIQUE(user_uid, target_uid)`.
- No change to column types or existing indexes.

#### 2. Add data migration to deduplicate existing votes
**File:** `database/__init__.py` or the function called during app startup (e.g., `init_db()`).

Before creating tables or after migration:  
Run a SQL statement that removes duplicate rows, keeping only one row per `(user_uid, target_uid)` — preferably the row that is **not** soft-deleted (`deleted_at IS NULL`) if any, otherwise the most recent row. Example:

```sql
DELETE FROM votes WHERE rowid NOT IN (
    SELECT MIN(rowid) FROM votes
    GROUP BY user_uid, target_uid
    HAVING COUNT(*) = 1
    UNION
    SELECT MIN(rowid) FROM votes
    WHERE deleted_at IS NULL
    GROUP BY user_uid, target_uid
    UNION
    SELECT MAX(rowid) FROM votes
    WHERE rowid IN (
        SELECT MAX(rowid) FROM votes
        GROUP BY user_uid, target_uid
        HAVING COUNT(deleted_at) = COUNT(*)  -- all rows are soft-deleted
    )
);
```

(Simpler alternative: keep the row with `deleted_at IS NULL` if any, otherwise the latest `created_at`. This ensures no duplicates remain.)

Place this migration before the `CREATE UNIQUE INDEX` or after the table creation, so it runs only once (e.g., guarded by a version check). Since this is a development setup, a simple one-time `sqlite3` execution in `init_db()` is acceptable.

#### 3. Defensive change in `apply_vote()` (optional but recommended)
**File:** `devplacepy/content.py` line 209.

Change the `find_one` call to explicitly filter `deleted_at=None` instead of relying on the default. This avoids ambiguity even if a duplicate exists.

Current line:
```python
existing = votes.find_one(user_uid=user["uid"], target_uid=target_uid, target_type=target_type)
```

Change to:
```python
existing = votes.find_one(user_uid=user["uid"], target_uid=target_uid, target_type=target_type, deleted_at=None)
```

Then adjust the revive logic: if `existing` is `None`, search for a soft-deleted row to revive:
```python
if existing is None:
    existing = votes.find_one(user_uid=user["uid"], target_uid=target_uid, target_type=target_type)
```

This ensures we always operate on the live row first, preventing duplicate live rows.

#### 4. Add API-level test to verify `my_vote != 0` in feed after vote
**File:** create a new test or add to an existing test file (e.g., `tests/api/votes.py` or `tests/api/posts/feed.py`).

Write a test that:
1. Creates a user and a post (use existing fixtures).
2. Sends `POST /votes/post/{uid}` with `value=1`.
3. Sends `GET /feed` with `Accept: application/json`.
4. Asserts that `data["posts"][0]["my_vote"] == 1`.

Also add a similar test for downvote (`value=-1`) and for toggling off (`value=0`).

### Definition of Done

- [ ] UNIQUE constraint added to `votes` table in `database/schema.py` (or equivalent schema definition file).
- [ ] Data migration executed in `init_db()` (or appropriate startup script) that deduplicates existing rows.
- [ ] `apply_vote()` in `devplacepy/content.py` updated to use `deleted_at=None` filter and fallback to revive soft-deleted row.
- [ ] New API test(s) added that verify `my_vote` is correct in JSON feed response after vote actions (upvote, downvote, unvote).
- [ ] Lint passes: `pip install -q ruff && ruff check .` produces no new warnings (pre-existing E402 warnings in `tests/api/votes.py` are permissible).
- [ ] Unit tests pass: `pip install -e '.[dev]' -q && make test-unit` – the pre-existing failure in `tests/unit/content.py::test_owns_instance_true_for_creator` is expected and unrelated; no new failures are introduced.
- [ ] The execution agent confirms the change resolves the reported symptom: after the fix, a reproducible test (manual or automated) shows `my_vote` returning the correct value for a voted post.