array( label, sql, type, options?, entity? )`: * sql a developer-written fragment, always `t.`, never touched by the request * type one of Core\Filters\Operators::TYPES — decides which operators are offered * options for `select`, a LIST OF VALUES — not a value => label map * labels optional value => label map, ONLY for options the client cannot know (see below) * entity for `entity`, the slug the id points at, so the client knows what to pick from * * OPTIONS ARE VALUES AND THE CLIENT LABELS THEM. `LEAD_STATUS_LABELS` and its siblings already * exist in the React app, already translated, and are already what every other screen renders a * status with. Sending labels from here would mean a second set to keep in step, and the first * time they disagreed the filter would offer a choice worded unlike the column it filters. * * `labels` is the exception, for options that are ROWS rather than constants — a pipeline stage * the user named. The client cannot have a map for those, so the server sends one. Anything a * constant can express does not use it. * * DEFAULT IS EMPTY, AND DELIBERATELY SO. Returning the common timestamps here would put a * Filters button on all twenty lists at once — including email templates and portal users, * where nobody asked for one. A repository opts in by overriding, and gets `common_filters()` * to avoid retyping the three every entity shares. * * This is NOT where custom fields come from. They are derived from the definitions table by * `Core\Filters\FilterCatalogue`, so that defining a field is the only step needed to filter by * it. See the note there. * * @return array> */ public function filterable(): array { return array(); } /** * The three filters every assignable entity shares. * * `assigned_user_id` is in here rather than in `filterable()` above because the class contract * does not promise the column — it promises `deleted_at`, the timestamps and the by-columns. * Teams, templates and portal users have no owner, and a filter on a column that is not there * is a SQL error rather than an empty result. So this is opt-in: a repository that calls it is * asserting it has the column. * * @return array> */ final protected function common_filters(): array { return array( 'assigned_user_id' => array( 'label' => __( 'Owner', 'plugixa-crm' ), 'sql' => 't.assigned_user_id', 'type' => 'user', ), 'created_at' => array( 'label' => __( 'Created', 'plugixa-crm' ), 'sql' => 't.created_at', 'type' => 'date', ), 'modified_at' => array( 'label' => __( 'Modified', 'plugixa-crm' ), 'sql' => 't.modified_at', 'type' => 'date', ), ); } final protected function table_name(): string { return Schema::table( $this->table() ); } /** * The column holding a record's display name. Override where it is not `name`. * * A person is the exception: contacts and leads carry `full_name`, because "Grace" and "Hopper" are * stored apart and the whole name is the one anybody reads. */ protected function name_column(): string { return 'name'; } /** * The display names of some records, keyed by id, in one query. * * Exists so that a module can answer `plugixa_crm_record_names` for its own entities without every * module writing the same lookup. That filter is how a list shows "Site visit — at Northwind Traders" * without the listing module knowing what an account is, or which of its columns holds its name. * * Trashed rows are resolved too, deliberately: a row pointing at a trashed parent should render the * parent's name and let the reader see what happened, not silently show "—" as though the link had * never existed. * * @param int[] $ids Records to resolve. * * @return array Keyed by record id. */ public function names( array $ids ): array { $ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ); if ( ! $ids ) { return array(); } global $wpdb; // The list of ids is three bound values rather than a run of placeholders as wide as $ids, so // the statement is one literal. See InList for why both BETWEEN and FIND_IN_SET are there. [ $lowest, $highest, $set ] = InList::ids( $ids ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, %i AS display_name FROM %i WHERE id BETWEEN %d AND %d AND FIND_IN_SET( id, %s )', $this->name_column(), $this->table_name(), $lowest, $highest, $set ), ARRAY_A ); $names = array(); foreach ( (array) $rows as $row ) { $names[ (int) $row['id'] ] = (string) $row['display_name']; } return $names; } /** * Find one live row by primary key. * * Soft-deleted rows are invisible here by default, so no caller can leak a trashed * record by forgetting the trash exists. Pass $with_trashed to reach one — restore() * and purge() do. * * @param int $id Row id. * @param bool $with_trashed Include a soft-deleted row. * * @return array|null */ public function find( int $id, bool $with_trashed = false ): ?array { global $wpdb; $table = $this->table_name(); // Two shapes, two literal statements. Concatenating the trash clause onto a // variable would read more cheaply and defeat the point: prepare() can only be // audited when the statement it receives is visible at the call site. if ( $with_trashed ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ), ARRAY_A ); } else { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND deleted_at IS NULL', $table, $id ), ARRAY_A ); } return is_array( $row ) ? $row : null; } /** * A query builder scoped to this entity, with the ACL seam already wired. * * @param string $context Query context. */ public function query( string $context = 'list' ): QueryBuilder { return new QueryBuilder( $this->table_name(), $this->entity_type(), $context ); } /** * Insert a row. * * @param array $data Column => value. * * @return int The new row id, or 0 on failure. */ public function insert( array $data ): int { global $wpdb; $entity = $this->entity_type(); /** * Filter an entity's data immediately before insert. * * @param array $data Sanitised, fillable-filtered payload. * @param string $entity Entity slug. */ $data = (array) apply_filters( 'plugixa_crm_before_insert', $this->only_fillable( $data ), $entity ); $data['created_at'] = $data['created_at'] ?? current_time( 'mysql', true ); $data['modified_at'] = $data['created_at']; $data['created_by'] = $data['created_by'] ?? get_current_user_id(); $data['modified_by'] = $data['created_by']; if ( ! $this->fits( $data ) ) { return 0; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $ok = $wpdb->insert( $this->table_name(), $data ); if ( ! $ok ) { return 0; } $id = (int) $wpdb->insert_id; /** * Fires after any CRM entity is created. * * @param string $entity Entity slug. * @param int $id New row id. * @param array $data The inserted data. */ do_action( 'plugixa_crm_entity_created', $entity, $id, $data ); return $id; } /** * Update a row. * * @param int $id Row id. * @param array $data Column => value. */ public function update( int $id, array $data ): bool { global $wpdb; $old = $this->find( $id ); if ( null === $old ) { return false; } $entity = $this->entity_type(); /** * Filter an entity's data immediately before update. * * @param array $data Sanitised, fillable-filtered payload. * @param string $entity Entity slug. * @param int $id Row id. * @param array $old The row as it currently stands. */ $data = (array) apply_filters( 'plugixa_crm_before_update', $this->only_fillable( $data ), $entity, $id, $old ); $data['modified_at'] = current_time( 'mysql', true ); $data['modified_by'] = get_current_user_id(); if ( ! $this->fits( $data ) ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $result = $wpdb->update( $this->table_name(), $data, array( 'id' => $id ) ); if ( false === $result ) { return false; } // One event per changed field. A "when Status becomes Won" listener // hooks here; reconstructing it from a whole-row diff // downstream would be lossy and every consumer would reimplement it. foreach ( $data as $field => $new_value ) { if ( array_key_exists( $field, $old ) && (string) $old[ $field ] !== (string) $new_value ) { /** * Fires for each individual field that changed on a CRM entity. * * @param string $entity Entity slug. * @param int $id Row id. * @param string $field Column name. * @param mixed $new_value New value. * @param mixed $old_value Previous value. */ do_action( 'plugixa_crm_entity_field_changed', $entity, $id, $field, $new_value, $old[ $field ] ); } } if ( isset( $data['assigned_user_id'] ) && (int) $old['assigned_user_id'] !== (int) $data['assigned_user_id'] ) { /** * Fires when a CRM entity is reassigned to a different user. * * @param string $entity Entity slug. * @param int $id Row id. * @param int $new_user New assignee. * @param int $old_user Previous assignee. */ do_action( 'plugixa_crm_entity_assigned', $entity, $id, (int) $data['assigned_user_id'], (int) $old['assigned_user_id'] ); } /** * Fires after any CRM entity is updated. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $data The applied changes. * @param array $old The row before the update. */ do_action( 'plugixa_crm_entity_updated', $entity, $id, $data, $old ); return true; } /** * Move a row to the trash. * * CRM records are soft-deleted. A hard DELETE on an account would take its * contacts, its history and everything referencing it with no way back, and an * accidental click is not worth that. purge() is the deliberate, irreversible one. * * @param int $id Row id. */ public function delete( int $id ): bool { global $wpdb; $old = $this->find( $id ); if ( null === $old ) { return false; } $entity = $this->entity_type(); /** * Fires before a CRM entity is trashed. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $old The row about to be trashed. */ do_action( 'plugixa_crm_before_delete', $entity, $id, $old ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $result = $wpdb->update( $this->table_name(), array( 'deleted_at' => current_time( 'mysql', true ) ), array( 'id' => $id ) ); if ( false === $result ) { return false; } /** * Fires after a CRM entity is trashed. The row still exists and can be restored. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $old The trashed row. */ do_action( 'plugixa_crm_entity_deleted', $entity, $id, $old ); return true; } /** * Bring a row back out of the trash. * * @param int $id Row id. */ public function restore( int $id ): bool { global $wpdb; $row = $this->find( $id, true ); if ( null === $row || null === $row['deleted_at'] ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $result = $wpdb->update( $this->table_name(), array( 'deleted_at' => null ), array( 'id' => $id ) ); if ( false === $result ) { return false; } /** * Fires after a trashed CRM entity is restored. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $row The restored row. */ do_action( 'plugixa_crm_entity_restored', $this->entity_type(), $id, $row ); return true; } /** * Delete a row permanently. There is no way back from here. * * @param int $id Row id. */ public function purge( int $id ): bool { global $wpdb; $row = $this->find( $id, true ); if ( null === $row ) { return false; } $entity = $this->entity_type(); /** * Fires before a CRM entity is permanently deleted. Listeners should clean up * anything that references it — this is the last moment the row exists. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $row The row about to be destroyed. */ do_action( 'plugixa_crm_before_purge', $entity, $id, $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. $ok = (bool) $wpdb->delete( $this->table_name(), array( 'id' => $id ) ); if ( ! $ok ) { return false; } /** * Fires after a CRM entity is permanently deleted. * * @param string $entity Entity slug. * @param int $id Row id. * @param array $row The destroyed row. */ do_action( 'plugixa_crm_entity_purged', $entity, $id, $row ); return true; } /** * Stop this table pointing at a record that is being purged, by clearing the column that holds its id. * * For a `plugixa_crm_before_purge` listener. Purging an account left three contacts, three affiliation * rows and two deals pointing at an id that no longer existed (E2E run, 2026-09-11) — every one a * broken link on screen, and a wrong answer the day auto_increment hands that id out again. * * One UPDATE, not a loop of {@see update()}: the company a deal belonged to being destroyed is not an * edit of the deal, so no modified_at moves and no `_updated` fires — for a large account that would be * thousands of stream entries and queued webhooks. Trashed rows are cleared as well, or restoring one * would bring the dead pointer back. * * @param string $column The column holding the id, e.g. `account_id`. A column name from code, never input. * @param int $id The record being purged. * * @return int Rows changed. */ final public function forget_reference( string $column, int $id ): int { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. return (int) $wpdb->query( $wpdb->prepare( 'UPDATE %i SET %i = NULL WHERE %i = %d', $this->table_name(), $column, $column, $id ) ); } /** * Detach every row whose polymorphic parent is a record being purged. * * The row itself survives — a meeting that was held is still a meeting that was held after the company * it was logged against is gone — it simply no longer claims a parent. Same rules as * {@see forget_reference()}: one UPDATE, no events, trashed rows included. * * @param string $entity_type The purged record's type. * @param int $id The purged record. * * @return int Rows changed. */ final public function forget_parent( string $entity_type, int $id ): int { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from. return (int) $wpdb->query( $wpdb->prepare( 'UPDATE %i SET parent_type = NULL, parent_id = NULL WHERE parent_type = %s AND parent_id = %d', $this->table_name(), $entity_type, $id ) ); } /** * Whether every text value fits its column, recording why not when one does not. * * A value longer than its column never reaches MySQL: `$wpdb->insert()` notices, refuses the whole * write and returns false, and every service answers that with a 500 "could not be saved" — which is * what a 256-character account name got (E2E run, 2026-09-11). The client is told nothing it can fix. * Measuring here, the way `$wpdb` measures (characters for CHAR/VARCHAR, bytes for the TEXT types), * names the field and its limit instead. Costs no query: `$wpdb` has already read the column * definitions for the write it was about to make. * * @param array $data Column => value, as it will be written. */ private function fits( array $data ): bool { global $wpdb; self::$refusal = null; foreach ( $data as $column => $value ) { if ( ! is_string( $value ) ) { continue; } $limit = $wpdb->get_col_length( $this->table_name(), (string) $column ); if ( ! is_array( $limit ) || empty( $limit['length'] ) ) { continue; } $length = 'byte' === $limit['type'] ? strlen( $value ) : mb_strlen( $value, 'UTF-8' ); if ( $length <= $limit['length'] ) { continue; } self::$refusal = new WP_Error( 'plugixa_crm_value_too_long', 'byte' === $limit['type'] /* translators: 1: field name, 2: maximum size in bytes. */ ? sprintf( __( '%1$s is too long. It can hold at most %2$s bytes.', 'plugixa-crm' ), $column, number_format_i18n( (int) $limit['length'] ) ) /* translators: 1: field name, 2: maximum number of characters. */ : sprintf( __( '%1$s is too long. It can be at most %2$s characters.', 'plugixa-crm' ), $column, number_format_i18n( (int) $limit['length'] ) ), array( 'status' => 422, 'field' => $column, 'max' => (int) $limit['length'], ) ); return false; } return true; } /** * The reason the most recent write was refused for a value too long for its column, once. * * Read by {@see \PlugixaCrm\Core\Http\RefusedWrite}, which answers the REST request with this 422 in * place of the service's generic 500. A static rather than a return value because insert() and update() * keep their int/bool contract for the dozens of services that call them. */ public static function take_refusal(): ?WP_Error { $refusal = self::$refusal; self::$refusal = null; return $refusal; } /** * Drop any key that is not a writable column. * * @param array $data Raw payload. * * @return array */ final protected function only_fillable( array $data ): array { return array_intersect_key( $data, array_flip( $this->fillable() ) ); } /** * Resolve a requested sort column against the allow-list. * * @param string $requested Column asked for by the client. */ final public function safe_sort_column( string $requested ): string { return in_array( $requested, $this->sortable(), true ) ? $requested : 'id'; } }