# GeoLynx — Login Sharing Protection Plan

## Problem

GeoLynx is a named-user licensed platform. Login sharing — where one set of credentials is used concurrently by multiple people — undermines the licensing model, introduces accountability gaps in the audit trail, and creates a security risk (an ex-employee's credentials may still be in use by others).

**Current state:** The system has no protection against concurrent sessions. `users_login_log` records every login with a `session_id` and `last_active_datetime`, giving us the data infrastructure needed to detect and prevent sharing, but no enforcement logic acts on it.

---

## Approach

The most effective approach for named-user SaaS is **single active session enforcement**: when a user logs in, any previously active session for that account is immediately invalidated. If someone else is using the same credentials, they are logged out the moment the account holder (or the sharer) logs in again.

This is combined with admin visibility tools, brute-force protection, and logout audit logging to give a complete picture.

---

## Changes Overview

| # | Change | Where | Priority |
|---|---|---|---|
| 1 | Store active session token in DB; validate on every request | Schema + login.php + login_check.php | High |
| 2 | Invalidate previous session on new login | login.php | High |
| 3 | Record logout timestamp in login log | logout.php + schema | High |
| 4 | Brute-force lockout (failed login attempts) | Schema + login.php | High |
| 5 | Admin: active sessions view + kill session | admin_load.php + admin_save.php + admin UI | Medium |
| 6 | Admin: concurrent session alert view | admin_load.php | Medium |
| 7 | Notify user on new login from unrecognised device | login.php (email) | Low / Future |

---

## 1. Single Session Enforcement

### How it works

On login, GeoLynx writes the new PHP `session_id` to a dedicated `users.active_sessions` table. On every page request, `loginCheck()` queries this table to confirm the current `session_id` is still the registered active session for that user. If another login has occurred in the meantime, the old session_id will have been replaced and the earlier user is logged out with a clear message.

### Why a separate table rather than a column on `users.users`

A dedicated table allows future extension to N-sessions-per-user (e.g. allowing one desktop + one mobile session) without altering the users table again. It also keeps the session record self-contained with its own created/expired timestamps.

### Schema — `users.active_sessions`

```sql
CREATE TABLE IF NOT EXISTS users.active_sessions (
    session_record_id  bigserial PRIMARY KEY,
    user_id            bigint NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    session_id         varchar(128) NOT NULL,
    login_log_id       bigint REFERENCES users.users_login_log(id) ON DELETE SET NULL,
    created_at         timestamp without time zone DEFAULT now(),
    last_seen_at       timestamp without time zone DEFAULT now(),
    UNIQUE (user_id)   -- one active session per user; replace on new login
);

CREATE INDEX IF NOT EXISTS idx_active_sessions_session_id ON users.active_sessions(session_id);
```

The `UNIQUE (user_id)` constraint enforces one active session per user at the DB level. An `INSERT ... ON CONFLICT (user_id) DO UPDATE` on login atomically replaces any previous session record.

### Schema — add `logout_at` to `users_login_log` (Step 3 combined)

```sql
ALTER TABLE users.users_login_log
    ADD COLUMN IF NOT EXISTS logout_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS logout_reason varchar(30);
-- logout_reason values: 'user_logout' | 'session_timeout' | 'displaced' | 'admin_kill'
```

`displaced` = the session was invalidated because the same account logged in elsewhere.

---

### Code changes

#### `www/fn/login.php` — on successful login

After `session_regenerate_id(true)` and inserting into `users_login_log`:

```php
// 1. Mark any existing active session as displaced
$stmt = $pdo->prepare("
    UPDATE users.users_login_log
    SET logout_at = now(), logout_reason = 'displaced'
    WHERE id = (
        SELECT login_log_id FROM users.active_sessions WHERE user_id = :uid
    )
    AND logout_at IS NULL
");
$stmt->execute(['uid' => $user['id']]);

// 2. Register (or replace) the active session record
$stmt = $pdo->prepare("
    INSERT INTO users.active_sessions (user_id, session_id, login_log_id)
    VALUES (:uid, :sid, :log_id)
    ON CONFLICT (user_id) DO UPDATE
        SET session_id    = EXCLUDED.session_id,
            login_log_id  = EXCLUDED.login_log_id,
            created_at    = now(),
            last_seen_at  = now()
");
$stmt->execute([
    'uid'    => $user['id'],
    'sid'    => session_id(),
    'log_id' => $logRow['id'],  // from the RETURNING id on login_log insert
]);
```

#### `www/fn/login_check.php` — `loginCheck()` function

Replace the current simple check with a DB-validated check:

```php
function loginCheck($value, $pdo = null)
{
    if (!isset($_SESSION['id']) || !isset($_SESSION['logged_in'])) {
        if ($value == 'page') {
            header('Location: index.php?do=login');
            exit;
        }
        return false;
    }

    // Validate session against DB (requires $pdo to be passed)
    if ($pdo !== null) {
        $stmt = $pdo->prepare("
            SELECT session_id FROM users.active_sessions WHERE user_id = :uid
        ");
        $stmt->execute(['uid' => $_SESSION['id']]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$row || $row['session_id'] !== session_id()) {
            // Session has been displaced or does not exist — force logout
            session_unset();
            session_destroy();
            if ($value == 'page') {
                header('Location: index.php?do=login&reason=displaced');
                exit;
            }
            return false;
        }
    }

    return true;
}
```

Pass `$pdo` from `index.php` where `loginCheck('page', $pdo)` is called. AJAX endpoints pass `loginCheck('func', $pdo)` and check the false return.

Show a message on the login page when `?reason=displaced`:

> *"Your session was ended because this account was signed in on another device. If this wasn't you, contact your administrator."*

#### `www/fn/login_check.php` — `updateActiveUser()` function

Also update `last_seen_at` in `active_sessions`:

```php
function updateActiveUser($id, $pdo)
{
    if ($id >= 1) {
        $pdo->prepare("UPDATE users.users SET date_last_active = now() WHERE id = :id")
            ->execute([':id' => $id]);

        if (!empty($_SESSION['login_log_id'])) {
            $pdo->prepare("UPDATE users.users_login_log SET last_active_datetime = now() WHERE id = :log_id")
                ->execute([':log_id' => $_SESSION['login_log_id']]);
        }

        // Keep active_sessions fresh (used for idle detection)
        $pdo->prepare("UPDATE users.active_sessions SET last_seen_at = now() WHERE user_id = :uid")
            ->execute([':uid' => $id]);
    }
}
```

#### `www/fn/logout.php` — record logout timestamp

```php
session_start();

if (!empty($_SESSION['login_log_id'])) {
    require 'fn/db.php';
    $pdo->prepare("
        UPDATE users.users_login_log
        SET logout_at = now(), logout_reason = 'user_logout'
        WHERE id = :log_id
    ")->execute([':log_id' => $_SESSION['login_log_id']]);

    // Remove from active sessions
    $pdo->prepare("DELETE FROM users.active_sessions WHERE user_id = :uid")
        ->execute([':uid' => $_SESSION['id']]);
}

session_unset();
session_destroy();
header('Location: index.php?do=login');
exit;
```

---

## 2. Brute-Force Login Protection

Without lockout, credentials obtained from sharing can be brute-forced or credential-stuffed. Add failed attempt tracking to prevent this.

### Schema — add to `users.users`

```sql
ALTER TABLE users.users
    ADD COLUMN IF NOT EXISTS failed_login_attempts integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS locked_until          timestamp without time zone;
```

### Code changes — `www/fn/login.php`

**Before** the password verify step, check for lockout:

```php
if ($user['locked_until'] !== null && strtotime($user['locked_until']) > time()) {
    $remaining = ceil((strtotime($user['locked_until']) - time()) / 60);
    die("Account temporarily locked. Try again in {$remaining} minute(s).");
}
```

**On failed password:**

```php
$stmt = $pdo->prepare("
    UPDATE users.users
    SET failed_login_attempts = failed_login_attempts + 1,
        locked_until = CASE
            WHEN failed_login_attempts + 1 >= 10 THEN now() + interval '30 minutes'
            WHEN failed_login_attempts + 1 >= 5  THEN now() + interval '5 minutes'
            ELSE locked_until
        END
    WHERE id = :id
");
$stmt->execute([':id' => $user['id']]);
die('Incorrect username / password combination!');
```

**On successful login**, reset the counter:

```php
$pdo->prepare("
    UPDATE users.users
    SET failed_login_attempts = 0, locked_until = NULL
    WHERE id = :id
")->execute([':id' => $user['id']]);
```

**Thresholds:**

| Failed attempts | Lockout duration |
|---|---|
| 5 | 5 minutes |
| 10 | 30 minutes |
| Admin reset | Immediate unlock (admin_save.php `userToggleActive` or new `userUnlock` mode) |

---

## 3. Admin: Active Sessions View & Kill

### New `admin_load.php` modes

#### `active_sessions_list`

Returns all currently active sessions for admin visibility:

```sql
SELECT
    u.id            AS user_id,
    u.username,
    u.email,
    c.company_name,
    s.session_id,
    s.created_at    AS session_started,
    s.last_seen_at,
    l.ip_address,
    l.user_agent,
    EXTRACT(EPOCH FROM (now() - s.last_seen_at)) / 60 AS idle_minutes
FROM users.active_sessions s
JOIN users.users u ON u.id = s.user_id
JOIN users.companies c ON c.company_id = u.company
LEFT JOIN users.users_login_log l ON l.id = s.login_log_id
ORDER BY s.last_seen_at DESC;
```

#### `session_history`

Returns the login log with `logout_at` and `logout_reason` for a given `user_id` — extends the existing `userLoginHistory()` mode.

#### `concurrent_session_alerts`

Returns login log entries where the same user logged in from a different IP within 30 minutes of a previous login, suggesting credential sharing:

```sql
SELECT
    a.user_id,
    u.username,
    a.timestamp     AS login_at,
    a.ip_address,
    a.user_agent,
    b.timestamp     AS prior_login_at,
    b.ip_address    AS prior_ip
FROM users.users_login_log a
JOIN users.users_login_log b
    ON  b.user_id = a.user_id
    AND b.timestamp < a.timestamp
    AND b.timestamp > a.timestamp - interval '30 minutes'
    AND b.ip_address != a.ip_address
JOIN users.users u ON u.id = a.user_id
WHERE u.id NOT IN (1,2,3,4)
ORDER BY a.timestamp DESC
LIMIT 200;
```

### New `admin_save.php` modes

#### `session_kill`

Allows an admin to forcibly end a user's active session:

```php
function sessionKill($pdo, $targetUserId) {
    // Mark the login log row as admin-killed
    $pdo->prepare("
        UPDATE users.users_login_log
        SET logout_at = now(), logout_reason = 'admin_kill'
        WHERE id = (
            SELECT login_log_id FROM users.active_sessions WHERE user_id = :uid
        )
        AND logout_at IS NULL
    ")->execute(['uid' => $targetUserId]);

    // Remove from active sessions
    $pdo->prepare("DELETE FROM users.active_sessions WHERE user_id = :uid")
        ->execute(['uid' => $targetUserId]);
}
```

#### `user_unlock`

Resets `failed_login_attempts = 0, locked_until = NULL` for a given user — callable from the admin user list when a user is locked out legitimately.

---

## 4. Admin UI — `?do=admin_users`

Add a new tab or sub-section to the existing admin users page:

### "Active Sessions" tab

- Tabulator table from `active_sessions_list`
- Columns: Username, Company, IP Address, Browser (truncated user_agent), Session Started, Last Active, Idle (mins)
- Row action: **Kill Session** button → calls `session_kill` → removes row from table
- Auto-refresh every 60 seconds

### "Suspicious Logins" tab

- Tabulator table from `concurrent_session_alerts`
- Columns: Username, Login Time, IP, Prior Login Time, Prior IP
- Read-only — for investigation. Admin can click through to the user's full login history.

### User list additions

- Add a **Locked** badge next to users where `locked_until > now()`
- Add **Unlock** action button → calls `user_unlock`
- Add **Kill Session** inline if the user has an active session

---

## 5. `?reason=displaced` Message on Login Page

In `www/html/html_body_login.php` (or equivalent login HTML), add:

```php
<?php if (isset($_GET['reason']) && $_GET['reason'] === 'displaced'): ?>
<div class="alert alert-warning">
    Your session was ended because this account was signed in from another location.
    If this wasn't you, please contact your administrator immediately.
</div>
<?php endif; ?>
```

---

## Build Order

1. **DDL** — `sql/login_sharing_protection.sql`:
   - Create `users.active_sessions`
   - `ALTER TABLE users.users_login_log` — add `logout_at`, `logout_reason`
   - `ALTER TABLE users.users` — add `failed_login_attempts`, `locked_until`

2. **`login.php`** — displace old session, register new session in `active_sessions`, reset failed attempt counter

3. **`login.php`** — add brute-force lockout check + failed attempt increment

4. **`login_check.php`** — extend `loginCheck()` to validate session against `active_sessions`; update `updateActiveUser()` to refresh `last_seen_at`

5. **`logout.php`** — record `logout_at` / `logout_reason`, delete from `active_sessions`

6. **`admin_load.php`** — add `active_sessions_list`, `concurrent_session_alerts`, extend `session_history`

7. **`admin_save.php`** — add `session_kill`, `user_unlock`

8. **Admin UI** — Active Sessions tab + Suspicious Logins tab + user list badges/actions

9. **Login page** — `?reason=displaced` alert message

- This part  of build order completed on 2026-04-04
---

## 6. Multi-Device Sessions — Allowing Legitimate Concurrent Logins

### The problem with strict single-session enforcement

The current implementation displaces any existing session on new login. This works well against sharing, but it breaks the legitimate case where one real person uses GeoLynx on their laptop in the office and then picks up their phone on the way home — the laptop session would be killed.

---

### Option A — One session per device class (your initial idea)

Classify the incoming `User-Agent` string at login time into a small number of device classes (`desktop`, `mobile`, `tablet`) and allow one active session per user **per class**. A new desktop login displaces the previous desktop session but leaves the mobile session untouched.

**How it works:**

`active_sessions` gets a `device_class` column. The `UNIQUE` constraint changes from `(user_id)` to `(user_id, device_class)`. The upsert on login targets the conflict on `(user_id, device_class)` rather than just `user_id`.

```sql
ALTER TABLE users.active_sessions
    ADD COLUMN IF NOT EXISTS device_class varchar(20) NOT NULL DEFAULT 'desktop';

ALTER TABLE users.active_sessions
    DROP CONSTRAINT IF EXISTS active_sessions_user_id_key,
    ADD CONSTRAINT active_sessions_user_id_device_class_key UNIQUE (user_id, device_class);
```

Device classification at login (PHP, using the `user_agent` string):

```php
function classifyDevice(string $userAgent): string {
    $ua = strtolower($userAgent);
    if (preg_match('/ipad|android(?!.*mobile)|tablet/i', $ua)) return 'tablet';
    if (preg_match('/iphone|android.*mobile|mobile/i', $ua))   return 'mobile';
    return 'desktop';
}
```

The session registration upsert becomes:

```php
$pdo->prepare("
    INSERT INTO users.active_sessions (user_id, session_id, login_log_id, device_class)
    VALUES (:uid, :sid, :log_id, :device)
    ON CONFLICT (user_id, device_class) DO UPDATE
        SET session_id   = EXCLUDED.session_id,
            login_log_id = EXCLUDED.login_log_id,
            created_at   = now(),
            last_seen_at = now()
")->execute(['uid' => $uid, 'sid' => session_id(), 'log_id' => $logId, 'device' => $deviceClass]);
```

**Pros:** Simple to implement on top of what exists. Intuitive to explain to users ("one login per device type").

**Cons:** User-Agent device classification is imprecise — Chrome Dev Tools can spoof any class. A sharer who knows this could stay on `desktop` deliberately. Also, two people sharing credentials on two laptops would both appear as `desktop` and one would still be displaced.

---

### Option B — Named / trusted devices (more robust)

Rather than inferring device type from the User-Agent, issue each browser a persistent `device_id` cookie (a random UUID) on first visit. `active_sessions` stores `device_id` instead of `device_class`. The user gets a quota of N concurrent sessions (e.g. 3).

```sql
ALTER TABLE users.active_sessions
    ADD COLUMN IF NOT EXISTS device_id varchar(64),
    ADD COLUMN IF NOT EXISTS device_label varchar(80);  -- "Chrome on MacBook" shown in UI

ALTER TABLE users.active_sessions
    DROP CONSTRAINT IF EXISTS active_sessions_user_id_key,
    ADD CONSTRAINT active_sessions_user_id_device_id_key UNIQUE (user_id, device_id);
```

On login, PHP reads `$_COOKIE['glx_device_id']`; if absent, generates one and sets a long-lived cookie. The session row is keyed to `(user_id, device_id)`.

**Pros:** A genuine second sharer on a different device gets a genuinely different `device_id`. If the user has 3 active sessions and a fourth `device_id` appears, the oldest session is displaced. The admin Active Sessions view can show device labels ("Chrome on Windows", "Safari on iPhone") making sharing much more visible. Users can see and revoke their own sessions from a "My Sessions" profile page.

**Cons:** Shared/public computers reuse the same cookie, so two people on the same browser instance would share the device slot. Slightly more complex to implement. The concurrent session limit (e.g. 3) needs to be configurable per company or user.

---

### Option C — IP-range pinning (lightweight corporate use)

Allow N sessions per user but flag/alert when a new login comes from an IP address outside a known CIDR range for that company. Not a hard block — just raises the alert in the Suspicious Logins tab. This works well for desk-based staff but not for field engineers who roam.

---

### Recommended approach

**Option B (named devices)** is the most robust and user-friendly long term. It maps well onto the existing `active_sessions` table and the admin visibility already built. The `concurrent_session_alerts` query already surfaces the multi-IP pattern that sharing produces.

A practical rollout:

| Phase | Change | Protects against |
|---|---|---|
| Current (done) | Single session, displaced on new login | All sharing, but disrupts legitimate multi-device use |
| Phase 2 | Option A — one per device class | Most sharing cases; allows phone + laptop |
| Phase 3 | Option B — named device cookie + quota | Robust sharing detection; self-service session management |

Phase 2 can be delivered with ~30 lines of code change on top of what is already built. Phase 3 requires a `My Sessions` UI page and the device cookie infrastructure.

---

## SQL File — `sql/login_sharing_protection.sql`

```sql
-- =============================================================================
-- GeoLynx Login Sharing Protection — DDL
-- Run after geolynx_ddl.sql and admin_area.sql
-- =============================================================================

-- One active session per user. Replaced atomically on new login.
CREATE TABLE IF NOT EXISTS users.active_sessions (
    session_record_id  bigserial PRIMARY KEY,
    user_id            bigint NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    session_id         varchar(128) NOT NULL,
    login_log_id       bigint REFERENCES users.users_login_log(id) ON DELETE SET NULL,
    created_at         timestamp without time zone DEFAULT now(),
    last_seen_at       timestamp without time zone DEFAULT now(),
    UNIQUE (user_id)
);

CREATE INDEX IF NOT EXISTS idx_active_sessions_session_id
    ON users.active_sessions(session_id);

-- Logout audit on login log
ALTER TABLE users.users_login_log
    ADD COLUMN IF NOT EXISTS logout_at     timestamp without time zone,
    ADD COLUMN IF NOT EXISTS logout_reason varchar(30);
-- Valid values: 'user_logout' | 'session_timeout' | 'displaced' | 'admin_kill'

-- Brute-force protection on users
ALTER TABLE users.users
    ADD COLUMN IF NOT EXISTS failed_login_attempts integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS locked_until          timestamp without time zone;
```