ores.iam.session

1. Flags
2. Natural keys
3. Columns
4. SQL
- 4.1. Flags
- 4.2. Indexes
5. C++
6. See also

A user session: one login or service-account connection, recorded for analytics, session listing and end-time tracking. The table is a TimescaleDB hypertable partitioned by start_time with 7-day chunks (see projects/ores.sql/create/iam/iam_sessions_create.sql).

The table has no valid_from=/=valid_to, no GIST exclusion, no version column and no audit tail, and it keys on (id, start_time) rather than a single surrogate key – the partition column must sit in the primary key of a hypertable. The :current_state: and :hypertable: flags in the * SQL ** Flags drawer select exactly that shape. The compound key is what makes this the first entity in the estate whose key carries a timestamp; the mapper, entity and generator templates gained the timestamp key-column branches they were missing.

end_time is text not null default '', not a nullable timestamp, and client_ip is text, not inet. The model mirrors each column's real type; the domain projection is the column's type too, so a session's end_time is an empty string while the session is active and client_ip is a string. The richer readings (an instant, an IP address) belong to the consumer that needs them.

The hand-written domain struct also carried party_id, visible_party_ids and username. No column backs any of them; they are the denormalised, party-scoped shape of a session, and the rule that an entity describes its table puts them on a message instead. They are declared as message fields on session_view in ores.iam.session_messages.

The entity's canonical CRUD subjects are generated: session_registrar owns list/get/get-many/put/put-many/delete on iam.v1.sessions.*. The operation models declare a different set under the same prefix – iam.v1.sessions.active and iam.v1.sessions.samples – so the two coexist rather than compete, and no handler is suppressed here.

1. Flags

2. Natural keys

3. Columns

3.1. id

Unique identifier for the session.

3.2. start_time

Timestamp when the session started (login time). It is the hypertable's partition column, so it is part of the primary key.

3.3. account_id

Foreign key referencing the associated account.

3.4. end_time

Timestamp when the session ended (logout or disconnect), stored as an ISO 8601 string. Empty while the session is still active.

std::string("")

3.5. client_ip

Client IP address (IPv4 or IPv6).

boost::asio::ip::make_address("192.168.1.100")

3.6. client_identifier

Client identifier string from the handshake, typically the client application name.

std::string(faker::word::noun()) + " Client"

3.7. client_version_major

Client protocol version major number.

3.8. client_version_minor

Client protocol version minor number.

3.9. bytes_sent

Total bytes sent to the client during this session.

3.10. bytes_received

Total bytes received from the client during this session.

3.11. country_code

ISO 3166-1 alpha-2 country code from geolocation. Empty if geolocation is unavailable or the IP is private or localhost.

std::string("GB")

3.12. protocol

Protocol used for this session: binary or http.

std::string("binary")

4. SQL

4.1. Flags

4.2. Indexes

name	columns	unique	current_only	where_extra
tenant	tenant_id, start_time desc	false	false
account_id	account_id, start_time desc	false	false
active	account_id	false	false	end_time = ''
country	country_code, start_time desc	false	false	country_code != ''
protocol	protocol, start_time desc	false	false

5. C++

5.1. Flags

5.2. Repository

5.3. Domain includes

#include <boost/asio/ip/address.hpp>
#include <boost/uuid/uuid.hpp>
#include <chrono>
#include <cstdint>
#include <string>

5.4. Entity includes

#include <cstdint>
#include <string>
#include "sqlgen/Timestamp.hpp"
#include "sqlgen/PrimaryKey.hpp"

5.5. Conventions

5.6. Table display

column	header
id	ID (UUID)
account_id	Account
start_time	Start
end_time	End
client_ip	Client IP
client_identifier	Client
country_code	Country
protocol	Protocol
bytes_sent	Bytes Sent
bytes_received	Bytes Received

5.7. Presentation

The screen's declaration. A session is addressed by its id, which is the session's own identifier: the storage key is composite because the table is a hypertable partitioned by start_time, and a partition column has to be part of a primary key. That composite addresses a row for the store; it is not what an operator means by a session, and it is not what a path segment can carry.

The screen is read-only. A session is opened by signing in and closed by the service that ends it, so there is nothing for a person to write here; what the screen is for is seeing who is connected and from where.

5.7.1. Detail fields

field	label	widget	type	is_key	is_required	placeholder
id	ID	idEdit	line_edit	true	true	Enter the session id
account_id	Account	accountIdEdit	line_edit
start_time	Start	startTimeEdit	date
end_time	End	endTimeEdit	date
client_ip	Client IP	clientIpEdit	line_edit
client_identifier	Client	clientIdentifierEdit	line_edit
country_code	Country	countryCodeEdit	line_edit
protocol	Protocol	protocolEdit	line_edit

5.7.2. Columns

enum_name	field	header	type	width
AccountId	account_id	Account	string	300
StartTime	start_time	Start	timestamp	160
EndTime	end_time	End	timestamp	160
ClientIp	client_ip	Client IP	string	150
CountryCode	country_code	Country	string	90
Protocol	protocol	Protocol	string	110
BytesSent	bytes_sent	Bytes Sent	int	110
BytesReceived	bytes_received	Bytes Recv.	int	110

5.8. Paste blocks

5.8.1. repository_reads

The generated CRUD set reads by compound primary key and orders by it. The consumers on the session surface read by account, by active state, by session id alone (the logout path needs the start_time before it can address the row), and update end_time and the byte counters in place on the hot path. Those reads and the two in-place updates stay declared here, over the generated entity and mapper, rather than forcing the consumers onto full-row upserts.

#include "ores.platform/time/datetime.hpp"
#include <boost/lexical_cast.hpp>
#include <boost/uuid/uuid_io.hpp>

std::optional<domain::session> read(context ctx, const boost::uuids::uuid& session_id);

void update_bytes(context ctx,
                  const boost::uuids::uuid& session_id,
                  const std::chrono::system_clock::time_point& start_time,
                  std::uint64_t bytes_sent,
                  std::uint64_t bytes_received);

void end_session(context ctx,
                 const boost::uuids::uuid& session_id,
                 const std::chrono::system_clock::time_point& start_time,
                 const std::chrono::system_clock::time_point& end_time,
                 std::uint64_t bytes_sent,
                 std::uint64_t bytes_received);

std::vector<domain::session> read_by_account(context ctx,
                                             const boost::uuids::uuid& account_id,
                                             std::uint32_t limit = 0,
                                             std::uint32_t offset = 0);

std::vector<domain::session> read_active_by_account(context ctx,
                                                    const boost::uuids::uuid& account_id);

std::uint32_t count_by_account(context ctx, const boost::uuids::uuid& account_id);

std::vector<domain::session> read_all_active(context ctx);

std::optional<domain::session>
session_repository::read(context ctx, const boost::uuids::uuid& session_id) {
    const auto session_id_str = boost::lexical_cast<std::string>(session_id);
    const auto query = sqlgen::read<std::vector<session_entity>> |
                       where("id"_c == session_id_str) |
                       limit(static_cast<std::size_t>(1));

    const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
    ensure_success(r, lg());

    if (r->empty()) {
        return std::nullopt;
    }

    return session_mapper::map(r->front());
}

void session_repository::update_bytes(context ctx,
                                      const boost::uuids::uuid& session_id,
                                      const std::chrono::system_clock::time_point& start_time,
                                      std::uint64_t bytes_sent,
                                      std::uint64_t bytes_received) {
    const auto session_id_str = boost::lexical_cast<std::string>(session_id);
    const auto start_time_str = ores::platform::time::datetime::to_db_string(start_time);

    const auto query = sqlgen::update<session_entity>(
                           "bytes_sent"_c.set(static_cast<std::int64_t>(bytes_sent)),
                           "bytes_received"_c.set(static_cast<std::int64_t>(bytes_received))) |
                       where("id"_c == session_id_str && "start_time"_c == start_time_str);

    const auto r = sqlgen::session(ctx.connection_pool())
                       .and_then(begin_transaction)
                       .and_then(query)
                       .and_then(commit);
    ensure_success(r, lg());
}

void session_repository::end_session(context ctx,
                                     const boost::uuids::uuid& session_id,
                                     const std::chrono::system_clock::time_point& start_time,
                                     const std::chrono::system_clock::time_point& end_time,
                                     std::uint64_t bytes_sent,
                                     std::uint64_t bytes_received) {
    const auto session_id_str = boost::lexical_cast<std::string>(session_id);
    const auto start_time_str = ores::platform::time::datetime::to_db_string(start_time);
    const auto end_time_str = ores::platform::time::datetime::to_db_string(end_time);

    const auto query = sqlgen::update<session_entity>(
                           "end_time"_c.set(end_time_str),
                           "bytes_sent"_c.set(static_cast<std::int64_t>(bytes_sent)),
                           "bytes_received"_c.set(static_cast<std::int64_t>(bytes_received))) |
                       where("id"_c == session_id_str && "start_time"_c == start_time_str);

    const auto r = sqlgen::session(ctx.connection_pool())
                       .and_then(begin_transaction)
                       .and_then(query)
                       .and_then(commit);
    ensure_success(r, lg());
}

std::vector<domain::session> session_repository::read_by_account(context ctx,
                                                                 const boost::uuids::uuid& account_id,
                                                                 std::uint32_t limit_count,
                                                                 std::uint32_t offset_count) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);

    std::vector<session_entity> entities;
    if (limit_count > 0) {
        const auto query =
            sqlgen::read<std::vector<session_entity>> | where("account_id"_c == account_id_str) |
            order_by("start_time"_c.desc()) | sqlgen::offset(static_cast<std::size_t>(offset_count)) |
            sqlgen::limit(static_cast<std::size_t>(limit_count));
        const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
        ensure_success(r, lg());
        entities = *r;
    } else {
        const auto query =
            sqlgen::read<std::vector<session_entity>> | where("account_id"_c == account_id_str) |
            order_by("start_time"_c.desc()) | sqlgen::offset(static_cast<std::size_t>(offset_count));
        const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
        ensure_success(r, lg());
        entities = *r;
    }

    return session_mapper::map(entities);
}

std::vector<domain::session>
session_repository::read_active_by_account(context ctx, const boost::uuids::uuid& account_id) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);
    const std::string empty_end_time;
    const auto query = sqlgen::read<std::vector<session_entity>> |
                       where("account_id"_c == account_id_str && "end_time"_c == empty_end_time) |
                       order_by("start_time"_c.desc());

    const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
    ensure_success(r, lg());

    return session_mapper::map(*r);
}

std::uint32_t session_repository::count_by_account(context ctx,
                                                   const boost::uuids::uuid& account_id) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);

    struct count_result {
        long long count;
    };

    const auto query = sqlgen::select_from<session_entity>(sqlgen::count().as<"count">()) |
                       where("account_id"_c == account_id_str) | sqlgen::to<count_result>;

    const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
    ensure_success(r, lg());

    return static_cast<std::uint32_t>(r->count);
}

std::vector<domain::session> session_repository::read_all_active(context ctx) {
    const std::string empty_end_time;
    const auto query = sqlgen::read<std::vector<session_entity>> |
                       where("end_time"_c == empty_end_time) | order_by("start_time"_c.desc());

    const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
    ensure_success(r, lg());

    return session_mapper::map(*r);
}

6. See also

ores.iam — component group overview.